•   PostgreSQL

Beta-release of pg_pathman partitioning extension

Source:
Postgres Pro Team Subscribe to blog
Source:
Postgres Pro Team Subscribe to blog

          Partitioning is a process of dividing single big table into many smaller separate tables which could have some independent management. In PostgreSQL partitioning is traditionally implemented using inheritance mechanism. CHECK CONSTRAINT is assigned to each child table (partition) and determines which data ought to be placed to this partition. When query runs over parent table planner selects children tables which CHECK CONSTRAINTs are compatible with query. However usage of inheritance mechanism for partitioning in PostgreSQL has a lot of shortcomings. The major shortcoming is linear algorithm of children tables selection during query planning. For large number of partitions that may cause significant overhead comparable with query execution itself.

          pg_pathman extension intended to optimize query planning time by using binary search for RANGE-partitioning and hash-table for HASH-partitioning.

          On the graph above the dependency between planning time and total number of partitions is shown while only one partition gets to final plan. Planning time is compared for inheritance mechanism (original) and for pg_pathman (pathman).

          On the graph below dependency between planning time and number of selected partitions is shown (total number of partitions – 1096).

          It is shown that with small number of selected partitions and large number of total partitions planning time difference could be multiple orders of magnitude.

          pg_pathman is distributed as PostgreSQL 9.5 extension and available at github. pg_pathman will be included into PostgresPro packages. Detail information about pg_pathman could be found in README.

 

← Back to all articles