The pg_pathman module provides optimized partitioning mechanism and functions to manage partitions.
The extension is compatible with:
- PostgreSQL 9.5, 9.6, 10;
- Postgres Pro Standard 9.5, 9.6;
- Postgres Pro Enterprise.
Partitioning means splitting one large table into smaller pieces. Each row in such table is moved to a single partition according to the partitioning key. PostgreSQL <= 10 supports partitioning via table inheritance: each partition must be created as a child table with CHECK CONSTRAINT. PostgreSQL 10 provides native partitioning. Despite the flexibility, this approach forces the planner to perform an exhaustive search and to check constraints on each partition to determine whether it should be present in the plan or not. Large amount of partitions may result in significant planning overhead.
The pg_pathman module features partition managing functions and optimized planning mechanism which utilizes knowledge of the partitions' structure. It stores partitioning configuration in the pathman_config table; each row contains a single entry for a partitioned table (relation name, partitioning column and its type). During the initialization stage the pg_pathmanmodule caches some information about child partitions in the shared memory, which is used later for plan construction. Before a SELECT query is executed, pg_pathman traverses the condition tree in search of expressions.
Based on the partitioning type and condition's operator, pg_pathman searches for the corresponding partitions and builds the plan. Currently pg_pathman supports two partitioning schemes:
- RANGE - maps rows to partitions using partitioning key ranges assigned to each partition. Optimization is achieved by using the binary search algorithm;
- HASH - maps rows to partitions using a generic hash function.
Feature highlights:
- HASH and RANGE partitioning schemes;
- Partitioning by expression and composite key;
- Both automatic and manual partition management;
- Support for integer, floating point, date and other types, including domains;
- Effective query planning for partitioned tables (JOINs, subselects etc);
RuntimeAppend&RuntimeMergeAppendcustom plan nodes to pick partitions at runtime;PartitionFilter: an efficient drop-in replacement for INSERT triggers;- Automatic partition creation for new INSERTed data (only for RANGE partitioning);
- Improved
COPY FROMstatement that is able to insert rows directly into partitions; - UPDATE triggers generation out of the box (will be replaced with custom nodes too);
- User-defined callbacks for partition creation event handling;
- Non-blocking concurrent table partitioning;
- FDW support (foreign partitions);
- Various GUC toggles and configurable settings.
