Disadvantages of table partitioning (e.g. SQL Server)

Question

I have never partitioned a table, however from what I have read it has many advantage in terms of querying and archiving data.

Is it superior to creating indexes? Are there any disadvantages? Is the time spent on partitioning the only cost?

Answer 1

There are two reasons to partition a table - if its very large or if you need to quickly add or drop off a slice of data.

On a vary large audit table you may find it useful to divide the table into monthly partitions. This way when you search for rows for a given month SQL Server only has to look through the partition associated with that month. When doing selects you can treat the partitioned as a typical table - no special syntax is necessary.

If at the beginning of a new month you may want to drop the oldest month of data and add the newest month it is then a simple operation to drop a partition and add a partition. SQL Server does not have to rebuild the indexes on the entire table - it just drops and adds the partition(s) internally as a meta operation and usually takes seconds.

The SQL to do so is not hard and you can arrange to backup individual partitions. But partitioning in SQL Server is only available on Enterprise Edition and partitioned tables cannot be restored to other versions of SQL Server.

Answer 2

Background:

Latency of your database query will be low if most of the operations happen in memory. Databases go to great lengths to do this automatically. They also offer tuning options to make sure your buffer cache/ buffer pool and other options can be set correctly.

Why partition the table?

As we keep adding rows to the table, size of primary key index and any other indexes on the table also grows. Once they grow to a point where the entire index does not fit in the configured size of buffer (place where it stores pages that are frequently accessed), the database engine will have to go and fetch index pages from disk and thus slowing down the query.

Its not superior or inferior to indexes, its a different technique.

Before going the partitioning route

• Check the explain plan of the query (if there is a particular access pattern) and tune it to make sure right indexes are used and disk operations are limited.
• Check the size of the table and index in question and how it relates to the configured database memory (buffer pool).
• Check if the table can be defragmented. All databases offer commands to do this (MySQL calls it OPTIMIZE, PostgreSQL calls it VACUUM)
• Check if you can add memory to server, RAM is cheap.

Advantages of partitioning

• Smaller tables, smaller indexes, lower query latency.

Disadvantages

• intrusive as application needs changes to write to the correct partition. If the number of partitions is out-grown, you have the same issue with the partitions. AFAIK full re-partitioning would be needed to increase the partition count. Certainly a non-trivial activity.