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.

2
votes
1answer
13 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
26 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
279 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
17 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
34 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
25 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
30 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
35 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
49 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
9 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
13 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
24 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
52 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
38 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
30 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
23 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 ...
6
votes
2answers
109 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 ...
0
votes
1answer
16 views

mysql 5.5 repartition

With MySQL 5.5, I want to change the partitioning scheme of a very large InnoDB table from "hash" to "range". Can I do it while the table is accepting DML? If not, what is the best-practice work-...
0
votes
0answers
23 views

how to backup, remove and then restore a filegroup of a partitioned table

Continuation of the question "Archiving SQL Server data on disk to be pottentially restored". I'm still looking for a way to archive data per month which can easily be restored... I'm in the ...
0
votes
2answers
37 views

Is there a way to automatically update a view based on daily created tables?

Let's say I have a table that is created daily: 20181227 20181226 20181225 ... There is view that is like: CREATE OR REPLACE VIEW someview AS SELECT * FROM 20181227 UNION SELECT * FROM 20181226 ...
4
votes
1answer
78 views

PostgreSQL How to DEFAULT Partitioned Identity Column?

PostgreSQL 11 What is the best way to generate default values for identity columns on partition tables. E.g CREATE TABLE data.log ( id BIGINT GENERATED ALWAYS AS IDENTITY ...
0
votes
0answers
16 views

Mariadb / mysql table partitionning column usage

I read mariadb documention :https://mariadb.com/kb/en/library/partition-maintenance/ And it sayed : The partition field should not be the field first in any key But in mysql documentation :https:...
2
votes
1answer
42 views

MySQL when using WHERE BETWEEN, query hits all partitions

This is probably just me misunderstanding how MySQL partitions work, but I have a table defined with: `ID` int(11) NOT NULL, `target_id` int(11) NOT NULL, `created_at` datetime NOT NULL, ...
1
vote
1answer
21 views

Moving mysql database to new partition with different partition “type”

Sorry, Possibly a dumb question, but something which needs clarification. Currently mysql is stored on sda which is around 1TB. Now our DB has outgrown that, almost !!. So trying to create new ...
0
votes
1answer
69 views

Postgres 11 declarative partitioning by relative time

I'm working on a system that uses PostgreSQL 11 as it's main data store, and I'm looking at how to most efficiently query a large table of events (up to 120 million rows, so not 'big data' big, but ...
8
votes
1answer
2k views

TOP(1) BY GROUP of very huge (100,000,000+) table

Setup I have a huge table of ~115,382,254 rows. The table is relatively simple and logs application process operations. CREATE TABLE [data].[OperationData]( [SourceDeciveID] [bigint] NOT NULL, ...
1
vote
1answer
27 views

Does dropping a partition and running update indexes stop DML execution on the whole table?

I am looking to drop a partition from a table in the database. I wanted to know if I run ALTER TABLE DROP PARTITION UPDATE INDEX, would there be any blocking of DML statement executions on the other ...
2
votes
1answer
96 views

Moving the data between partitions in a table

There is one table with partition, where partition function and scheme is defined like below: CREATE PARTITION FUNCTION DateRangePF (CHAR(8)) AS RANGE RIGHT FOR VALUES ('20180101','20190101') ...
1
vote
1answer
49 views

Adding a CHECK constraint doesn't improve performance of ATTACH PARTITION on postgres table

I'm attempting to merge a table into a partition, the merge works but the performance isn't good. I've read this link, in particular this quote.... Before running the ATTACH PARTITION command, it ...
1
vote
2answers
103 views

How to identify the column used to partition a table from the Postgres system catalogs

Given a table created as so... CREATE TABLE measurement ( city_id int not null, logdate date not null, peaktemp int, unitsales int ) PARTITION BY RANGE (...
0
votes
2answers
36 views

With partitioned table, How can I use hints to group each partition separately

Suppose I have the following table data(partitioned_key_index, some_dummy_measure) Assume that partitions are of an equal large size. with Oracle 11g. The end result should be like this ...
1
vote
0answers
15 views

Filegroup per Table partition - remove filegroup and re-add filegroup

I've seaching all over the web for a solution, if it is even possible... I'm not a savvy in Partitioned tables in different filegroups... Our software is supported by SQL 2008 R2 SP2 to SQL 2017 for ...
1
vote
0answers
31 views

How to VACUUM FULL a single partition without stalling the parent?

I have a table called TABLE_A that's partitioned by month. Each partition is named according to it's month, TABLE_A_201810, TABLE_A_201811 etc I've noticed that if I attempt to VACUUM an old ...
0
votes
1answer
28 views

partition in ndbcluster

My environment will use 2-3 cluster nodes. The db schema at first was designed without knowing the limitation/rules with NDBCLUSTER on (BIG mistake! I know). Some things I notice and want to ...
0
votes
1answer
49 views

Partitioning an existing table into multiple filegroups [duplicate]

I am working on a SQL Server 2016 SP2 Standard edition server, with an in-house application that is 24/7 used in multiple locations across multiple countries. The current application has one database ...
2
votes
1answer
67 views

Is database partitioning only created to get a better performance? and why should one partition a table, and a database? [closed]

I was asked if SQL Server can handle huge databases ( for them a huge database is 100GB). I know it can. I worked with 4tB databases already. But I would like to ask, thinking about really huge ...
1
vote
1answer
104 views

Index rebuild at partition level on a table

We have functionality in SQL Server to rebuild indexes at partition level using below command ALTER INDEX IX_name ON table REBUILD PARTITION=68 In non-partitioned table we disable the indexes, load ...
6
votes
1answer
204 views

Rebuilding indexes for a partitioned table having 300 partitions

Scenario Partitioned table is empty and I am loading data for 1 partition which has 180k rows. I disabled the indexes and loaded the data and rebuilt the indexes after data is loaded. Issue While ...
1
vote
2answers
47 views

How to identify the ranges over which a postgres table was partitioned?

If I create a table and partitions like this... CREATE TABLE tab1 (a int, b int) PARTITION BY RANGE(a); CREATE TABLE tab1_p1 PARTITION OF tab1 FOR VALUES FROM (0) TO (100); CREATE TABLE tab1_p2 ...
1
vote
2answers
66 views

SQL To Fetch Partition Boundaries and Filegroups

For a given partition scheme name, I need to retrieve the set of Boundary Value FileGroup Name This must work without the existence of any tables participating in the scheme. i.e. joins via sys....
0
votes
1answer
43 views

Declarative partitioning with Postgres for integer column

I was reading this article: https://blog.timescale.com/scaling-partitioning-data-postgresql-10-explained-cd48a712a9a1 the article lifts information directly from here: https://www.postgresql.org/docs/...
2
votes
0answers
97 views

missing ibd files

I'm running mysql 5.6. For reasons entirely my own fault, I've got some missing ibd that make up partitions for several tables. When I try to mysql I get the following messages for the missing ...
0
votes
1answer
34 views

Switching out of one scheme into another

I have a table named dbo.messages. We are expecting a lot of data in this table. To keep things to a reasonable level we are looking at a weekly partitioning on that table. What I would like to do ...
0
votes
1answer
49 views

Optimizing SQL query with two table joins and over 40 million rows in partitioned table

I have three tables, lets say their names are: table1 (has total over 40 million rows, but is partitioned with Declarative partition https://www.postgresql.org/docs/10/static/ddl-partitioning.html ...
4
votes
1answer
94 views

How many partitions should I make for my clustered columnstore index tables? Should I partition the rowstore tables also?

I have a data warehouse comprised of four clustered columnstore index tables (CCI) and nine rowstore tables. These tables are used only for analytics and the CCI data is inserted from staging tables ...
1
vote
1answer
42 views

Select data with the latest table partition that actually holds data

Version: SQL Server 2016 So, I need to be able to select the data within the most recent table partition. I can do this by using the below script but the latest partition doesn't contain any data. ...
0
votes
1answer
47 views

SQL server - Re. Filegroup / partitioning

Assuming VLDB is split into multiple file groups, each having its own file and on a separate disk. Now let's say there is a disk crash bringing down one of the file groups - then will the other parts ...
0
votes
1answer
90 views

Counting using window function with partition over an empty table

If i have an empty table (created but without rows in it), and i would like to get the number 0 from the below query, how i could achieve that? select count(*) over (partition by field_name) from ...
1
vote
0answers
58 views

Handling backups of a large table (>1 TB) in Postgres?

I have a 1TB table (X) that is a pain to backup. The table X contains historical log data that is not often updated after creation. We usually only access a single row at a time, so performance is ...