Keith's Ramblings…

WARNING: If accidentally read, induce vomiting

Managing Constraint Exclusion in Table Partitioning

with 7 comments

One of the biggest advantages of table partitioning in databases is taking advantage of a feature called constraint exclusion. The PostgreSQL docs explain this best:

With constraint exclusion enabled, the planner will examine the constraints of each partition and try to prove that the partition need not be scanned because it could not contain any rows meeting the query’s WHERE clause. When the planner can prove this, it excludes the partition from the query plan.

Using the following table partitioned on col1, some examples of how this works will follow

Without constraint exclusion, doing a simple SELECT * for a smaller subset of the partition set still does a scan across all tables (constraint exclusion is turned on by default with the setting “partition”).

Even though this is doing INDEX scans, doing this against a partition set with much larger amounts of data would be extremely expensive and cause significant delays. With constraint exclusion on, you can see the plan is greatly simplified and only the tables that need to be queried actually are

However, when you create a partitioned set, usually only the column that has a constraint on it is the column that is controlling the partitioning. As soon as you do a query with a WHERE condition on one of the other columns, you lose all the advantages you thought you gained. In this case its even worse because it causes full sequential scans across the whole set. Indexes could help, but again, not if your partition set is very large.

You could try and add constraints before you insert the data, but often that is either hard or impossible. Another alternative is to go back and create constraints on old partitions that are no longer being edited based on the data they contain. That is the solution I built into pg_partman with the 1.5.0 release.

The second constraint is the new one that was created based on the data currently in the child table. The configuration column constraint_cols is an ARRAY that can contain as many columns for the given table that you’d like to have constraints set for. The above is how you can go back and add additional constraints to an already existing partition set. The python script will apply the constraint to all relevant child partitions. It determines which children to apply the constraints to by the premake pg_partman configuration value for that partition set (default is 4, so it will not apply constraints to child tables newer than the current and the previous 4. See the pg_partman docs for more details on what the premake value is used for).

So, while it won’t apply constraints to all the old children, it can at least allow constraint exclusion to potentially exclude a huge majority of them. In this case it was able to exclude partitions _p30, _p40 & _p50. As more partitions are added, more constraints would get added to older partitions and at most the tables that would need to be scanned would be the current one, the last 4, the empty future ones and whichever partitions contained the relevant data. On a partition set with thousands of partitions and/or partitions with millions of rows each, that would have significant performance benefits.

The big caveat with this is that it would prevent edits on any of these older tables if the value would change the constraint boundaries. If there comes a time when you must edit this data, some included functions can help. The drop_constraints() and apply_constraints() functions can drop then reapply all constraints managed by pg_partman on a given child table (see docs for their parameters). They’ve been designed to cleanly handle if constraints created by pg_partman already exist or if the columns contain only NULL values. These functions only work on a single child table at a time. If you need to drop/apply to the entire partition set, the python script used  above can make that much easier.

You can set this up during partition creation with a parameter to the create_parent() function. Or it can be added at any time later like I did above. Once the additional constraint columns are configured, the normal partition maintenance procedures that pg_partman runs will take care of creating the constraints for you.

This is a pretty tricky situation to handle and its one of the things that makes partitioning a big challenge to set up correctly. And it’s something most people don’t realize until they’ve already gotten their partitioning set up and only encounter the issue when they start noticing the performance impacts of scanning their now very large partition set. Hopefully this feature will allow more people to take advantage of partitioning when long term storage of data is required within PostgreSQL. If anyone has any feedback or suggestions for making this feature better, I’d greatly appreciate it.

Written by Keith

January 8th, 2014 at 11:21 am

  • Jeff Amiel

    Thank you. Thank you. Thank you. This is the biggest reason I have avoided large scale partitioning up until now.

    • keithf4

      Thanks for the feedback! Please let me know if you come across any issues while using it.

  • Jeff Amiel

    For the life of me, I cannot get create_parent to set up the constraint_cols –
    I can manually update the part_config table (as you did in your post) and set up constraints afterwords – but I cannot do it when setting up from scratch.
    Create_parent() seemingly takes my constraints_cols array with no complaint (single column) but nothing ends up in the column in part_config – and hence the additional constraints aren’t put on the generated partition tables. :(

    • keithf4

      Woops! Mistake on my part. I got the create_parent() constraint_cols stuff working for id partitioning, but not for time based partitioning. I’ll work on a fix and get it out as soon as I can. Thanks for letting me know!

    • keithf4

      Pushed out v1.5.1 with a fix for this. Let me know if it works ok for you.

  • arun desai

    Hi , assume i have created partitions , now i want to delete all partions along with trigger function. can that be done in one function. i am aware of drop_partition_time function. but it only deletes partitions, wont delete trigger function. basically i want to recreate partitions.

    • keithf4

      Please look at the undo functions and the associated python script.