Questions tagged [partitioning]

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

Filter by
Sorted by
Tagged with
42 votes
2 answers
66k views

How to partition existing table in postgres?

I would like to partition a table with 1M+ rows by date range. How is this commonly done without requiring much downtime or risking losing data? Here are the strategies I am considering, but open to ...
user avatar
  • 1,053
37 votes
1 answer
63k views

Index optimization with dates

I have a large table of objects (15M+ row) in PostgreSQL 9.0.8, for which I want to query for outdated field. I want to divide the query by millions, for scalability & concurrency purposes, and I ...
user avatar
  • 551
32 votes
3 answers
20k views

How Does Table Partitioning Help?

I am having difficulty to grab the idea of pros and cons of table partitioning. I am about to start work on a project which would have 8 tables and one of them will be the main data table which will ...
user avatar
  • 1,261
28 votes
4 answers
135k views

How do I get a list of all the partitioned tables in my database?

How do I get a list of all the partitioned tables in my database? Which system tables/DMVs should I be looking at?
user avatar
  • 2,397
26 votes
2 answers
71k views

How to partition an existing non-partitioned table

I have an existing table with data: dbo.Test (col1,col2,col3....) ON [PRIMARY] I need to change this table to be partitioned like this: dbo.Test(col1,col2,col3....) ON Ps_Date(Col2) How I can I ...
user avatar
  • 263
26 votes
3 answers
20k views

Does the partition key also have to be part of the primary key?

I am partitioning a table based on a column that is not a primary key? I've read some conflicting information today on whether the partition column must be a part of the primary key. My gut says no, ...
user avatar
  • 1,881
22 votes
2 answers
7k views

SQL Server does not optimize parallel merge join on two equivalently partitioned tables

Apologies in advance for the very detailed question. I have included queries to generate a full data set for reproducing the problem, and I am running SQL Server 2012 on a 32-core machine. However, ...
user avatar
22 votes
2 answers
876 views

Is it possible to force the optimizer to eliminate irrelevant tables in this partitioned view?

I'm testing different architectures for large tables and one suggestion that I've seen is to use a partitioned view, whereby a large table is broken into a series of smaller, "partitioned" tables. 1, ...
user avatar
  • 10.4k
22 votes
2 answers
5k views

What is the modern way to partition PostgreSQL across machines, when the data is "naturally partitionable"

After several years of dwelling into the "NoSQL" space, now I have a problem that is quite "relational" in its nature. Today I see data stores with quite different eyes than before. Things like Riak ...
user avatar
  • 321
21 votes
1 answer
1k views

Seek and you shall Scan... on partitioned tables

I've read these articles in PCMag by Itzik Ben-Gan: Seek and You Shall Scan Part I: When the Optimizer Doesn't Optimize Seek and You Shall Scan Part II: Ascending Keys I’m currently having a "...
user avatar
21 votes
1 answer
1k views

Statistics disappear after incremental update

We have a large partitioned SQL Server database utilizing incremental statistics. All of the indexes are partitioned aligned. When we try to rebuild a partition online by partition all of the ...
user avatar
  • 313
20 votes
2 answers
53k views

Get all partition names for a table

I want to list all the partitions created by dynamic triggers in PostgreSQL 9.1. I was able to generate a count of partitions using this related answer by Frank Heikens. I have a table foo with an ...
user avatar
19 votes
2 answers
19k views

Many columns vs few tables - performance wise

Yes, I am aware that data normalization should be my priority (as it is). I've got a table with 65 columns storing vehicle data with columns: used_vehicle, color, doors, mileage, price and so forth, ...
user avatar
18 votes
1 answer
12k views

Database archive solutions

In continuation to a question posted by me on Is it a good idea to move high-volume and highly-accessed tables to a separate database?, am looking out for different techniques/solutions available for ...
user avatar
  • 957
17 votes
4 answers
11k views

Can I move rows between partitions by updating the partition key?

I would think that this would be a fairly simply question, but I've actually had a difficult time finding an answer for this. The question: Can you move rows of data within a partitioned table from ...
user avatar
  • 6,253
17 votes
4 answers
4k views

SQL large table design

I have a general question about SQL Server 2008 table(s) design. We currently have a table that is over 600GB and grows at about 3GB a day. This table has the appropriate indecies but is becoming a ...
user avatar
  • 544
17 votes
2 answers
4k views

effective mysql table/index design for 35 million rows+ table, with 200+ corresponding columns (double), any combination of which may be queried

I am looking for advice on table/index design for the following situation: I have a large table (stock price history data, InnoDB, 35 million rows and growing) with a compound primary key (assetid (...
user avatar
  • 171
16 votes
4 answers
3k views

Is it a good idea to Partition a table (in MS SQL) based on date, when there's a clustered index present on ID (INT)

I have a table in MS SQL Server . Table Size: 806 GB Rows : 1.2 billion Index Space : 1.2 GB Table Usage: Logging from the Web Service calls 99.9% is usage is from the logging, developers look ...
user avatar
  • 263
15 votes
2 answers
9k views

SQL Server 2008 - Partitioning and Clustered Indexes

So let me preface by saying I do not have total control over my db design, so a lot of the aspects of the current system cannot be changed for the purposes of this scenario. Comments about how we ...
user avatar
  • 17.8k
15 votes
2 answers
5k views

Database redesign opportunity: What table design to use for this sensor data collection?

Background I have a network of approximately 2000 sensors, each of which has about 100 data points that we collect on 10 minute intervals. These data points are typically int values, but some are ...
user avatar
  • 447
14 votes
2 answers
5k views

How accurate is the sys.partition.rows column?

The system view sys.partitions has a column "rows" that is the total number of rows in a given partition. For a table that is not partitioned (or only has one partition depending on how you look at ...
user avatar
13 votes
1 answer
5k views

Switching Data In Fails with "allows values that are not allowed by check constraints or partition function on target table"

Given the following -- table ddl create table dbo.f_word( sentence_id int NULL, sentence_word_id int NULL, word_id int NULL, lemma_id int NULL, source_id int NULL, ...
user avatar
  • 10.4k
13 votes
2 answers
39k views

Effectively handle 10-100 millions row table of unrelated data

What are the common approaches to boost read/write performance of table with up to 100 millions of rows? Table has columnSEGMENT_ID INT NOT NULL, where each segment has about 100.000-1.000.000 rows. ...
user avatar
  • 387
13 votes
4 answers
5k views

Table partitioning for archiving data

Scenario: two databases: DB_A and DB_Archive with one very big table called tableA. every day, records older than 60 days are deleted from DB_A and moved to DB_Archive mainly to leave thing "...
user avatar
  • 446
13 votes
1 answer
674 views

Disk file effects of delete and vacuum

I have a very frequently updated table with 240 million rows (and growing). Every three hours 1.5 million rows are inserted and 1.5 million are deleted. When I moved the cluster to a SSD this bulk ...
user avatar
  • 1,045
12 votes
1 answer
289 views

How can I interpret the results of these DMVs to help me evaluate our partitioning strategy?

Version: SQL Server 2008 R2 Enterprise Edtn. (10.50.4000) In attempt to evaluate our partitioning strategy, I wrote this query to get the access methods against indexes on partitions (in the broadest ...
user avatar
  • 10.4k
12 votes
1 answer
2k views

Storing and querying rolling data in PostgreSQL

I have a large quantity of weather model data being put into a PostgreSQL database. The machine has 8 cores and 16 GB of RAM. I'm running PostgreSQL 9.3 with PostGIS 2.1. Each table will have a ...
user avatar
  • 231
11 votes
4 answers
10k views

SQL Server Partitioning - what to use for partition key?

I've never worked with SQL Server partitioning but I currently faced with designing a database for which the volumes probably warrant it. The system is for coupons. The coupons are to be issued ...
user avatar
11 votes
1 answer
795 views

Is there appreciable overhead from having a large proportion of empty table partitions in SQL Server?

I recently inherited a project using partitioning on date where a daily scheduled task implements a sliding window scheme with 30 days in the past and 60 future dates. In reality the data that is ...
user avatar
  • 78.5k
11 votes
1 answer
6k views

MySQL Partitioning: Is there a performance tradeoff between number of partitions and size of each partition?

I have a large table (several 100 million rows) that I would like to efficiently partition. My question is whether there is a tradeoff between partition size and number of partitions. As far as I ...
user avatar
11 votes
2 answers
10k views

Partitioning on a single filegroup

I have some very large tables in my database, but a substantial chunk of this data is "old". Due to circumstances beyond my control, I am not allowed to remove this "old" data. The other ...
user avatar
11 votes
1 answer
2k views

Partition constraint not used for joins involving tables partitioned by timestamp

I have a partitioned table structure like: CREATE TABLE measurements ( sensor_id bigint, tx timestamp, measurement int ); CREATE TABLE measurements_201201( CHECK (tx >= '2012-01-...
user avatar
  • 489
10 votes
1 answer
24k views

Join tables in two databases make query slow? is better to partition db?

I have a table1 in db1 and table2 in db2 on SQL Server 2008 R2. If I make a select query joining both tables, it's really slow to get the results. A simple query like SELECT * FROM db1.dbo.table1 ...
user avatar
10 votes
2 answers
1k 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 ...
user avatar
10 votes
3 answers
4k views

Is there an advantage to not partition aligning an index?

I have the privilege of managing a large partitioned OLAP table. While reviewing this table I noticed one of the indexes is not aligned with the partitioning scheme. Since the author is not ...
user avatar
  • 313
10 votes
1 answer
17k views

Partitioning vs Indexes

I'm very new to databases, so I apologize if my question is very basic... Anyways, I'm creating a table with what seems like alot of data (500 million rows right now, and potentially twice as much in ...
user avatar
10 votes
2 answers
2k views

How to Prevent Partitioned Columnstore Deadlocks on SELECT

I have three Clustered Columnstore Index (CCI) tables in SQL Server 2016. All of these CCIs are in the same partitioning scheme, based on Tenant ID. Lately, and inconsistently, I am getting deadlocks ...
user avatar
10 votes
3 answers
2k views

Why NOT partition?

When would one NOT want to partition a database? (thinking MySQL partitioning) In my case I'll be starting with a couple millions rows, it should grow from there. Primary key on a character field ...
user avatar
  • 165
10 votes
2 answers
227 views

SQL Server 2008 R2 Partitioning - same FileGroup, 1 File, 2 partition_numbers - HELP

It's my first go at partitioning in SQL Server, I learned from the Brent Ozar guide which is fantastic :) A few times I have ran into a weird scenario; when I run: SELECT * FROM ph.FileGroupDetail ...
user avatar
  • 101
9 votes
2 answers
26k views

Does SQL Server 2016 Standard Edition support table partitioning?

I want to upgrade my SQL Server 2008 Enterprise Edition to SQL Server 2016 Standard Edition; however, one database makes use of table partitions over multiple file groups (used on a large logging ...
user avatar
9 votes
1 answer
1k views

Offline Index Rebuild on a Partitioned Table

If I partition a table with ntext, text or image datatypes and rebuild an index on a single partition with online = off, does that lock the whole table or just the partition in question?
user avatar
  • 1,480
9 votes
1 answer
161 views

Why does performing a delete on my partitioned view result in a Clustered Index Insert?

I have a partitioned view that has the below insert trigger (poor mans partition). When I perform a DELETE, I get the below query plan: delete from factproductprice where pricedate = '20170725' ...
user avatar
  • 213
9 votes
1 answer
21k views

ALTER TABLE ... SWITCHing from regular table to partitioned table fails

The code below does the following: Creates a database play_partition in C:\TEMP Creates two identical partitioned tables play_table and archive_play_table Switches play_table partition 1 to ...
user avatar
9 votes
2 answers
539 views

Getting a realistic query plan when partitioning with low volumes of data

We're using partitioning to reduce the amount of blocking our OLTP system experiences due to locks, with a partition scheme that splits the working tables into 100 partitions based on a customer id. ...
user avatar
9 votes
2 answers
2k views

How to best store Google ngrams in a database?

I downloaded google onegrams some days ago and it’s already a huge amount of data. I inserted the first of 10 packages into mysql and now I have a 47 million record database. I am wondering how one ...
user avatar
9 votes
2 answers
2k views

To partition or not to partition?

Having already read several questions on SO, external blog posts and manual SO: Foreign Key constraint to partitioned table in Pg dba.SE: Different ways of handling FK to partitioned table in Pg ...
user avatar
9 votes
2 answers
17k views

Should I group by and then join, or join and then group by?

(oracle's optimizer does the same thing for both approaches when just two tables are involved, thus my question involves three tables) Assume three tables t1, t2 and t3 with a pairwise 1:n ...
user avatar
  • 253
9 votes
1 answer
1k views

Why my database mirror break down after changing file group settings from RESTRICTED_USER to MULTI_USER?

My environment is the following: VMWare 5.5 vitalized server MS Windows Server 2008R2 Enterprise domain and SQL Server 2008 R2 Enterprise. Centralized storage with fibre-channel connection. I have ...
user avatar
8 votes
3 answers
51k views

Is table partitioning improving performance? Is it worth it?

I have just been involved in a project on which I will have to develop a data migration process and a web interface that uses an already existing SQL Server DB. This DB was developed by another person ...
user avatar
  • 545
8 votes
2 answers
1k views

Round robin vs Demand Parallelism partitioning

This is related to having a common key driver on top of the query with a nested loops, and the parallelism for the rows coming out of driver being either demand type or round robin type. I would have ...
user avatar
  • 304

1
2 3 4 5
20