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

Eleni Markou

When I first came across table partitioning and started searching, I realized two things. First, that it is a complex operation that requires good planning and second, that in some cases can be proven extremely beneficial while in others 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 to 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 BiqQuery.

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.

PostgreSQL databases

So, let’s first create our toy table:

PostgreSQL: Create table

CREATE TABLE testing_table(receipt_id BIGINT, date DATE);

Now that we have our table we need to define a function that will be triggered during inserting in order to check whether a new partition needs to be created based on some criterion.

The date field is the one controlling the name of the newly created partition.

PostgreSQL : Create Function

CREATE OR REPLACE FUNCTION partition_function() RETURNS trigger AS
$BODY$
DECLARE
partition_date TEXT;
partition TEXT;
BEGIN
partition_date := to_char(NEW.date,'YYYY_MM_DD');
partition := TG_RELNAME || '_' || partition_date;
IF NOT EXISTS(SELECT relname FROM pg_class WHERE relname=partition) THEN
RAISE NOTICE 'A partition has been created %',partition;
EXECUTE 'CREATE TABLE ' || partition || ' (check (date = ''' || NEW.date || ''')) INHERITS (' || TG_RELNAME || ');';
END IF;
EXECUTE 'INSERT INTO ' || partition || ' SELECT(' || TG_RELNAME || ' ' || quote_literal(NEW) || ').* RETURNING receipt_id;';
RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

Now we just need to declare a trigger which will activate the above function each time an insert action is going to be executed.

PostgreSQL: Create Trigger

CREATE TRIGGER partition_trigger
BEFORE INSERT ON testing_table
FOR EACH ROW EXECUTE PROCEDURE partition_function();

There are of course many ideas regarding how you can improve this fairly simple implementation. For example, you can create the necessary tables ahead of time (one by day/month/year) and instead of a trigger, use an appropriate rule over them. A cron job which automatically creates the next X daily/monthly tables would be a good idea in this direction.

You can also check this interesting maintained extension for partition management in PostgreSQL which can be found here.

MS SQL Server databases

When it comes to Microsoft SQL Server things are a bit different as this database system does not support dynamic partitions and so partitioning the table manually can be a huge maintenance issue.

That being said, in order to create a partitioned table a similar procedure to the one previously presented must be followed. This time we will create a monthly partition.

MS SQL Server: Create table

CREATE TABLE testing_table(receipt_id BIGINT, date DATE);

Now we need to specify exactly how the table is going to be partitioned by the partitioning column, in this case, the date, along with the range of values included in each partition. Regarding partition boundaries, you can specify either the LEFT or the RIGHT.

MS SQL Server: Create Function

CREATE PARTITION FUNCTION partition_function (int)
AS RANGE LEFT FOR VALUES (20170630, 20170731, 20170831);

In this example, we have defined the following 4 partitions:

Partition No. 1         ⇒    All records with date ≤ 2017-06-30

Partition No. 2     ⇒     All records with date > 2017-06-30  & date ≤ 2017-07-31

Partition No. 3     ⇒     All records with date >  2017-07-31  & date ≤ 2017-08-31

Partition No. 4     ⇒    All records with date > 2017-08-31

MS SQL Server: Create Partition Scheme

CREATE PARTITION FUNCTION partition_function (int)
AS RANGE LEFT FOR VALUES (20170630, 20170731, 20170831);

Having followed these steps, if you want to determine at the partition at which a record will be placed you can do the following:

SELECT ‘20170613’ date, $PARTITION.partition_function(20170613) PartitionNumber
UNION
SELECT ‘20170713’ date, $PARTITION.partition_function(20170713) PartitionNumber
UNION
SELECT ‘20170813’ date, $PARTITION.partition_function(20170813) PartitionNumber
UNION
SELECT ‘20170913’ date, $PARTITION.partition_function(20170913)

 Big Query databases

For those using BigQuery, partitioning of a table can be done from within the BQ interface without using any SQL code. From there you define how to split large tables into smaller ones, where each partition contains monthly or daily data only.

A detailed explanation of the whole procedure can be found in Google’s official documentation here.

Outro

As everything in life, it seems that table partitioning comes at a cost. Nevertheless, if implemented in the right way under the right conditions, the whole concept can be a real lifesaver to your query performance over huge database tables and also help you with data handling in the sense of easier roll-in and roll-out operations as well as data migration.

Image: Variations (Progressive Motif) – Paul Kleem, 1927 – The Berggruen Klee Collection – The Metropolitan Museum of Art

Get our latest posts in your email to stay on top of everyone.