SQL Database, Table, and Data Partitioning: When and How to Do It

Find all the SQL queries the rest of the post at the source.  When I first came across table partitioning and started searching, I realized two things. First, it is a complex operation that requires good planning. Second, in some cases, it can be proven extremely beneficial, while in others, it can be a complete headache.

So the first question is when investing time in table partitioning seems a good way to go.  The typical case when you consider applying partitioning is the following:

“A company that maintains a large database, stores all of the data that are produced as a result of its activity. As time goes by, the velocity of the data increases more and more and queries become slower and slower as whole tables need to be scanned. But what happens in cases where there is no need for a full scan? Imagine the compilation of monthly business intelligence reports. The only data that are actually needed are those that were produced during the last month. It becomes evident that there are cases where it would be extremely helpful to be able to have control over the data that our queries take into consideration while being evaluated. ”

From what I have figured out, I would say that before creating any partitions, you should try to exhaust all other alternative options including table indexing and revision of queries. If you conclude that the only solution is table partitioning, you will have to pay special attention on how to implement it. The benefits that such an implementation can provide are constrained by the selection of the partition key and the granularity.

Regarding the first factor, you should keep in mind that partitioning can only occur in a single column and that your queries must include that column. If, for example, you have created a partitioned table, in order to run a query over the “eliminated” data, the partition indicator must be included in the query. Otherwise, a full scan will be performed.  For this, it is important to review the way in which your queries access the table in order to choose the most suitable column for partitioning.

As for the granularity, if your partitions are very large, then you won’t see any particular improvement on the performance. On the other hand, very small partitions can be hard to handle. Furthermore, even in the case of a good design, you won’t be able to see significant improvement in performance unless you are dealing with really huge tables.

If all the above concerns are being evaluated and you have concluded that table partitioning serves your needs, then the benefits you are going to gain include:

• The relative speedup of queries that require only portions of large data sets. In this case, the optimizer eliminates searching in partitions that do not have relevant information.
• Faster data load.
• Faster deletion of old data limited to certain partitions, if they are no longer needed.
• Faster archival of rarely used or old data can be migrated to cheaper and slower storage media.

That being said, let’s move on and see how can table partitioning be implemented in PostgreSQL, MS SQL Server, and Google BigQuery.

Regarding the actual implementation, the main idea behind table partitioning is that we are going to create a “parent” table and a number of “children” tables that are the ones actually responsible for holding the data. The number of the children is not necessarily constant and can grow as time goes by.

Of course, creating partitions does not mean that the “global” table stops to exist. You can still query it for events that span the whole period.

For sake of simplicity, in this post, we are going to work with a table that contains only two columns. Over this, we are going to make daily partitions. In real life, databases include much more columns but the idea remains exactly the same.

Find all the SQL queries the rest of the post at the source.