Postgres is very good at adding features built upon existing features. This process was used when
table partitioning was added in Postgres 8.1 in 2005. It was built
upon three existing features:

Though constraint_exclusion (which
controls partition selection) has received some usability improvements since 2005, the basic feature has remained unchanged.
Unfortunately, the feature as implemented has several limitations which still need to be addressed:

  • Partitioning requires users to create CHECK constraints to define the contents of each partition.
  • Because of CHECK constraints, there is no central recording of how the partitions are segmented.
  • Also because of CHECK constraints, there is no way to rapidly evaluate the partition contents — each CHECK
    constraint must be re-evaluated. Due to this expensive operation, partition selection is done while the plan is being created, rather
    than executed, meaning that only query constants can be used to select partitions — joining to a partitioned column cannot eliminate
    unnecessary partition scans.
  • CHECK constraint overhead also adds a serious performance overhead for systems using thousands of partitions.
  • To properly route write queries to the proper partition, triggers or rules must be created on the parent table. They must know about
    all partitions, and anytime a partition is created or removed, this trigger or rule must be updated, unless some type of partition-name
    wildcard pattern is used in the trigger code.
  • There is no parallel partition scanning, though that is more due to the lack of Postgres
    parallelism, rather than a partitioning limitation.

There is a wiki related to table partitioning that covers some of this in detail.

Continue Reading »