Common use cases of the pg_pathman extension-PolarDB(PolarDB)-阿里云帮助中心

This topic describes common use cases of the pg_pathman extension.

Background information

PolarDB for PostgreSQL includes the pg_pathman extension to improve the performance of partitioned tables. This extension provides a partition management and optimization mechanism.

Create the pg_pathman extension

Note

To use the partition management features of the pg_pathman extension, contact us.

CREATE EXTENSION IF NOT EXISTS pg_pathman;

After you create the extension, you can run the following SQL statement to view its version:

SELECT extname,extversion FROM pg_extension WHERE extname = 'pg_pathman';

The following result is returned:

  extname   | extversion 
------------+------------
 pg_pathman | 1.5
(1 row)

Upgrade the extension

PolarDB for PostgreSQL regularly upgrades its plugins to provide improved database services. You must upgrade the cluster to the latest version before you can upgrade a plugin.

Extension features

Supports hash partitioning and range partitioning.
Supports automatic partition management, which uses functions to create partitions and migrate data from the primary table. It also supports manual partition management, allowing you to attach or detach existing tables using functions.
Supports common data types for partition key columns, such as int, float, and date, including custom domains.
Generates efficient query plans for partitioned tables, including for JOINs and subselects.
Uses custom plan nodes, RuntimeAppend and RuntimeMergeAppend, to implement dynamic partition selection.
PartitionFilter provides an efficient alternative to insert triggers.
Supports automatic creation of new partitions. This feature is currently available only for range-partitioned tables.
Supports direct read and write operations on partitioned tables using copy from/to for improved efficiency.
Supports updates to partition key columns through a trigger. To avoid a potential performance impact, do not add this trigger if you do not need to update partition key columns.
Lets you define custom callback functions that are automatically triggered when a partition is created.
Creates partitioned tables and migrates data from the primary table to partitions in the background without blocking operations.
Supports Foreign Data Wrappers (FDWs). You can configure the pg_pathman.insert_into_fdw=(disabled | postgres | any_fdw) parameter to support postgres_fdw or any other FDW.

For more information, see the project on GitHub.

Related views and tables

pg_pathman uses functions to maintain partitioned tables and provides several views for you to check the status of these tables. The views are as follows:

pathman_config

CREATE TABLE IF NOT EXISTS pathman_config (
    partrel         REGCLASS NOT NULL PRIMARY KEY,  -- OID of the primary table
    attname         TEXT NOT NULL,  -- Name of the partition key column
    parttype        INTEGER NOT NULL,  -- Partitioning type (hash or range)
    range_interval  TEXT,  -- Interval for range partitions

    CHECK (parttype IN (1, 2)) /* check for allowed part types */ );

pathman_config_params

CREATE TABLE IF NOT EXISTS pathman_config_params (
    partrel        REGCLASS NOT NULL PRIMARY KEY,  -- OID of the primary table
    enable_parent  BOOLEAN NOT NULL DEFAULT TRUE,  -- Specifies whether to filter the primary table in the optimizer
    auto           BOOLEAN NOT NULL DEFAULT TRUE,  -- Specifies whether to automatically create a new partition if it does not exist during an insert operation
    init_callback  REGPROCEDURE NOT NULL DEFAULT 0);  -- OID of the callback function for partition creation

pathman_concurrent_part_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();

pathman_partition_list

-- helper SRF function
CREATE OR REPLACE FUNCTION show_partition_list()
RETURNS TABLE (
    parent     REGCLASS,
    partition  REGCLASS,
    parttype   INT4,
    partattr   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();

Partition management

Range partitioning

Four management functions are available to create range partitions. Two of them let you specify a start value, an interval, and the number of partitions. Their definitions are as follows:

create_range_partitions(relation       REGCLASS,  -- OID of the primary table
                        attribute      TEXT,      -- Name of the partition key column
                        start_value    ANYELEMENT,  -- Start value
                        p_interval     ANYELEMENT,  -- Interval. Can be of any data type suitable for any type of partitioned table.
                        p_count        INTEGER DEFAULT NULL,   -- Number of partitions to create
                        partition_data BOOLEAN DEFAULT TRUE)   -- Specifies whether to immediately migrate data from the primary table to partitions. This is not recommended. Use the non-blocking migration function partition_table_concurrently() instead.

create_range_partitions(relation       REGCLASS,  -- OID of the primary table
                        attribute      TEXT,      -- Name of the partition key column
                        start_value    ANYELEMENT,  -- Start value
                        p_interval     INTERVAL,    -- Interval. The interval data type is used for time-based partitioned tables.
                        p_count        INTEGER DEFAULT NULL,   -- Number of partitions to create
                        partition_data BOOLEAN DEFAULT TRUE)   -- Specifies whether to immediately migrate data from the primary table to partitions. This is not recommended. Use the non-blocking migration function partition_table_concurrently() instead.

The other two functions let you specify a start value, an end value, and an interval. Their definitions are as follows:

create_partitions_from_range(relation       REGCLASS,  -- OID of the primary table
                             attribute      TEXT,      -- Name of the partition key column
                             start_value    ANYELEMENT,  -- Start value
                             end_value      ANYELEMENT,  -- End value
                             p_interval     ANYELEMENT,  -- Interval. Can be of any data type suitable for any type of partitioned table.
                             partition_data BOOLEAN DEFAULT TRUE)   -- Specifies whether to immediately migrate data from the primary table to partitions. This is not recommended. Use the non-blocking migration function partition_table_concurrently() instead.

create_partitions_from_range(relation       REGCLASS,  -- OID of the primary table
                             attribute      TEXT,      -- Name of the partition key column
                             start_value    ANYELEMENT,  -- Start value
                             end_value      ANYELEMENT,  -- End value
                             p_interval     INTERVAL,    -- Interval. The interval data type is used for time-based partitioned tables.
                             partition_data BOOLEAN DEFAULT TRUE)   -- Specifies whether to immediately migrate data from the primary table to partitions. This is not recommended. Use the non-blocking migration function partition_table_concurrently() instead.

For example:

Create a primary table to be partitioned and insert test data.

--- Create the primary table to be partitioned
CREATE TABLE part_test(id int, info text, crt_time timestamp not null);  -- The partition key column must have a NOT NULL constraint  

--- Insert test data to simulate a primary table that already contains data
INSERT INTO part_test SELECT id,md5(random()::text),clock_timestamp() + (id||' hour')::interval from generate_series(1,10000) t(id);

Query the data in the primary table:

SELECT * FROM part_test limit 10;

The following result is returned:

 id |               info               |          crt_time          
----+----------------------------------+----------------------------
  1 | 36fe1adedaa5b848caec4941f87d443a | 2016-10-25 10:27:13.206713
  2 | c7d7358e196a9180efb4d0a10269c889 | 2016-10-25 11:27:13.206893
  3 | 005bdb063550579333264b895df5b75e | 2016-10-25 12:27:13.206904
  4 | 6c900a0fc50c6e4da1ae95447c89dd55 | 2016-10-25 13:27:13.20691
  5 | 857214d8999348ed3cb0469b520dc8e5 | 2016-10-25 14:27:13.206916
  6 | 4495875013e96e625afbf2698124ef5b | 2016-10-25 15:27:13.206921
  7 | 82488cf7e44f87d9b879c70a9ed407d4 | 2016-10-25 16:27:13.20693
  8 | a0b92547c8f17f79814dfbb12b8694a0 | 2016-10-25 17:27:13.206936
  9 | 2ca09e0b85042b476fc235e75326b41b | 2016-10-25 18:27:13.206942
 10 | 7eb762e1ef7dca65faf413f236dff93d | 2016-10-25 19:27:13.206947
(10 rows)

Create partitions. Each partition contains data for a one-month span.

--- Create partitions. Each partition contains data for a one-month span.  
SELECT                                             
create_range_partitions('part_test'::regclass,             -- OID of the primary table
                        'crt_time',                        -- Name of the partition key column
                        '2016-10-25 00:00:00'::timestamp,  -- Start value
                        interval '1 month',                -- Interval. The interval data type is used for time-based partitioned tables.
                        24,                                -- Number of partitions to create
                        false) ;                           -- Do not migrate data

Use the non-blocking migration function to migrate data from the primary table.

--- Before data migration, the data is still in the primary table
SELECT count(*) FROM ONLY part_test;
 count 
-------
 10000
(1 row)


--- Non-blocking migration function  
partition_table_concurrently(relation   REGCLASS,              -- OID of the primary table
                             batch_size INTEGER DEFAULT 1000,  -- Number of records to migrate in a single transaction batch
                             sleep_time FLOAT8 DEFAULT 1.0)    -- The duration to sleep before retrying if acquiring row locks fails. The task exits after 60 retries.


--- Use the non-blocking migration function to migrate data from the primary table
SELECT partition_table_concurrently('part_test'::regclass,
                             10000,
                             1.0);


--- After migration, the primary table is empty. All data has been moved to the partitions.
SELECT count(*) FROM ONLY part_test;
 count 
-------
     0
(1 row)

After the data migration is complete, disable the primary table to prevent it from appearing in execution plans.

--- Disable the primary table
SELECT set_enable_parent('part_test'::regclass, false);

--- Verification
EXPLAIN SELECT * FROM part_test WHERE crt_time = '2016-10-25 00:00:00'::timestamp;
                                   QUERY PLAN                                    
---------------------------------------------------------------------------------
 Append  (cost=0.00..16.18 rows=1 width=45)
   ->  Seq Scan on part_test_1  (cost=0.00..16.18 rows=1 width=45)
         Filter: (crt_time = '2016-10-25 00:00:00'::timestamp without time zone)
(3 rows)

Note

When you use range-partitioned tables, follow these best practices:

The partition key column must have a NOT NULL constraint.
The number of partitions must be sufficient to cover all existing records.
Use the non-blocking migration function.
After data migration is complete, disable the primary table.

Hash partitioning

A management function lets you create range partitions by specifying the starting value, interval, and number of partitions as follows:

create_hash_partitions(relation         REGCLASS,  -- OID of the primary table
                       attribute        TEXT,      -- Name of the partition key column
                       partitions_count INTEGER,   -- Number of partitions to create
                       partition_data   BOOLEAN DEFAULT TRUE)   -- Specifies whether to immediately migrate data from the primary table to partitions. This is not recommended. Use the non-blocking migration function partition_table_concurrently() instead.

For example:

Create a primary table to be partitioned and insert test data.

--- Create the primary table to be partitioned
CREATE TABLE part_test(id int, info text, crt_time timestamp not null);    -- The partition key column must have a NOT NULL constraint  

--- Insert test data to simulate a primary table that already contains data
INSERT INTO part_test SELECT id,md5(random()::text),clock_timestamp() + (id||' hour')::interval FROM generate_series(1,10000) t(id);

Query the data in the primary table:

SELECT * FROM part_test limit 10;

The following result is returned:

 id |               info               |          crt_time          
----+----------------------------------+----------------------------
  1 | 29ce4edc70dbfbe78912beb7c4cc95c2 | 2016-10-25 10:47:32.873879
  2 | e0990a6fb5826409667c9eb150fef386 | 2016-10-25 11:47:32.874048
  3 | d25f577a01013925c203910e34470695 | 2016-10-25 12:47:32.874059
  4 | 501419c3f7c218e562b324a1bebfe0ad | 2016-10-25 13:47:32.874065
  5 | 5e5e22bdf110d66a5224a657955ba158 | 2016-10-25 14:47:32.87407
  6 | 55d2d4fd5229a6595e0dd56e13d32be4 | 2016-10-25 15:47:32.874076
  7 | 1dfb9a783af55b123c7a888afe1eb950 | 2016-10-25 16:47:32.874081
  8 | 41eeb0bf395a4ab1e08691125ae74bff | 2016-10-25 17:47:32.874087
  9 | 83783d69cc4f9bb41a3978fe9e13d7fa | 2016-10-25 18:47:32.874092
 10 | affc9406d5b3412ae31f7d7283cda0dd | 2016-10-25 19:47:32.874097
(10 rows)

Create partitions.

--- Create 128 partitions
SELECT                                              
create_hash_partitions('part_test'::regclass,              -- OID of the primary table
                        'crt_time',                        -- Name of the partition key column
                        128,                               -- Number of partitions to create
                        false) ;                           -- Do not migrate data

Use the non-blocking migration function to migrate data from the primary table.

--- Before data migration, the data is still in the primary table
SELECT count(*) FROM ONLY part_test;
 count 
-------
 10000
(1 row)


--- Non-blocking migration function  
partition_table_concurrently(relation   REGCLASS,              -- OID of the primary table
                             batch_size INTEGER DEFAULT 1000,  -- Number of records to migrate in a single transaction batch
                             sleep_time FLOAT8 DEFAULT 1.0)    -- The duration to sleep before retrying if acquiring row locks fails. The task exits after 60 retries.

--- Use the non-blocking migration function to migrate data from the primary table
SELECT partition_table_concurrently('part_test'::regclass,
                             10000,
                             1.0);

--- After migration, the primary table is empty. All data has been moved to the partitions.
SELECT count(*) FROM ONLY part_test;
 count 
-------
     0
(1 row)

After the data migration is complete, disable the primary table to prevent it from appearing in execution plans.

--- Disable the primary table
SELECT set_enable_parent('part_test'::regclass, false);

Verify the execution plan:

--- Query a single partition
EXPLAIN SELECT * FROM part_test WHERE crt_time = '2016-10-25 00:00:00'::timestamp;
                                   QUERY PLAN                                    
---------------------------------------------------------------------------------
 Append  (cost=0.00..1.91 rows=1 width=45)
   ->  Seq Scan on part_test_122  (cost=0.00..1.91 rows=1 width=45)
         Filter: (crt_time = '2016-10-25 00:00:00'::timestamp without time zone)
(3 rows)

The following constraint on the partitioned table shows that pg_pathman automatically performs the conversion. In contrast, traditional inheritance cannot perform partition pruning for a statement such as SELECT * FROM part_test WHERE crt_time = '2016-10-25 00:00:00'::timestamp;.

\d+ part_test_122
                                Table "public.part_test_122"
  Column  |            Type             | Modifiers | Storage  | Stats target | Description 
----------+-----------------------------+-----------+----------+--------------+-------------
 id       | integer                     |           | plain    |              | 
 info     | text                        |           | extended |              | 
 crt_time | timestamp without time zone | not null  | plain    |              | 
Check constraints:
    "pathman_part_test_122_3_check" CHECK (get_hash_part_idx(timestamp_hash(crt_time), 128) = 122)
Inherits: part_test

Note

When you use hash-partitioned tables, follow these best practices:

The partition key column must have a NOT NULL constraint.
Use the non-blocking migration function.
After data migration is complete, disable the primary table.
pg_pathman is not limited by the expression format. Therefore, a statement such as select * from part_test where crt_time = '2016-10-25 00:00:00'::timestamp; can also be used for hash partitioning.
The partition key column for hash partitioning is not limited to the int data type. A hash function is used for automatic conversion.

Migrate data to partitions

If you did not migrate data from the primary table when you created the partitioned table, you can use the non-blocking migration function to migrate the data.

WITH tmp AS (DELETE FROM primary_table limit xx nowait returning *) INSERT INTO partition SELECT * FROM tmp;

Alternatively, you can use the following statement to mark the rows and then perform DELETE and INSERT operations.

SELECT array_agg(ctid) FROM primary_table limit xx FOR UPDATE nowait;

The function is defined as follows:

partition_table_concurrently(relation   REGCLASS,              -- OID of the primary table
                             batch_size INTEGER DEFAULT 1000,  -- Number of records to migrate in a single transaction batch
                             sleep_time FLOAT8 DEFAULT 1.0)    -- The duration to sleep before retrying if acquiring row locks fails. The task exits after 60 retries.

For example:

SELECT partition_table_concurrently('part_test'::regclass,
                             10000,
                             1.0);

You can view the background data migration task.

SELECT * FROM pathman_concurrent_part_tasks;

Split a range partition

If a partition becomes too large, you can split it into two partitions. This feature is currently available only for range-partitioned tables. You can use the following function:

split_range_partition(partition      REGCLASS,            -- OID of the partition
                      split_value    ANYELEMENT,          -- Split value
                      partition_name TEXT DEFAULT NULL)   -- Name of the new partition table created after the split

For example:

Use the partitioned table from the Range partitioning example. The table schema is as follows.

\d+ part_test
                                  Table "public.part_test"
  Column  |            Type             | Modifiers | Storage  | Stats target | Description 
----------+-----------------------------+-----------+----------+--------------+-------------
 id       | integer                     |           | plain    |              | 
 info     | text                        |           | extended |              | 
 crt_time | timestamp without time zone | not null  | plain    |              | 
Child tables: part_test_1,
              part_test_10,
              part_test_11,
              part_test_12,
              part_test_13,
              part_test_14,
              part_test_15,
              part_test_16,
              part_test_17,
              part_test_18,
              part_test_19,
              part_test_2,
              part_test_20,
              part_test_21,
              part_test_22,
              part_test_23,
              part_test_24,
              part_test_3,
              part_test_4,
              part_test_5,
              part_test_6,
              part_test_7,
              part_test_8,
              part_test_9

\d+ part_test_1
                                 Table "public.part_test_1"
  Column  |            Type             | Modifiers | Storage  | Stats target | Description 
----------+-----------------------------+-----------+----------+--------------+-------------
 id       | integer                     |           | plain    |              | 
 info     | text                        |           | extended |              | 
 crt_time | timestamp without time zone | not null  | plain    |              | 
Check constraints:
    "pathman_part_test_1_3_check" CHECK (crt_time >= '2016-10-25 00:00:00'::timestamp without time zone AND crt_time < '2016-11-25 00:00:00'::timestamp without time zone)
Inherits: part_test

Split the partition.

SELECT split_range_partition('part_test_1'::regclass,              -- OID of the partition
                      '2016-11-10 00:00:00'::timestamp,     -- Split value
                      'part_test_1_2');                     -- Name of the partition table

The two tables after the split are as follows:

\d+ part_test_1
                                 Table "public.part_test_1"
  Column  |            Type             | Modifiers | Storage  | Stats target | Description 
----------+-----------------------------+-----------+----------+--------------+-------------
 id       | integer                     |           | plain    |              | 
 info     | text                        |           | extended |              | 
 crt_time | timestamp without time zone | not null  | plain    |              | 
Check constraints:
    "pathman_part_test_1_3_check" CHECK (crt_time >= '2016-10-25 00:00:00'::timestamp without time zone AND crt_time < '2016-11-10 00:00:00'::timestamp without time zone)
Inherits: part_test

\d+ part_test_1_2 
                                Table "public.part_test_1_2"
  Column  |            Type             | Modifiers | Storage  | Stats target | Description 
----------+-----------------------------+-----------+----------+--------------+-------------
 id       | integer                     |           | plain    |              | 
 info     | text                        |           | extended |              | 
 crt_time | timestamp without time zone | not null  | plain    |              | 
Check constraints:
    "pathman_part_test_1_2_3_check" CHECK (crt_time >= '2016-11-10 00:00:00'::timestamp without time zone AND crt_time < '2016-11-25 00:00:00'::timestamp without time zone)
Inherits: part_test

The data is automatically migrated to the new partitions.

SELECT count(*) FROM part_test_1;
 count 
-------
   373
(1 row)

SELECT count(*) FROM part_test_1_2;
 count 
-------
   360
(1 row)

The inheritance relationship is as follows:

\d+ part_test
                                  Table "public.part_test"
  Column  |            Type             | Modifiers | Storage  | Stats target | Description 
----------+-----------------------------+-----------+----------+--------------+-------------
 id       | integer                     |           | plain    |              | 
 info     | text                        |           | extended |              | 
 crt_time | timestamp without time zone | not null  | plain    |              | 
Child tables: part_test_1,
              part_test_10,
              part_test_11,
              part_test_12,
              part_test_13,
              part_test_14,
              part_test_15,
              part_test_16,
              part_test_17,
              part_test_18,
              part_test_19,
              part_test_1_2,    -- New table
              part_test_2,
              part_test_20,
              part_test_21,
              part_test_22,
              part_test_23,
              part_test_24,
              part_test_3,
              part_test_4,
              part_test_5,
              part_test_6,
              part_test_7,
              part_test_8,
              part_test_9

Merge range partitions

This feature is currently available only for range partitioning, and the partitions must be adjacent. You can call the following function:

--- Specify the two partitions to merge  
merge_range_partitions(partition1 REGCLASS, partition2 REGCLASS)

Here is an example:

Use the tables from the Split a range partition example to perform a merge operation.

SELECT merge_range_partitions('part_test_1'::regclass, 'part_test_1_2'::regclass);

Note

If you try to merge non-adjacent partitions, an error is reported.

SELECT merge_range_partitions('part_test_2'::regclass, 'part_test_12'::regclass) ;
ERROR:  merge failed, partitions must be adjacent
CONTEXT:  PL/pgSQL function merge_range_partitions_internal(regclass,regclass,regclass,anyelement) line 27 at RAISE
SQL statement "SELECT public.merge_range_partitions_internal($1, $2, $3, NULL::timestamp without time zone)"
PL/pgSQL function merge_range_partitions(regclass,regclass) line 44 at EXECUTE

After the merge, one of the original partitions is deleted.

\d part_test_1_2
Did not find any relation named "part_test_1_2".

\d part_test_1
             Table "public.part_test_1"
  Column  |            Type             | Modifiers 
----------+-----------------------------+-----------
 id       | integer                     | 
 info     | text                        | 
 crt_time | timestamp without time zone | not null
Check constraints:
    "pathman_part_test_1_3_check" CHECK (crt_time >= '2016-10-25 00:00:00'::timestamp without time zone AND crt_time < '2016-11-25 00:00:00'::timestamp without time zone)
Inherits: part_test

SELECT count(*) FROM part_test_1;
 count 
-------
   733
(1 row)

Add a range partition

If a primary table is already partitioned, you can add new partitions in several ways. This section describes three methods: appending a range partition, prepending a range partition, and adding a range partition with a specified start value.

Append a range partition

When you append a range partition (add a partition to the end), the interval specified during the initial creation of the partitioned table is used. You can query the `pathman_config` view to find the initial interval for each partitioned table, as shown below:

SELECT * FROM pathman_config;
  partrel  | attname  | parttype | range_interval 
-----------+----------+----------+----------------
 part_test | crt_time |        2 | 1 mon
(1 row)

The function to add a partition is as follows. Specifying a tablespace is not currently supported.

append_range_partition(parent         REGCLASS,            -- OID of the primary table
                       partition_name TEXT DEFAULT NULL,   -- Name of the new partition table. This is optional.
                       tablespace     TEXT DEFAULT NULL)   -- Tablespace for the new partition table. This is optional.

Here is an example:

SELECT append_range_partition('part_test'::regclass);

\d+ part_test_25
                                Table "public.part_test_25"
  Column  |            Type             | Modifiers | Storage  | Stats target | Description 
----------+-----------------------------+-----------+----------+--------------+-------------
 id       | integer                     |           | plain    |              | 
 info     | text                        |           | extended |              | 
 crt_time | timestamp without time zone | not null  | plain    |              | 
Check constraints:
    "pathman_part_test_25_3_check" CHECK (crt_time >= '2018-10-25 00:00:00'::timestamp without time zone AND crt_time < '2018-11-25 00:00:00'::timestamp without time zone)
Inherits: part_test

\d+ part_test_24
                                Table "public.part_test_24"
  Column  |            Type             | Modifiers | Storage  | Stats target | Description 
----------+-----------------------------+-----------+----------+--------------+-------------
 id       | integer                     |           | plain    |              | 
 info     | text                        |           | extended |              | 
 crt_time | timestamp without time zone | not null  | plain    |              | 
Check constraints:
    "pathman_part_test_24_3_check" CHECK (crt_time >= '2018-09-25 00:00:00'::timestamp without time zone AND crt_time < '2018-10-25 00:00:00'::timestamp without time zone)
Inherits: part_test

Prepend a range partition

To prepend a range partition, use the following function:

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

For example:

SELECT prepend_range_partition('part_test'::regclass);

\d+ part_test_26
                                Table "public.part_test_26"
  Column  |            Type             | Modifiers | Storage  | Stats target | Description 
----------+-----------------------------+-----------+----------+--------------+-------------
 id       | integer                     |           | plain    |              | 
 info     | text                        |           | extended |              | 
 crt_time | timestamp without time zone | not null  | plain    |              | 
Check constraints:
    "pathman_part_test_26_3_check" CHECK (crt_time >= '2016-09-25 00:00:00'::timestamp without time zone AND crt_time < '2016-10-25 00:00:00'::timestamp without time zone)
Inherits: part_test

\d+ part_test_1
                                 Table "public.part_test_1"
  Column  |            Type             | Modifiers | Storage  | Stats target | Description 
----------+-----------------------------+-----------+----------+--------------+-------------
 id       | integer                     |           | plain    |              | 
 info     | text                        |           | extended |              | 
 crt_time | timestamp without time zone | not null  | plain    |              | 
Check constraints:
    "pathman_part_test_1_3_check" CHECK (crt_time >= '2016-10-25 00:00:00'::timestamp without time zone AND crt_time < '2016-11-25 00:00:00'::timestamp without time zone)
Inherits: part_test

Add a partition with a specified start value

You can add a range partition by specifying its start and end values. The partition can be created provided that it does not overlap with existing partitions. This method does not require you to create contiguous partitions. For example, if existing partitions cover the range from 2010 to 2015, you can directly create a partition for the year 2020 without covering the range from 2015 to 2020. The function is as follows:

add_range_partition(relation       REGCLASS,    -- OID of the primary table
                    start_value    ANYELEMENT,  -- Start value
                    end_value      ANYELEMENT,  -- End value
                    partition_name TEXT DEFAULT NULL,  -- Name of the partition
                    tablespace     TEXT DEFAULT NULL)  -- Tablespace where the partition is created

For example:

SELECT add_range_partition('part_test'::regclass,    -- OID of the primary table
                    '2020-01-01 00:00:00'::timestamp,  -- Start value
                    '2020-02-01 00:00:00'::timestamp); -- End value

\d+ part_test_27
                                Table "public.part_test_27"
  Column  |            Type             | Modifiers | Storage  | Stats target | Description 
----------+-----------------------------+-----------+----------+--------------+-------------
 id       | integer                     |           | plain    |              | 
 info     | text                        |           | extended |              | 
 crt_time | timestamp without time zone | not null  | plain    |              | 
Check constraints:
    "pathman_part_test_27_3_check" CHECK (crt_time >= '2020-01-01 00:00:00'::timestamp without time zone AND crt_time < '2020-02-01 00:00:00'::timestamp without time zone)
Inherits: part_test

Delete a partition

To delete a single range partition, use the following function:

drop_range_partition(partition TEXT,   -- Name of the partition
                    delete_data BOOLEAN DEFAULT TRUE)  -- Specifies whether to delete the partition data. If false, the data is migrated to the primary table.  

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

To delete all partitions and specify whether to migrate the data to the primary table, use the following function:

drop_partitions(parent      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.

For example:

Delete a partition and migrate its data to the primary table.

SELECT drop_range_partition('part_test_1',false);
SELECT drop_range_partition('part_test_2',false);

Query the current data volume of the primary table:

SELECT count(*) FROM part_test;
 count 
-------
 10000
(1 row)

Delete a partition and its data. The data is not migrated to the primary table.

SELECT drop_range_partition('part_test_3',true);

Query the current data volume of the primary table:

SELECT count(*) FROM part_test;
 count 
-------
  9256
(1 row)

SELECT count(*) FROM ONLY part_test;
 count 
-------
  1453
(1 row)

Delete all partitions.

SELECT drop_partitions('part_test'::regclass, false);  -- Delete all partition tables and migrate the data to the primary table

Query the data in the primary table:

SELECT count(*) FROM part_test;
 count 
-------
  9256
(1 row)

Attach a partition

You can attach an existing table to a partitioned primary table. The existing table must have the same schema as the primary table, including dropped columns. You can check the `pg_attribute` view for consistency. The function is as follows:

attach_range_partition(relation    REGCLASS,    -- OID of the primary table
                       partition   REGCLASS,    -- OID of the partition table
                       start_value ANYELEMENT,  -- Start value
                       end_value   ANYELEMENT)  -- End value

Here is an example:

Create a table to be used as a partition.

CREATE TABLE part_test_1 (like part_test including all);

Attach the existing table to the primary table.

\d+ part_test
                                  Table "public.part_test"
  Column  |            Type             | Modifiers | Storage  | Stats target | Description 
----------+-----------------------------+-----------+----------+--------------+-------------
 id       | integer                     |           | plain    |              | 
 info     | text                        |           | extended |              | 
 crt_time | timestamp without time zone | not null  | plain    |              | 

\d+ part_test_1
                                 Table "public.part_test_1"
  Column  |            Type             | Modifiers | Storage  | Stats target | Description 
----------+-----------------------------+-----------+----------+--------------+-------------
 id       | integer                     |           | plain    |              | 
 info     | text                        |           | extended |              | 
 crt_time | timestamp without time zone | not null  | plain    |              | 

SELECT attach_range_partition('part_test'::regclass, 'part_test_1'::regclass, '2019-01-01 00:00:00'::timestamp, '2019-02-01 00:00:00'::timestamp);

When you attach a partition, the inheritance relationship and constraints are created automatically.

\d+ part_test_1
                                 Table "public.part_test_1"
  Column  |            Type             | Modifiers | Storage  | Stats target | Description 
----------+-----------------------------+-----------+----------+--------------+-------------
 id       | integer                     |           | plain    |              | 
 info     | text                        |           | extended |              | 
 crt_time | timestamp without time zone | not null  | plain    |              | 
Check constraints:
    "pathman_part_test_1_3_check" CHECK (crt_time >= '2019-01-01 00:00:00'::timestamp without time zone AND crt_time < '2019-02-01 00:00:00'::timestamp without time zone)
Inherits: part_test

Detach a partition

You can remove a partition from the primary table's inheritance hierarchy. This operation does not delete the data but removes the inheritance relationship and constraints. The function is as follows:

detach_range_partition(partition REGCLASS)  -- Specify the partition name to convert it into a standard table

Here is an example:

Query the current data volume of the primary table and the partition.

SELECT count(*) FROM part_test;
 count 
-------
  9256
(1 row)

SELECT count(*) FROM part_test_2;
 count 
-------
   733
(1 row)

Detach the partition.

SELECT detach_range_partition('part_test_2');

Query the current data volume of the primary table and the partition.

SELECT count(*) FROM part_test_2;
 count 
-------
   733
(1 row)

SELECT count(*) FROM part_test;
 count 
-------
  8523
(1 row)

Disable pg_pathman

You can disable pg_pathman for a single partitioned primary table. The function is as follows:

Important

The disable_pathman_for operation cannot be reversed. Use it with caution.

\sf disable_pathman_for
CREATE OR REPLACE FUNCTION public.disable_pathman_for(parent_relid regclass)
 RETURNS void
 LANGUAGE plpgsql
 STRICT
AS $function$
BEGIN
        PERFORM public.validate_relname(parent_relid);

        DELETE FROM public.pathman_config WHERE partrel = parent_relid;
        PERFORM public.drop_triggers(parent_relid);

        /* Notify backend about changes */
        PERFORM public.on_remove_partitions(parent_relid);
END
$function$

The following is an example:

SELECT disable_pathman_for('part_test');

\d+ part_test
                                  Table "public.part_test"
  Column  |            Type             | Modifiers | Storage  | Stats target | Description 
----------+-----------------------------+-----------+----------+--------------+-------------
 id       | integer                     |           | plain    |              | 
 info     | text                        |           | extended |              | 
 crt_time | timestamp without time zone | not null  | plain    |              | 
Child tables: part_test_10,
              part_test_11,
              part_test_12,
              part_test_13,
              part_test_14,
              part_test_15,
              part_test_16,
              part_test_17,
              part_test_18,
              part_test_19,
              part_test_20,
              part_test_21,
              part_test_22,
              part_test_23,
              part_test_24,
              part_test_25,
              part_test_26,
              part_test_27,
              part_test_28,
              part_test_29,
              part_test_3,
              part_test_30,
              part_test_31,
              part_test_32,
              part_test_33,
              part_test_34,
              part_test_35,
              part_test_4,
              part_test_5,
              part_test_6,
              part_test_7,
              part_test_8,
              part_test_9

\d+ part_test_10
                                Table "public.part_test_10"
  Column  |            Type             | Modifiers | Storage  | Stats target | Description 
----------+-----------------------------+-----------+----------+--------------+-------------
 id       | integer                     |           | plain    |              | 
 info     | text                        |           | extended |              | 
 crt_time | timestamp without time zone | not null  | plain    |              | 
Check constraints:
    "pathman_part_test_10_3_check" CHECK (crt_time >= '2017-06-25 00:00:00'::timestamp without time zone AND crt_time < '2017-07-25 00:00:00'::timestamp without time zone)
Inherits: part_test

After you disable the pg_pathman extension, the inheritance relationships and constraints remain unchanged. The only difference is that the pg_pathman extension no longer provides a custom scan in the execution plan. The execution plan after disabling the extension is as follows:

EXPLAIN SELECT * FROM part_test WHERE crt_time='2017-06-25 00:00:00'::timestamp;
                                   QUERY PLAN                                    
---------------------------------------------------------------------------------
 Append  (cost=0.00..16.00 rows=2 width=45)
   ->  Seq Scan on part_test  (cost=0.00..0.00 rows=1 width=45)
         Filter: (crt_time = '2017-06-25 00:00:00'::timestamp without time zone)
   ->  Seq Scan on part_test_10  (cost=0.00..16.00 rows=1 width=45)
         Filter: (crt_time = '2017-06-25 00:00:00'::timestamp without time zone)
(5 rows)

Advanced partition management

Disable the primary table

After all data from the primary table has been migrated to partitions, you can disable the primary table. The function is as follows:

set_enable_parent(relation REGCLASS, value BOOLEAN)


Include/exclude parent table into/from query plan. 

In original PostgreSQL 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 that was specified during initial partitioning in create_range_partitions() or create_partitions_from_range() functions. 

If the partition_data parameter was true then all data have already been migrated to partitions and parent table disabled. 

Otherwise it is enabled.

For example:

SELECT set_enable_parent('part_test', false);

Automatically extend partitions

For range-partitioned tables, you can enable automatic partition creation. If newly inserted data does not fall within the range of existing partitions, a new partition is created automatically.

set_auto(relation REGCLASS, value BOOLEAN)

Enable/disable auto partition propagation (only for RANGE partitioning). 

It is enabled by default.

For example:

Query the current partitions of the test table.

\d+ part_test
                                  Table "public.part_test"
  Column  |            Type             | Modifiers | Storage  | Stats target | Description 
----------+-----------------------------+-----------+----------+--------------+-------------
 id       | integer                     |           | plain    |              | 
 info     | text                        |           | extended |              | 
 crt_time | timestamp without time zone | not null  | plain    |              | 
Child tables: part_test_10,
              part_test_11,
              part_test_12,
              part_test_13,
              part_test_14,
              part_test_15,
              part_test_16,
              part_test_17,
              part_test_18,
              part_test_19,
              part_test_20,
              part_test_21,
              part_test_22,
              part_test_23,
              part_test_24,
              part_test_25,
              part_test_26,
              part_test_3,
              part_test_4,
              part_test_5,
              part_test_6,
              part_test_7,
              part_test_8,
              part_test_9

\d+ part_test_26
                                Table "public.part_test_26"
  Column  |            Type             | Modifiers | Storage  | Stats target | Description 
----------+-----------------------------+-----------+----------+--------------+-------------
 id       | integer                     |           | plain    |              | 
 info     | text                        |           | extended |              | 
 crt_time | timestamp without time zone | not null  | plain    |              | 
Check constraints:
    "pathman_part_test_26_3_check" CHECK (crt_time >= '2018-09-25 00:00:00'::timestamp without time zone AND crt_time < '2018-10-25 00:00:00'::timestamp without time zone)
Inherits: part_test

\d+ part_test_25
                                Table "public.part_test_25"
  Column  |            Type             | Modifiers | Storage  | Stats target | Description 
----------+-----------------------------+-----------+----------+--------------+-------------
 id       | integer                     |           | plain    |              | 
 info     | text                        |           | extended |              | 
 crt_time | timestamp without time zone | not null  | plain    |              | 
Check constraints:
    "pathman_part_test_25_3_check" CHECK (crt_time >= '2018-08-25 00:00:00'::timestamp without time zone AND crt_time < '2018-09-25 00:00:00'::timestamp without time zone)
Inherits: part_test

Insert a value that is outside the range of existing partitions. This automatically creates the necessary number of partitions based on the interval specified during the initial setup. Note that this operation may take a long time.

INSERT INTO part_test VALUES (1,'test','2222-01-01'::timestamp);

You can query the current partition status:

\d+ part_test
                                  Table "public.part_test"
  Column  |            Type             | Modifiers | Storage  | Stats target | Description 
----------+-----------------------------+-----------+----------+--------------+-------------
 id       | integer                     |           | plain    |              | 
 info     | text                        |           | extended |              | 
 crt_time | timestamp without time zone | not null  | plain    |              | 
Child tables: part_test_10,
              part_test_100,
              part_test_1000,
              part_test_1001,
              ......

Note

We recommend that you do not enable automatic partition creation for range-partitioned tables. Creating many partitions automatically can be very time-consuming.

Callback functions

A callback function is a function that is automatically triggered each time a partition is created. For example, you can use a callback function for DDL logical replication to record DDL statements in a table. The function to set a callback is as follows:

set_init_callback(relation REGCLASS, callback REGPROC DEFAULT 0)

Set partition creation callback to be invoked for each attached or created partition (both HASH and RANGE). 

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"
}

For example:

Create a callback function.

CREATE OR REPLACE FUNCTION f_callback_test(jsonb) RETURNS void AS
$$
DECLARE
BEGIN
  CREATE TABLE if NOT EXISTS rec_part_ddl(id serial primary key, parent name, parttype int, partition name, range_max text, range_min text);
  if ($1->>'parttype')::int = 1 then
    raise notice 'parent: %, parttype: %, partition: %', $1->>'parent', $1->>'parttype', $1->>'partition';
    INSERT INTO rec_part_ddl(parent, parttype, partition) values (($1->>'parent')::name, ($1->>'parttype')::int, ($1->>'partition')::name);
  elsif ($1->>'parttype')::int = 2 then
    raise notice 'parent: %, parttype: %, partition: %, range_max: %, range_min: %', $1->>'parent', $1->>'parttype', $1->>'partition', $1->>'range_max', $1->>'range_min';
    INSERT INTO rec_part_ddl(parent, parttype, partition, range_max, range_min) values (($1->>'parent')::name, ($1->>'parttype')::int, ($1->>'partition')::name, $1->>'range_max', $1->>'range_min');
  END if;
END;
$$ LANGUAGE plpgsql strict;

Prepare a test table.

CREATE TABLE tt(id int, info text, crt_time timestamp not null);

--- Set the callback function for the test table
SELECT set_init_callback('tt'::regclass, 'f_callback_test'::regproc);

--- Create partitions
SELECT                                                           
create_range_partitions('tt'::regclass,                    -- OID of the primary table
                        'crt_time',                        -- Name of the partition key column
                        '2016-10-25 00:00:00'::timestamp,  -- Start value
                        interval '1 month',                -- Interval. The interval data type is used for time-based partitioned tables.
                        24,                                -- Number of partitions to create
                        false) ;

Check whether the callback function was invoked.

SELECT * FROM rec_part_ddl;

The following result is returned:

 id | parent | parttype | partition |      range_max      |      range_min      
----+--------+----------+-----------+---------------------+---------------------
  1 | tt     |        2 | tt_1      | 2016-11-25 00:00:00 | 2016-10-25 00:00:00
  2 | tt     |        2 | tt_2      | 2016-12-25 00:00:00 | 2016-11-25 00:00:00
  3 | tt     |        2 | tt_3      | 2017-01-25 00:00:00 | 2016-12-25 00:00:00
  4 | tt     |        2 | tt_4      | 2017-02-25 00:00:00 | 2017-01-25 00:00:00
  5 | tt     |        2 | tt_5      | 2017-03-25 00:00:00 | 2017-02-25 00:00:00
  6 | tt     |        2 | tt_6      | 2017-04-25 00:00:00 | 2017-03-25 00:00:00
  7 | tt     |        2 | tt_7      | 2017-05-25 00:00:00 | 2017-04-25 00:00:00
  8 | tt     |        2 | tt_8      | 2017-06-25 00:00:00 | 2017-05-25 00:00:00
  9 | tt     |        2 | tt_9      | 2017-07-25 00:00:00 | 2017-06-25 00:00:00
 10 | tt     |        2 | tt_10     | 2017-08-25 00:00:00 | 2017-07-25 00:00:00
 11 | tt     |        2 | tt_11     | 2017-09-25 00:00:00 | 2017-08-25 00:00:00
 12 | tt     |        2 | tt_12     | 2017-10-25 00:00:00 | 2017-09-25 00:00:00
 13 | tt     |        2 | tt_13     | 2017-11-25 00:00:00 | 2017-10-25 00:00:00
 14 | tt     |        2 | tt_14     | 2017-12-25 00:00:00 | 2017-11-25 00:00:00
 15 | tt     |        2 | tt_15     | 2018-01-25 00:00:00 | 2017-12-25 00:00:00
 16 | tt     |        2 | tt_16     | 2018-02-25 00:00:00 | 2018-01-25 00:00:00
 17 | tt     |        2 | tt_17     | 2018-03-25 00:00:00 | 2018-02-25 00:00:00
 18 | tt     |        2 | tt_18     | 2018-04-25 00:00:00 | 2018-03-25 00:00:00
 19 | tt     |        2 | tt_19     | 2018-05-25 00:00:00 | 2018-04-25 00:00:00
 20 | tt     |        2 | tt_20     | 2018-06-25 00:00:00 | 2018-05-25 00:00:00
 21 | tt     |        2 | tt_21     | 2018-07-25 00:00:00 | 2018-06-25 00:00:00
 22 | tt     |        2 | tt_22     | 2018-08-25 00:00:00 | 2018-07-25 00:00:00
 23 | tt     |        2 | tt_23     | 2018-09-25 00:00:00 | 2018-08-25 00:00:00
 24 | tt     |        2 | tt_24     | 2018-10-25 00:00:00 | 2018-09-25 00:00:00
(24 rows)