Questions tagged [partitioning]
Splitting a database table into multiple segments for performance or manageability.
868
questions
0
votes
0answers
13 views
Non matching attribute 'INSTANT COLUMN(s)' between partition and table in mysql 8.0.20
I am using MySQL 8.0.20. I found a solution that recommended to include algorithm=Inplace or algorithm=copy in alter table exchange partition statement explicitly, but my DDL statement doesn't seem to ...
0
votes
1answer
22 views
SQL 2016 - NU/NC Index Missing From sys.partitions
Is there a reason that a non-unique, non-clustered index would have 0 corresponding records in the sys.partitions view? There is a CI on the same table and there is a record for it in the view. My ...
0
votes
1answer
46 views
Partition switching vs schema transfer
I'm designing a database whose data will be generated daily by an ETL.
After some research, I found that one can use partition switching or schema transfer to maximize the uptime of the "live&...
3
votes
1answer
44 views
Half of the partitions are empty on a KEY partition
We are trying to move data from one table that currently has over 1B rows to a partitioned table.
But as we started to do so we notice that half of the partitions are empty, why is that?
Table is ...
1
vote
3answers
60 views
Shrinking data file to save disk space before migration
I know this is most discussed answered on various forums: I completely understand answer is no in most of the time or cases.
However want to know if it can be good approach under any scenario:
Lets ...
2
votes
1answer
88 views
Postgres 12 partitions performance by coll type
I do performance tests on posgtres 12 with partitions and a lot of data.
Each partition contains only one site with 400k of rows and I generated around 1k of partitions tables;
For the first test ...
0
votes
2answers
58 views
MySQL partitioning relatively large DB Table
I have table structure similar to the following -
CREATE TABLE `ProductCatalog` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`SerialNumber` varchar(20) DEFAULT NULL,
`BasePrice` decimal(10,2)...
1
vote
1answer
13 views
Partitioning in MySql 5.7 with Primary Key and Unique Key
I am trying to create a hash partition in MySQL 5.7 AWS RDS, cause the table is growing much rapidly approximately with 200 Million Rows in a month.
The table structure is like this and tried to ...
0
votes
1answer
47 views
Best Practice for Handling Table with Large Amount of Transaction Data
I have a transaction table storing large amount of transaction data. Only the most recent data of one month are required to be available for transactional use in real-time. Less recent data may only ...
2
votes
1answer
99 views
<> does partition elimination?
We have a partitioned tables partitioned on STATUS Column using below partitioning function. So a table will have total two partitions and both are going to different filegroups as per partition ...
4
votes
2answers
370 views
Delete rows older than x days without locking table
we have a quite big MS SQL database with millions of rows.
I created a simple script to delete rows older than 1 month, but this seems to lock the table and creates trouble for the application.
The ...
0
votes
1answer
29 views
Can Postgres partition table by column values to enable partition pruning
I'm using Hive currently and it has partitioned tables.
Let's say this is my data:
Country City DailySale
USA NY 200.2
USA SF 190.5
CAN TR 390.4
The I can define my table
create ...
0
votes
0answers
25 views
mysql partition by date and id
At the moment I'm partitioning my table daily by
PARTITION BY RANGE COLUMNS(date)
On a daily basis I load data into that table, I have billions of rows in that table. I was thinking on switch to ...
0
votes
1answer
27 views
Creating row number for multiple columns?
I have the below data, and I want to only take one Location per Order ID ordered by the distance. This data set takes customer zips and compares to store zips and then returns the distance. I want to ...
1
vote
1answer
44 views
postgres query performance [closed]
I can't find a good way to rewrite this query or improve the performance:
query:
update company_holding c
set issuer_stim_buy_quarter = sq.estim_quarter_buy
from (select h.company_id
,h....
0
votes
1answer
55 views
Thousands foreign keys referenced to one table and other postgres scaling philosophy
I'm inspecting one legacy and quite big database which is growing faster nowadays than before.
Following query
select count(*) from information_schema.tables where table_schema = 'public' returns a ...
0
votes
1answer
30 views
When dropping a partition on InnoDB table does mysql flush pages in the whole table or only in a partition to be dropped?
When issuing ALTER TABLE .. DROP PARTITION p1 mysql have to flush pages to disk. My question is: does mysql flush pages in the whole table (in each partition) or only in a partition to be dropped? ...
1
vote
2answers
53 views
How to reduce fragmentation of nonclustered index on date partitioned log table
Looking for some advice before I follow my gut on this and rebuild the nonclustered index to lead with our partition key. This is an insert-only table which is never updated or deleted, we keep a ...
-1
votes
1answer
34 views
what's the best way to execute fast aggregation queries based on YEAR and MONTH filters in addition to dynamic filters
My SQL table is going to have millions of rows. Currently, with only 2M rows, my aggregation queries take anywhere between 20 seconds to a minute.
The filters in WHERE clause for aggregation queries ...
0
votes
1answer
25 views
Query against partitioned table accessing more partitions than necessary?
I first posted this on SO but got no replies, this is the reason for why I'm trying here now and I hope I'm not breaking any rules in doing so (fingers crossed).
I'm trying to understand how to use ...
0
votes
0answers
9 views
How to avoid errors 135 (no more room in file) and 176 (wrong checksum) when repairing a corrupted partition in MariaDB
I have no prior experience working with partitioned tables, I got a corrupted partition and all my repair attempts have failed. I would really appreciate your help.
The affected table is created as ...
0
votes
1answer
17 views
How to create automatic partitions on a non-time based column in Postgres?
I want to create a partition on a column which is of type serial. A hash based partition seems appropriate here. But from what I understand I will need to create all the partitions myself (based on ...
0
votes
1answer
31 views
Sql Server Dynamic partition with page compressed index
I created a table with partitions by following this 2012 partitioned index document.
I created partitions monthly based on Date_Id column;
CREATE PARTITION FUNCTION Fnc_Prt_Fact_Sales (INT)
AS RANGE ...
0
votes
2answers
21 views
Is it possible to edit a partition function on MSSQL 2016?
I've inherited a partitioned database with millions of rows and I've noticed there is a typo in the partition function:
CREATE PARTITION FUNCTION [Partition_by_customer](varchar(10)) AS RANGE RIGHT ...
0
votes
0answers
19 views
Efficient way to get one value from each table list partition?
I use PostgreSQL partitions to store OHLCV data for multiple markets and timeframes:
create table candles (
market character varying(64) not null,
timeframe character varying(64) not null,
"...
0
votes
0answers
12 views
Best mysql partition for stats table - by date or productid?
we have a large stats table which logs hits per day, per product.
date | datetime
productid | int
hits | int
channel | enum
About 200M rows+
We have two ways of querying this date - It ...
0
votes
1answer
45 views
Consistent Prefix Reads
In the book Designing Data-Intensive Applications, the author suggests that replication lag can lead to an anomaly were causality is violated. In the example that the author uses, the system is both ...
0
votes
1answer
15 views
What would be an efficient way of preventing sequential scan if data is partitioned by list, when it comes to updating multiple partitions?
What would be an efficient way of preventing sequential scan if data is partitioned by list, when it comes to updating multiple partitions with a condition that is not referring to the partition key?
...
1
vote
2answers
52 views
Speed up large database queries
I am running a large local database with MySQL and InnoDB. The size is around 30GB for data and 6GB for Keys. The machine has 16GB of RAM.
The table looks like this
symbol VARCHAR(10),
iso_datestamp ...
0
votes
1answer
21 views
Oracle autotrace - pstart and pstop always the same
I have been executing autotrace against a partitioned table. The table is partitioned by month range. For the data I have so far there are 4 partitions - however, when I execute autotrace against ...
0
votes
1answer
23 views
Fetch partitions where values are present
I have table partitioned by range of dates (monthly).
Having a list of dates, I want to select all the partitions, where values from the list of dates are present. That is to avoid scanning the ...
0
votes
1answer
97 views
postgresql multicolumn partitioning
Postgres 11
I want to make a table calendars, that is at the same time partitioned by range (one month of data) and a list of keys.
The reason is that:
I want to prune months that are not longer ...
-1
votes
1answer
45 views
Does Oracle guarentee that ORA_HASH is used to determine which hash partition a row is inserted, and will this be honored in the future?
I use hash partitioning for a few of my very large tables, and occasionally I have a use case where it would be convenient to have a mechanism that would return the partition name that a row would be ...
2
votes
2answers
63 views
When should we use table partitioning in Postgresql?
Context: we have two fairly large tables in our database, one holding 80 millions records and the other 160 millions.
We're seeing performances issues and are thinking about using table partitioning ...
1
vote
0answers
56 views
When partitioning a huge table in postgres , how much is too much?
I have a huge table in Postgres with 10 million inserts per day, Since it's not feasible to keep all these data in a single table, we are splitting it based on the week. we are currently using ...
0
votes
1answer
20 views
How to select rows with a particular rank per partition?
I want to pick the row with the highest value in a column based on partitioning on another column. The problem is to pick the highest-earning employees from each department, click here for the ...
1
vote
1answer
559 views
ERROR: insufficient columns in PRIMARY KEY constraint definition
I recently upgraded the database from PostgreSQL v9.6 to v11.7. we have some partitioned table with inherence and planning to migrate them to the declaration.
Table DDL:
CREATE TABLE c_account_p
(
...
0
votes
1answer
28 views
Table Partitioning - database file increased after partitioning
I have a table that is 100 GB in size. I forgot the exact blog which I read when partitioning tables but when I ran the partition query, my primary table file increased while being able to partition ...
0
votes
1answer
32 views
Creating and dropping partition in PostgreSQL “on the fly”?
In our web-applicatons we have PostgreSQL database. Users write into and delete from this database ecology forecasts. Because data amount is significant (more than 100 Gb), we use declarative ...
0
votes
0answers
77 views
PostgreSQL 10 - create partitions on column that needs to be calculated after importing a csv
I am in the situation where I need to create a table which also has a time column.
This table is filled with data taken from a CSV, but the csv has no time column.
Only after importing the data inside ...
0
votes
2answers
42 views
Reclaiming space from half filled huge FGs having Partitioned and LOB Data in it
We have a database with multiple filegroups. Out of which, there are 4 filegroups(Non-Primary) which has most of the data. These filegroups have partitioned tables into it. It also contains LOB Data ...
2
votes
2answers
55 views
Should I in general partition tables by tenant IDs in a multi-tenant system?
We're building a system where the data in 10 tables is associated with Accounts. A typical table looks like this:
create table Things(
accountId varchar(64) not null,
internalId varchar(64) ...
2
votes
2answers
126 views
What is the best choose for PK in Partitioned Table?
I have one large table that is partitioned (table name: Trans). At the moment this table is to be created on 32 partitions. This table will contain approximately 300 million records and data older ...
0
votes
0answers
22 views
how to migrate certain filegroups data with partitions to another server periodically
I have 3 partition tables on multiple file groups on monthly basis., each month it generates about 1-2TB data on one partition, one filegroup. For more than 1 month old data, we set the filegroup to ...
5
votes
0answers
553 views
In SQL Server, what is “Partitioned Table Parallelism” that is only available in Enterprise Edition?
I am reviewing the differences between Standard and Enterprise editions of SQL Server and can't reproduce the differences advertised in this demo that explains the differences - the performance I ...
1
vote
1answer
18 views
Postgres partition avoiding redundant column
Say I have a table, say project_data with lots of rows where the data may be grouped by a project_id integer column, and partition it onto one table for each project_id value.
Do I then need the ...
0
votes
1answer
111 views
Simple query on sys.dm_db_partition_stats is running for 30 minutes
Simple query on sys.dm_db_partition_stats on user database is running for close to 30 mins where as it used to complete in less than 5 seconds earlier.
Query is just to fetch the right partition for ...
-1
votes
1answer
150 views
Oracle 11gR2 - Export - Import (expdp, impdp) How To Exclude A Table Partiton And This Table's Indexes
I have a table partition (135GB) and index partition on this table (total=150GB) and I want to exclude owner schema of this table and indexes with excluding this table partition and it's indexes.
What ...
0
votes
0answers
20 views
Is it possible to Auto Archive Old Data into ReadOnly partitions on a large table with existing PS/PF and auto drop old read only partitions
Is it possible to Auto Archive Old Data into Read Only partitions on a very large table with existing PS/PF but then also allow continued automatic pruning/dropping of those read only partitions i.e. ...
0
votes
1answer
306 views
Postgres 11 - Range Partitioning on a Date Column with NULL values
We have a table structure with parent and child records inside the same table. They are referenced via a column (parentId) which references the parent (and null on the top parent records). Also, we ...
