Questions tagged [partitioning]

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

Filter by
Sorted by
Tagged with
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
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
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
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
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
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
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
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
4 votes
2 answers
4k views

Separate tables or partition one huge table?

I'm building a rather large table that includes a particular column, my_column. There are a small-ish number of possible values for my_column, and each unique value of my_column should have the same ...
user avatar
1 vote
3 answers
3k views

Improve performance by partitioning

I'm using SQL Server 2012. I’m using CTE to create an orders table from the items table. The final count for the orders table is around 120 million. It is taking about 2 hours for the whole process. ...
user avatar
2 votes
1 answer
1k views

Splitting Existing Partitions in SQL 2014

I have a SQL Server table that contains over 20 billion rows, with 13 billion rows in the last partition. The table is currently partitioned 6 ways by a "Time" field: 2011 2012 2013 2014 2015 2016/...
user avatar
  • 340
4 votes
2 answers
45k views

How to remove a table partition

I would like to remove a partition from a table in a SQL Server 2012 database. The table has a primary key but the partition column is not a primary key. I would also like to keep the data structure ...
user avatar
  • 167
5 votes
3 answers
26k views

Dynamic Table partitioning on a daily basis

I have a SQL Server database which contains two tables -- Acks and Logs. These two tables are related in logic but not in a relational database way. Basically, every message that comes in gets saved ...
user avatar
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
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
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
7 votes
2 answers
11k views

how to generate the creation script for partition function and partition schema?

I have a script to generate the create table script. You can see it on my answer to this question. However, now I need to script my partition functions and partition schemes On this question here, ...
user avatar
6 votes
2 answers
685 views

Trigger on partitioned table and performance

I'm not sure if what I've found is a bug, but it certainly looks like it. I wasn't able to find much information about it, so I've decided I'll put it here. So, in a nutshell, I'm facing terrible ...
user avatar
6 votes
3 answers
6k 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 ...
user avatar
4 votes
1 answer
518 views

Proper Database Partitioning

I had asked a previous question "Proper technique for storing users event data" and the correct answer in my opinion was to create a database partition. Now from what I have read on it there are ...
user avatar
4 votes
1 answer
9k views

Large table and poor performance - what to do next?

We have a Windows 2008 R2 / SQL Server 2008 R2 (Standard) server that is used to host a single database. The database itself mainly consists of a single large table containing both live and historical ...
user avatar
  • 303
3 votes
2 answers
3k views

Alter existing partitioned table

I have a large table that is already partitioned by an identifier (CustomerId). I have created new customers, and want to add new partitions to the table, and want to confirm the correct way to do ...
user avatar
  • 145
2 votes
1 answer
586 views

How does subpartitioning actually work and what physical files are created?

I can't understand how the subpartitioning works in mysql. I am reading the following example: create table invoices ( customer_id int not null, product_id int not null, amount decimal (8,...
user avatar
  • 741
2 votes
3 answers
23k views

conversion of high_value to date datatype

I need to comparte the high_value which is of LONG datatype to sysdate which is of DATE datatype. I need to fetch the partition names of all the partitions which is 90 days old. The code is some what ...
user avatar
  • 99
2 votes
1 answer
855 views

how to align data with an specific partition? plus 2 other questions related to the same table partition

I am creating a table for testing purposes: begin try drop table countries end try begin catch end catch go create table countries( name nvarchar(100) not null, visit date not null, constraint ...
user avatar
2 votes
3 answers
227 views

Large database partitioning and backups of only updated data (partial backups)

I'm setting up a large SQL Server database (2-4tb), but only a small percentage of it will be updated. I'd like to partition by year so that my backups can be small. HOWEVER, some of the old data may ...
user avatar
2 votes
2 answers
1k views

Partitioned Tables and Indexes - what are the downsides?

When talking about Partitioned Tables and Indexes for tables with less than 100 partitions, no nonaligned indexes: with that I mean: Nonaligned index An index partitioned ...
user avatar
2 votes
1 answer
3k views

Can't execute dynamic DDL, argument is NULL [closed]

I'm running PostgreSQL 9.4.4. I'm new to Postgres (used to Oracle & SQL Server) so I might be doing something really silly. I'm trying to dynamically create partitions for a table. I've got the ...
user avatar
  • 195
1 vote
2 answers
268 views

Choosing what table fields to partition on

I'm having a tough time deciding whether or not to partition my table based on a unix timestamp or by a descriptor attribute. It's just over a billion rows currently. My table basically contains: ...
user avatar
1 vote
2 answers
2k views

SQL Partition Function without actual values [duplicate]

I would like to partition a column by fiscalyear. How Do I create a partition function without harding coding values? This is what I currently have below. create table dbo.CustomerAccount ( ...
user avatar
1 vote
1 answer
693 views

Getting insight into partitions of a table in Azure Synapse (similar to SQL Server)

I'm using Dedicated SQL-Pools AKA Azure Synapse (which is different from serverless/on-demand Synapse that comes with Azure Synapse Analytics). And According to Azure: While the syntax of ...
user avatar
  • 119
1 vote
2 answers
4k views

How to split a MySQL table file?

I have a table in a MySQL database for which innodb_file_per_table is enabled. One of my tables is about 20GB and growing. How can I partition the table into separate files? Suppose that I have a ...
user avatar
  • 244
0 votes
1 answer
51 views

Replication reverted partitioned table to primary filegroup, can I re-link to the partitions?

We have 2 servers: Server A. production (no partitions) Server B. New Server (partitions and compression) We restored a backup to the new server, partitioned a large table by datetime and turned on ...
user avatar
  • 75
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
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
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
7 votes
1 answer
8k views

Setting up a Postgresql Partition with foreign key

I have a Ubuntu 12.04 Server with PostgreSQL 9.1 installed. I am trying to setup Partitioning on a existing Postgres DB(say 'xyz') which has very huge tables and some have foreign keys set on them. ...
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
6 votes
1 answer
3k views

Will table partitioning speed up my queries?

My database is set up with what we call our staging tables and our live tables. Currently for we have about 2 million rows for a table called products. Our customers we call manufacturers load their ...
user avatar
  • 173
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
6 votes
1 answer
2k views

How do I create a partition using a bit field in SQL Server 2014

I have a table where data is archived. I need to keep this data as it is rarely viewed. Instead of moving archived data to a separate table, I wanted to create a partition on the Archived field. When ...
user avatar
  • 163
5 votes
3 answers
4k views

Is it a good practice to apply Vertical Partition to a newly created model?

Suppose you are modeling the ER model for a new Object Oriented Web Application. There is a table with 20 columns, and there are going to be a lot of INSERTs populating 10 columns, and a lot of ...
user avatar
5 votes
2 answers
3k 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 ...
user avatar
0 votes
1 answer
2k views

MySQL Read Speed and Partitioning on Separate Drives

Introduction Simple project but for an heavy read load -90% reads- over a table(~20Milion Rows ever-growing where I have to boost performance and ensure response low time for read queries) that was ...
user avatar
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
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
7 votes
1 answer
14k views

How to create a partitioned table based on date?

I'm trying to create a partitioned table where partitioning is determined by OrderDate. I'm trying to create a clustered index on OrderDate and a nonclustered primary key on OrderID. But I get an ...
user avatar
  • 5,203
7 votes
1 answer
5k views

Performance issues with inherited tables and indices

I have a PostgreSQL database with a master table and 2 child tables. My master table: CREATE TABLE test ( id serial PRIMARY KEY, date timestamp without time zone ); CREATE INDEX ON test(...
user avatar
  • 275
7 votes
2 answers
480 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 ...
user avatar
  • 335