To perform hash partitioning with pg_pathman, run the create_hash_partitions() function:

create_hash_partitions(parent_relid     REGCLASS,
                       expression       TEXT,
                       partitions_count INTEGER,
                       partition_data   BOOLEAN DEFAULT TRUE,
                       partition_names  TEXT[] DEFAULT NULL,
                       tablespaces      TEXT[] DEFAULT NULL)

The pg_pathman module creates the specified number of partitions based on the hash function. Optionally, you can specify partition names and tablespaces by setting partition_names and tablespaces options, respectively.

You cannot add or remove partitions after the parent table is split. If required, you can replace the specified partition with another table:

replace_hash_partition(old_partition       REGCLASS,
                       new_partition       REGCLASS,
                       lock_parent         BOOL DEFAULT TRUE);

When set to true, lock_parent parameter will prevent any INSERT/UPDATE/ALTER TABLE queries to parent table.

If you omit the optional partition_data parameter or set it to true, all the data from the parent table gets migrated to partitions. The pg_pathman module blocks the table for other transactions until data migration completes. To avoid downtime, you can set the partition_data parameter to false and later use the partition_table_concurrently() function to migrate your data to partitions without blocking other queries. For details, see the Section F.40.2.2.

The pg_pathman module provides the create_range_partitions() for range partitioning. This function creates partitions based on the specified interval and the initial partitioning key value. New partitions are created automatically when you insert data outside of the already covered range.

create_range_partitions(parent_relid   REGCLASS,
                        expression     TEXT,
                        start_value    ANYELEMENT,
                        p_interval     ANYELEMENT | INTERVAL,
                        p_count        INTEGER DEFAULT NULL,
                        partition_data BOOLEAN DEFAULT TRUE)

The pg_pathman module creates partitions based on the specified parameters. If you omit the optional p_count parameter, pg_pathman calculates the required number of partitions based on the specified start value and interval. If you insert new data outside of the existing partition range, pg_pathman creates new partitions automatically, keeping the specified interval. This approach ensures that all partitions are of the same size, which can improve query performance and facilitate database management.

Alternatively, you can specify an array defining the bounds of partitions to be created using the bounds parameter:

create_range_partitions(parent_relid    REGCLASS,
                        expression      TEXT,
                        bounds          ANYARRAY,
                        partition_names TEXT[] DEFAULT NULL,
                        tablespaces     TEXT[] DEFAULT NULL,
                        partition_data  BOOLEAN DEFAULT TRUE)

If required, you can also use partition management functions to add partitions manually. For example, if there is a gap between the created partitions, pg_pathman cannot fill it with a new partition in an automated mode.

By default, all the data from the parent table gets migrated to the specified number of partitions. The pg_pathman module blocks the table for other transactions until data migration completes. To avoid downtime, you can set the partition_data parameter to false and later use the partition_table_concurrently() function to migrate your data to partitions without blocking other queries. For details, see the Section F.40.2.2.

Suppose you have the test table that stores some jsonb data:

CREATE TABLE test(col jsonb NOT NULL);
INSERT INTO test
SELECT format('{"key": %s, "date": "%s", "value": "%s"}',
              i, current_date, md5(i::text))::jsonb
FROM generate_series(1, 10000 * 10) as g(i);

To partition this data by range of the key value, you need to extract this value from the jsonb object and convert it to a numeric type, such as bigint:

SELECT create_range_partitions('test', '(col->>''key'')::bigint', 1, 10000, 10);

pg_pathman splits the parent table into ten partitions, with each partition storing 10000 rows:

SELECT * FROM pathman_partition_list;
 parent | partition | parttype |              expr               | range_min | range_max
--------+-----------+----------+---------------------------------+-----------+-----------
 test   | test_1    |        2 | ((col ->> 'key'::text))::bigint | 1         | 10001
 test   | test_2    |        2 | ((col ->> 'key'::text))::bigint | 10001     | 20001
 test   | test_3    |        2 | ((col ->> 'key'::text))::bigint | 20001     | 30001
 test   | test_4    |        2 | ((col ->> 'key'::text))::bigint | 30001     | 40001
 test   | test_5    |        2 | ((col ->> 'key'::text))::bigint | 40001     | 50001
 test   | test_6    |        2 | ((col ->> 'key'::text))::bigint | 50001     | 60001
 test   | test_7    |        2 | ((col ->> 'key'::text))::bigint | 60001     | 70001
 test   | test_8    |        2 | ((col ->> 'key'::text))::bigint | 70001     | 80001
 test   | test_9    |        2 | ((col ->> 'key'::text))::bigint | 80001     | 90001
 test   | test_10   |        2 | ((col ->> 'key'::text))::bigint | 90001     | 100001
(10 rows)

Suppose you have the test table that stores some temporal data:

CREATE TABLE test (logdate date NOT NULL, comment text);

To partition this data by month and year, you have to create a composite key:

CREATE TYPE test_key AS (year float8, month float8);

To enable automatic partition naming, run the create_naming_sequence() function passing the table name as an argument:

SELECT create_naming_sequence('test');

Register the test table with pg_pathman, specifying the partitioning key you are going to use:

SELECT add_to_pathman_config('test',
                             '( extract(year from logdate),
                                extract(month from logdate) )::test_key',
                             NULL);

Create a partition that includes all the data in the range of ten years, starting from January of the current year:

SELECT add_range_partition('test',
                           (extract(year from current_date), 1)::test_key,
                           (extract(year from current_date + '10 years'::interval), 1)::test_key);

Suppose you have the journal table with some logs, which is partitioned by month:

-- create an empty table
CREATE TABLE journal (
id      SERIAL,
dt      TIMESTAMP NOT NULL,
level   INTEGER,
msg     TEXT);

-- generate some log data into the table
INSERT INTO journal (dt, level, msg)
SELECT g, random() * 6, md5(g::text)
FROM generate_series('2015-01-01'::date, '2015-12-31'::date, '1 minute') as g;

-- partition the table by range
SELECT create_range_partitions('journal', 'dt', '2015-01-01'::date, '1 month'::interval);

If having smaller partitions makes more sense at some point, you can further split the partitions by hash or range. For example, to split the journal_1 partition into subpartitions by day, run:

SELECT create_range_partitions('journal_1', 'dt', '2015-01-01'::date, '1 day'::interval);

Similarly, you can use hash partitioning to create child partitions. For example, split the journal_2 partition into five partitions by hash using the id column as the partitioning key:

SELECT create_hash_partitions('journal_2', 'id', '5');

This table stores the list of partitioned tables. This is the main configuration storage.

CREATE TABLE IF NOT EXISTS pathman_config (
    partrel         REGCLASS NOT NULL PRIMARY KEY,
    attname         TEXT NOT NULL,
    parttype        INTEGER NOT NULL,
    range_interval  TEXT);

This table stores optional parameters that override standard pg_pathman behavior.

CREATE TABLE IF NOT EXISTS pathman_config_params (
    partrel        REGCLASS NOT NULL PRIMARY KEY,
    enable_parent  BOOLEAN NOT NULL DEFAULT TRUE,
    auto           BOOLEAN NOT NULL DEFAULT TRUE,
    init_callback  REGPROCEDURE NOT NULL DEFAULT 0,
    spawn_using_bgw BOOLEAN NOT NULL DEFAULT FALSE);

This view lists all currently running concurrent partitioning tasks.

-- helper SRF function
CREATE OR REPLACE FUNCTION show_concurrent_part_tasks()
RETURNS TABLE (
    userid     REGROLE,
    pid        INT,
    dbid       OID,
    relid      REGCLASS,
    processed  INT,
    status     TEXT)
AS 'pg_pathman', 'show_concurrent_part_tasks_internal'
LANGUAGE C STRICT;

CREATE OR REPLACE VIEW pathman_concurrent_part_tasks
AS SELECT * FROM show_concurrent_part_tasks();

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

-- helper SRF function
CREATE OR REPLACE FUNCTION show_partition_list()
RETURNS TABLE (
    parent     REGCLASS,
    partition  REGCLASS,
    parttype   INT4,
    expr       TEXT,
    range_min  TEXT,
    range_max  TEXT)
AS 'pg_pathman', 'show_partition_list_internal'
LANGUAGE C STRICT;

CREATE OR REPLACE VIEW pathman_partition_list
AS SELECT * FROM show_partition_list();

create_hash_partitions(parent_relid     REGCLASS,
                       expression       TEXT,
                       partitions_count INTEGER,
                       partition_data   BOOLEAN DEFAULT TRUE,
                       partition_names  TEXT[] DEFAULT NULL,
                       tablespaces      TEXT[] DEFAULT NULL)

Performs hash partitioning for relation by integer key expression. The partitions_count parameter specifies the number of partitions to create; it cannot be changed afterwards. If partition_data is true, all the data will be automatically migrated from the parent table to partitions. Note that data migration may take 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,
                        expression     TEXT,
                        start_value    ANYELEMENT,
                        p_interval     ANYELEMENT,
                        p_count        INTEGER DEFAULT NULL,
                        partition_data BOOLEAN DEFAULT TRUE)

create_range_partitions(relation       REGCLASS,
                        expression     TEXT,
                        start_value    ANYELEMENT,
                        p_interval     INTERVAL,
                        p_count        INTEGER DEFAULT NULL,
                        partition_data BOOLEAN DEFAULT TRUE)

create_range_partitions(relation        REGCLASS,
                        expression      TEXT,
                        bounds          ANYARRAY,
                        partition_names TEXT[] DEFAULT NULL,
                        tablespaces     TEXT[] DEFAULT NULL,
                        partition_data  BOOLEAN DEFAULT TRUE)

Performs range partitioning for relation by partitioning key defined by expression. The start_value argument specifies the initial value, p_interval sets the default range for automatically created partitions or partitions created with append_range_partition() or prepend_range_partition(). If p_interval is set to NULL, automatic partition creation is disabled. p_count is the number of premade partitions. If p_count is not set, than pg_pathman tries to determine the number of partitions based on the expression value. The bounds array defines the bounds for partitions to be created. You can build this array using the generate_range_bounds() function. Partition creation callback is invoked for each partition if set beforehand.

partition_table_concurrently(relation REGCLASS,
                             batch_size INTEGER DEFAULT 1000,
                             sleep_time FLOAT8 DEFAULT 1.0)

Starts a background worker to move data from parent table to partitions. The worker utilizes short transactions to copy small batches of data (up to 10K rows per transaction) and thus doesn't significantly interfere with user's activity.

stop_concurrent_part_task(relation REGCLASS)

Stops a background worker performing a concurrent partitioning task. Note: worker will exit after it finishes relocating a current batch.

Triggers are no longer required for INSERT and cross-partition UPDATE operations. However, user-supplied triggers are supported:

replace_hash_partition(old_partition       REGCLASS,
                       new_partition       REGCLASS,
                       lock_parent         BOOLEAN DEFAULT TRUE)

Replaces the specified partition of hash-partitioned table with another table. When set to true, the lock_parent parameter prevents any INSERT/UPDATE/ALTER TABLE queries to the parent table.

split_range_partition(partition_relid  REGCLASS,
                      split_value      ANYELEMENT,
                      partition_name   TEXT DEFAULT NULL,
                      tablespace       TEXT DEFAULT NULL)

Split range partition in two by value, with the specified value included into the second partition. Partition creation callback is invoked for a new partition if available.

merge_range_partitions(variadic partitions REGCLASS[])
      

Merge several adjacent range partitions. Partitions are automatically ordered by increasing bounds. All the data will be accumulated in the first partition, while other merged partitions are removed. If the remaining partition has any child partitions, new child partitions for the merged data will be created as required using the same partitioning expression.

append_range_partition(parent_relid   REGCLASS,
                       partition_name TEXT DEFAULT NULL,
                       tablespace     TEXT DEFAULT NULL)

Append new range partition with pathman_config.range_interval as interval.

prepend_range_partition(parent_relid   REGCLASS,
                        partition_name TEXT DEFAULT NULL,
                        tablespace     TEXT DEFAULT NULL)

Prepend new range partition with pathman_config.range_interval as interval.

add_range_partition(parent_relid   REGCLASS,
                    start_value    ANYELEMENT,
                    end_value      ANYELEMENT,
                    partition_name TEXT DEFAULT NULL,
                    tablespace     TEXT DEFAULT NULL)

Create a new range partition for relation with the specified range bounds. If the start_value or the end_value is NULL, the corresponding range bound will be infinite.

drop_range_partition(partition_relid TEXT, delete_data BOOLEAN DEFAULT TRUE)

Drop range partition and all of its data if delete_data is true.

attach_range_partition(parent_relid    REGCLASS,
                       partition_relid REGCLASS,
                       start_value     ANYELEMENT,
                       end_value       ANYELEMENT)

Attach partition to the existing range-partitioned relation. The attached table must have exactly the same structure as the parent table, including the dropped columns. Partition creation callback is invoked if set (see Section F.40.5.2.2).

detach_range_partition(partition_relid REGCLASS)

Detach partition from the existing range-partitioned relation.

disable_pathman_for(parent_relid REGCLASS)

Permanently disable pg_pathman partitioning mechanism for the specified parent table and remove the insert trigger if it exists. All partitions and data remain unchanged.

drop_partitions(parent_relid REGCLASS,
                delete_data  BOOLEAN DEFAULT FALSE)

Drop partitions of the parent table (both foreign and local relations). If delete_data is false, the data is copied to the parent table first. Default is false.

pathman_version()

Returns the pg_pathman version number.

set_interval(relation REGCLASS, value ANYELEMENT)

Update range-partitioned table interval. Note that interval must not be negative and it must not be trivial, i.e. its value should be greater than zero for numeric types, at least 1 microsecond for timestamp and at least 1 day for date.

set_enable_parent(relation REGCLASS, value BOOLEAN)

Include/exclude parent table into/from query plan. In original Postgres Pro planner parent table is always included into query plan even if it's empty, which can lead to additional overhead. You can use disable_parent() if you are never going to use parent table as a storage. Default value depends on the partition_data parameter specified during initial partitioning with the create_range_partitions() function. If the partition_data parameter was true, then all data have already been migrated to partitions and the parent table is disabled. Otherwise, it is enabled.

set_auto(relation REGCLASS, value BOOLEAN)

Enable/disable auto partition propagation (only for range partitioning). It is enabled by default.

set_init_callback(relation REGCLASS, callback REGPROCEDURE DEFAULT 0)

Set partition creation callback to be invoked for each attached or created partition (both hash and range). If callback is marked with SECURITY INVOKER, it is executed with the privileges of the user who produced a statement that has led to creation of a new partition. For example:

INSERT INTO partitioned_table VALUES (-5)

The callback must have the following signature: part_init_callback(args JSONB) RETURNS VOID. Parameter arg consists of several fields whose presence depends on partitioning type:

/* Range-partitioned table abc (child abc_4) */
{
    "parent":    "abc",
    "parttype":  "2",
    "partition": "abc_4",
    "range_max": "401",
    "range_min": "301"
}

/* Hash-partitioned table abc (child abc_0) */
{
    "parent":    "abc",
    "parttype":  "1",
    "partition": "abc_0"
}
      

set_spawn_using_bgw(relation REGCLASS, value BOOLEAN)
      

When inserting new data beyond the partitioning range, use SpawnPartitionsWorker to create new partitions in a separate transaction.

create_naming_sequence(parent_relid REGCLASS)
      

Enable automatic partition naming for the specified relation table. You must run this function when partitioning this table by composite key.

add_to_pathman_config(parent_relid     REGCLASS,
                      expression       TEXT,
                      range_interval   TEXT)
add_to_pathman_config(parent_relid     REGCLASS,
                      expression       TEXT)
      

Register the specified relation table with pg_pathman to enable partitioning by the provided expression. For range partitioning, the range_interval argument is mandatory. You can set it to NULL if you are going to add partition manually.

generate_range_bounds(p_start     ANYELEMENT,
                      p_interval  INTERVAL,
                      p_count     INTEGER)

generate_range_bounds(p_start     ANYELEMENT,
                      p_interval  ANYELEMENT,
                      p_count     INTEGER)

Build the bounds array that defines the bounds for partitions to be created. You can pass this array as an argument to the create_range_partitions() function.