Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [partitioning]

Splitting a database table into multiple segments for performance or manageability.

0
votes
1answer
27 views

Truncate Table With Partitions - aligned/ non-aligned indexes

SQL Server 2016 introduced a modification to the TRUNCATE TABLE statement which allows you to truncate a specific partition or set of partitions from a table: TRUNCATE TABLE < tablename > WITH (...
0
votes
1answer
29 views

Efficient partition pruning with ORDER BY on same column as PARTITION BY RANGE + LIMIT?

I've set up a table in MariaDB (10.4.5, currently RC) with InnoDB using partitioning by a column of which its value is incrementing-only and new data is always inserted at the end. For this case, ...
0
votes
0answers
20 views

Advantage and Disadvantage of Partitioning by some of values of Primary Key

I'm going to implement/deploy a table like this: CREATE TABLE docref ( namespace VARCHAR(50), key VARCHAR(50), time_order INT, state SMALLINT, hash TEXT, ...
2
votes
1answer
10 views

Slow performance on partitioned PostgreSQL table when using now() to filter

I have a PostgreSQL v10 table partitioned on a timestamp field: CREATE TABLE front.probes ( bucket_start timestamp NOT NULL DEFAULT now(), -- Extra fields here ... ) PARTITION BY RANGE (...
0
votes
1answer
24 views

Limits of table partitioning (in PostgreSQL)

I have the following use case (PostgreSQL, currently v9 but I'm planning to upgrade to the latest version). I have a data warehouse application with a central table that has a sizeable number of ...
0
votes
0answers
13 views

Copying partitions from one clickhouse instance to another, won't attach

I am currently working out a solution to free up space from one clickhouse node (not clustered as of yet) by freezing old partitions and attaching them to a different node, then deleting them from the ...
3
votes
1answer
44 views

Check if partition elimination works

I implemented partitions on one of the tables our DEV/TEST environment, and modified some of the procedures to use the partition key when accessing this table. How can I verify that partition ...
0
votes
0answers
17 views

ORA-00600 When running ALTER modify PARTITION BY RANGE

I am running this on the table with 2 million records (2012378), I get the below error. when i do the same for less no.of rows (41000) there are no errors. Error starting at line : 51 in command - ...
0
votes
1answer
23 views

Postgresql update a partitioned table, where update affects multiple partitions

If I have a partitioned table partitioned on PKey column, can I update (or merge etc) the table like below (or more complex jonining to another table) which will cause multiple partitions to be ...
6
votes
1answer
161 views

Slide Partition from Rowstore to Columnstore

I have a rowstore table which contains ~10 billion records. The table is partitioned on an INT field and has about 1,400 active partitions. I also have a clustered columnstore table built on the same ...
0
votes
0answers
19 views

What's the maximum number of partition per table I can/should have in MySql 8?

I have around 1T (1,000,000,000) records of products belong to 500,000 accounts. Each product record have the account_id and since I always query within a single account context (there are no cross-...
0
votes
1answer
15 views

PostgreSQL Trigger to override insert on table with inheritance

We have a table documents that is partitioned by time into child tables like documents_2019_01, documents_2019_02. We do this using a BEFORE trigger on the parent documents table to insert into the ...
-1
votes
0answers
33 views

Undone the table partition for a particular year

I have implemented partitioning for a particular table, in such a way that user can partition min 1 and max upto 52, to a particular table for a particular year. Now, after performing partitioning, I ...
0
votes
1answer
63 views

How to alter an existing partitioned table to add sub-partitions to it?

This forum has helped me earlier on many occasions and now I need someone to help me with this. I am looking to convert a pre-existing partitioned (range) table - to sub-partitioned (composite range-...
0
votes
1answer
21 views

If I move my SQLdata file from my F drive, delete the F drive, and rename the new Drive F will I have any file path problems?

I have a windows server that only had 50 GB on the C drive and 4TB on the F drive. I need to exstend the C drive but the only way to do that is by deleting the F drive to reallocate space. If I move ...
1
vote
1answer
39 views

Create a partition for a temporal table based on a period

To improve the performance of my ETL, I decided to use temporal table instead of a slowly changing dimension. For my Employee dimension, I created a temporal table. Now I want to split data into ...
0
votes
0answers
40 views

PostgreSQL logical replication and partition table

We have a large database and we want a copy of the database in our company for faster access. we are going to achieve this copy by logical replication on PostgreSQL 11. But we have a big problem. We ...
0
votes
1answer
40 views

Partitioning with AUTO_INCREMENT primary key

I am looking to partition a MySQL table by the month of the entry timestamp column (time that the row was inserted). The table in question uses an AUTO_INCREMENT column as a primary key as this table ...
0
votes
1answer
107 views

Why does SQL Server table partitioning stop working? [closed]

For a number of reasons, but mostly the sheer volume of data, we have 5 MS-SQL (2017) sharded servers to break up the overall load on any individual server. Secondly we have a real-time table that is ...
-1
votes
1answer
41 views

Alternatives to MySQL Partitioning, so we can have a Unique Index

I have a DBA vs Developer Issue here, and am wondering if there are any alternatives to partitioning that I can employ here. I have a table as follows: CREATE TABLE `tracking` ( `Id` int(10) ...
0
votes
1answer
84 views

PostgreSQL 11: how to partition 1000 tenants

Setup We have a multi-tenant app that has about 1000 customers more or less. When a customer churns we delete all their data after a period. We have a few tables that are pretty big and we're ...
0
votes
0answers
31 views

How to make a table invisible in PostgreSQL?

I'm using the declarative partitioning feature in PG10, so far so good. However, the partitions are also visible as separate tables and it's a bit visually distracting. For example: Sales is the ...
2
votes
1answer
33 views

What's the difference between partitions and many tables?

PostgresSQL 10 and 11 introduced partitioned tables. Quoted from the official site: "Partitioning refers to splitting what is logically one large table into smaller physical pieces" And the ...
0
votes
1answer
28 views

How to PARTITION a table in mysql?

I read a lot of articles that PARTITIONing is not helpful in most cases. I believe it should speed up the queries in my case. I have a table with the structure of CREATE TABLE pages ( page_id int(11) ...
1
vote
1answer
71 views

Index partitioned table to prevent Postgres from doing a sequential scan

I have a Postgres database with a partitioned table that will contain ~2,000,000,000 entries. I have defined a partitioned database based on the first letter of the "identifier" - this is split into ...
0
votes
1answer
18 views

Is there a way to partition a mysql table, by range columns, where the 2 columns in question are of datetime and bigint?

I need to partition a table into 4 partitions, p0 to p3: with p0 having all rows with "id"=0, and p1 having all rows with "id"=1 and "doj" before february, and p2 having "id"=1 and "doj" before march, ...
3
votes
1answer
28 views

Is PRIMARY(ColumnA, ColumnB) + INDEX(ColumnB) + PARTITIONING(ColumnB) redundant?

I have a big table (30 millions rows) defined like this: CREATE TABLE `drawing` ( `id` int unsigned NOT NULL, `artist` int unsigned NOT NULL, `imageUrl` text NOT NULL, `uploadDate` timestamp NOT ...
1
vote
2answers
42 views

Index options and applying partitioning to an index

I have following two questions: I have a SQL Server index with the following options: PAD_INDEX = OFF STATISTICS_NORECOMPUTE = OFF SORT_IN_TEMPDB = OFF DROP_EXISTING = OFF ONLINE = OFF ...
1
vote
0answers
54 views

Time Interval Partitioning in SQL Server

I have a table with time stamp (UTC) and for several reasons, like purging from this table (done at later point of ETL), I am trying to find out the feasibility of partitioning the table by 5 minutes ...
0
votes
0answers
81 views

Create a clustered primary key on partitioned table

I have a table that has been partitioned recently based on a datetime column. The same table had a primary key (clustered) on the ID column. During the partitioning of the table, the primary key is ...
1
vote
2answers
49 views

What is the impact on the Cost-Based Optimizer (CBO) when implementing ISCSI SAN solid-state drives

A Microsoft Technet article recommends creating a secondary file group as the default file group (see reference below). The secondary file group should have a number of files, say four, that are each ...
3
votes
2answers
346 views

Table partition existing table where partition key is not part of the primary key

Main objective: Add partitioning to table to make deletion of old orders non-blocking/quicker (and also understand partitioning) I have an existing table Order, like this: CREATE TABLE Order ( ...
2
votes
1answer
26 views

Is it possible to add a spatial index to a partition with Postgres 10?

I am creating declarative partitions like: CREATE TABLE mydata.my_features ( id bigint, my_feature1 character(1), my_feature2 integer, ... my_geom geometry ) PARTITION BY ...
0
votes
1answer
32 views

Questions on database table and index partitioning in SQL Server

I have developed Transact-SQL script to partition the table. To apply the partition, I am creating one non-clustered index for my partition key column and associating it to partition scheme. I have ...
6
votes
2answers
352 views

Datetime conversion fails when filtered index is placed on partition column

I've got a mysterious error of char data conversion at the moment of insertion a new row into a table. Troubleshooting reveals that a filtered index is the source of the problem. The index's filter ...
0
votes
0answers
23 views

How to effectively, and efficiently and the simplest way to partition and move data from one db to another?

We have a large database with client data, with many entities, but we dont allow connections to the production db (mysql) due to security. Only option remaining is to move some data to development db (...
0
votes
1answer
39 views

Oracle partitioning scenario with primary key absolutely required

Is there any partitioning scenario in which the partitioned table is absolutely required to have primary key?
0
votes
1answer
40 views

Would querying from a partitioned table be faster than from multiple tables with the same table definition?

I have a table that contains about 50 million rows that I have partitioned by 5 million rows per partition. I have manually partitioned it into Table_1, Table_2, Table_3, etc., not using MySql's built-...
1
vote
1answer
34 views

Suboptimal query plan when updating partitioned table

Background I have a simple CTE used to update a declaratively-partitioned table. The subquery runs quickly (1.7sec per EXPLAIN ANALYZE) and returns 3,769 records (CTE y in the query below). The ...
0
votes
1answer
65 views

SQL Server Partition Switch

I have a partitioned table in SQL server 2017. I want to switch partition number 2 of this table the the staging table which is created using the same partition scheme as the original table. I'm ...
1
vote
0answers
54 views

Problem with partitioning non-clustered indexes

I have a database with partition function defined. Over this partition function there are 2 schemes defined, one for Data files and one for Indexes files. Data and index files are divided quaterly (...
0
votes
1answer
13 views

Oracle 11g, Will droping partition reuse free space on bigfile tablespace

I have four range partitioned tables which belongs to bigfile tablespace. Also local indexes on that tables belongs to other bigfile tablespace. My partitioning range is one day and I have stored ...
0
votes
0answers
27 views

Adding constraints to child tables in pg_partman

I'm trying to build a logging table and using pg_partman partitioning it on a daily level. This part is setup. The issue is I need a unique constraint on the fk_id and timestamp. The process to fill ...
0
votes
0answers
44 views

Postgres 10 partition pruning through Extended Query protocol

From what I can tell, in postgres 10, parition pruning through declaritive partitioning only works if the partition key value is included in the query explicitly SELECT * FROM partitioned_table WHERE ...
2
votes
1answer
53 views

How can I get the last items by date desc then by id desc?

I have a employee course table which holds the current record for each of my employees who have some courses that they have taken. Then I have a history that keeps a static version of the save data ...
0
votes
0answers
28 views

Where each partition has only one value in the constraint column, can I avoid filtering?

I have partitioned a table in postgres 9.6 by date. Every date within a parition is identical. When I select data, a relatively expensive Seq Scan filter operation happens against every row in the ...
0
votes
1answer
43 views

sliding window partitioning on date field

I need to apply Sliding Window Partitioning on a table that contains 1B of records. CREATE TABLE table_1( [Id] [bigint] IDENTITY(1,1) NOT NULL, [log_date] datetime NULL, [field1] [bigint] ...
0
votes
0answers
32 views

Partitioning Postgres table that have references to and queried without date range filters

As I understand partitioning is useful only for fact tables that isn't referenced by other tables and when you issue range select queries in case if you also have retention policy. Spring Batch ...
2
votes
1answer
44 views

Network Partitioning

My current understanding of the network-partitioning scenario, also mentioned in the CAP theorem, is that it's a scenario where the clients are not able to connect to some DB nodes even though they ...
7
votes
2answers
156 views

Wrong estimate on a query on partitioned tables

I wonder why SQL Server makes wrong estimations in such a simple case. There is a scenario. CREATE PARTITION FUNCTION PF_Test (int) AS RANGE RIGHT FOR VALUES (20140801, 20140802, 20140803) CREATE ...