The`pg_pathman` module provides optimized partitioning mechanism and functions to manage partitions.

The extension is compatible with PostgreSQL 9.5+.

The extension is compatible with:

* PostgreSQL 9.5, 9.6, 10;

* Postgres Pro Standard 9.5, 9.6;

* Postgres Pro Enterprise;

By the way, we have a growing Wiki[out there](https://github.com/postgrespro/pg_pathman/wiki);

**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 supports partitioning via table inheritance: each partition must be created as a child table with CHECK CONSTRAINT. For example:

* 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);

* Various GUC toggles and configurable settings.

* Multi-level partitioning (ver 1.5);

* Improved referential integrity + foreign keys on partitioned tables (ver 1.5);

* Implement LIST partitioning scheme;

* Optimize hash join (both tables are partitioned by join key).

Take a look at[this page](https://github.com/postgrespro/pg_pathman/wiki/Roadmap);

To install`pg_pathman`, execute this in the module's directory:

create_hash_partitions(relation REGCLASS,

attributeTEXT,

exprTEXT,

partitions_countINTEGER,

partition_dataBOOLEAN DEFAULT TRUE,

partition_namesTEXT[] DEFAULTNULL,

tablespacesTEXT[] DEFAULTNULL)

Performs HASH partitioning for`relation` byinteger key`attribute`. The`partitions_count` parameter specifies the number of partitions to create; it cannot be changed afterwards. If`partition_data` is`true` then all the data will be automatically copied from the parent table to partitions. Note that data migration may took a while to finish and the table will be locked until transaction commits. See`partition_table_concurrently()` for a lock-free way to migrate data. Partition creation callback is invoked for each partition if set beforehand (see`set_init_callback()`).

Performs HASH partitioning for`relation` bypartitioning expression`expr`. The`partitions_count` parameter specifies the number of partitions to create; it cannot be changed afterwards. If`partition_data` is`true` then all the data will be automatically copied from the parent table to partitions. Note that data migration may took a while to finish and the table will be locked until transaction commits. See`partition_table_concurrently()` for a lock-free way to migrate data. Partition creation callback is invoked for each partition if set beforehand (see`set_init_callback()`).

create_range_partitions(relation REGCLASS,

attributeTEXT,

exprTEXT,

start_value ANYELEMENT,

p_interval ANYELEMENT,

p_countINTEGER DEFAULTNULL

partition_dataBOOLEAN DEFAULT TRUE)

create_range_partitions(relation REGCLASS,

attributeTEXT,

exprTEXT,

start_value ANYELEMENT,

p_interval INTERVAL,

p_countINTEGER DEFAULTNULL,

partition_dataBOOLEAN DEFAULT TRUE)

Performs RANGE partitioning for`relation` by partitioningkey`attribute`,`start_value` argument specifies initial value,`p_interval` sets the default range for auto created partitions or partitions created with`append_range_partition()` or`prepend_range_partition()` (if`NULL` then auto partition creation feature won't work),`p_count` is the number of premade partitions (if not set then`pg_pathman` tries to determine it based onattribute values). Partition creation callback is invoked for each partition if set beforehand.

Performs RANGE partitioning for`relation` by partitioningexpression`expr`,`start_value` argument specifies initial value,`p_interval` sets the default range for auto created partitions or partitions created with`append_range_partition()` or`prepend_range_partition()` (if`NULL` then auto partition creation feature won't work),`p_count` is the number of premade partitions (if not set then`pg_pathman` tries to determine it based onexpression's values). Partition creation callback is invoked for each partition if set beforehand.

create_hash_update_trigger(parent REGCLASS)

Creates the trigger on UPDATE for HASH partitions. The UPDATE trigger isn't created by default because of the overhead. It's useful in cases when thekey attribute might change.

Creates the trigger on UPDATE for HASH partitions. The UPDATE trigger isn't created by default because of the overhead. It's useful in cases when thepartitioning expression's value might change.

create_range_update_trigger(parent REGCLASS)

CREATETABLEIF NOT EXISTS pathman_config (

partrel REGCLASSNOT NULLPRIMARY KEY,

attnameTEXTNOT NULL,

exprTEXTNOT NULL,

parttypeINTEGERNOT NULL,

range_intervalTEXT);

range_intervalTEXT,

cooked_exprTEXT);

This table stores a list of partitioned tables.

parent REGCLASS,

partition REGCLASS,

parttype INT4,

partattrTEXT,

exprTEXT,

range_minTEXT,

range_maxTEXT)

AS'pg_pathman','show_partition_list_internal'

This view lists all existing partitions, as well as their parents and range boundaries (NULL for HASH partitions).

CREATEOR REPLACE FUNCTION @extschema@.show_cache_stats()

RETURNS TABLE (

contextTEXT,

size INT8,

used INT8,

entries INT8)

AS'pg_pathman','show_cache_stats_internal'

LANGUAGE C STRICT;

CREATEOR REPLACE VIEW @extschema@.pathman_cache_stats

ASSELECT*FROM @extschema@.show_cache_stats();

Shows memory consumption of various caches.

`pg_pathman` provides a couple of[custom plan nodes](https://wiki.postgresql.org/wiki/CustomScanAPI) which aim to reduce execution time, namely: