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.
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.
This blog post is part of our helpful SQL short guides
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.