Pg_pathman Beta Release

Partitioning in PostgreSQL is traditionally implemented using table inheritance. Table inheritance allow planner to include into plan only those child tables (partitions) which are compatible with query. Simultaneously a lot of work on partitions management remains on users: create inherited tables, writing trigger which selects appropriate partition for row inserting etc. In order to automate this work pg_partman extension was written. Also, there is upcoming work on declarative partitioning by Amit Langote for PostgreSQL core.

In Postgres Professional we notice performance problem of inheritance based partitioning. The problem is that planner selects children tables compatible with query by linear scan. Thus, for query which selects just one row from one partition it would be much slower to plan than to execute. This fact discourages many users and this is why we’re working on new PostgreSQL extension: pg_pathman.

pg_pathman caches partitions meta-information and uses set_rel_pathlist hook in order to replace mechanism of child tables selection by its own mechanism. Thanks to this binary search algorithm over sorted array is used for range partitioning and hash table lookup for hash partitioning. Therefore, time spent to partition selection appears to be negligible in comparison with forming of result plan nodes. See postgrespro blog post for performance benchmarks.

pg_pathman now in beta-release status and we encourage all interested users to try it and give us a feedback. pg_pathman is compatible with PostgreSQL 9.5 and distributed under PostgreSQL license. In the future we’re planning to enhance functionality of pg_pathman by following features.

• Execute time selection of partitions using custom nodes (useful for nested loops and prepared statements);
• Optimization of ordering output from partitioned tables (useful for merge join and order by);
• Optimization of hash join when both tables are partitioned by join key;
• LIST-partitioning;
• HASH-partitioning by attributes of any hashable type.

Despite we have pg_pathman useful here and now, we want this functionality to eventually become part of PostgreSQL core. This is why we are going to join work on declarative partitioning by Amit Langote which have excellent DDL infrastructure and fulfill it with effective internal algorithms.