Usage of the pg_pathman extension - PolarDB - Alibaba Cloud Documentation Center

This topic describes how to use the pg_pathman extension.

Background information

pg_pathman is an extension that you can use in a PolarDB for PostgreSQL cluster. The pg_pathman extension provides efficient partitioning capabilities and helps effectively manage partitions and improve the performance of partitioned tables.

Create the extension

Note

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

CREATE EXTENSION IF NOT EXISTS pg_pathman;

After the pg_pathman extension is created, you can execute the following SQL statement to view the version:

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

Sample result:

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

Upgrade the extension

PolarDB for PostgreSQL upgrades the pg_pathman extension on a regular basis to improve database services. To manually upgrade the pg_pathman extension, you can upgrade the cluster to the latest version.

Features

Supports hash partitioning and range partitioning.
Provides automatic and manual partition management. In automatic partition management, use functions to automatically create partitions and migrate data from a primary table to partitions. In manual partition management, use functions to attach existing tables to a primary table or detach tables from a primary table.
Supports a wide range of column types for partitioning, including the INT, FLOAT, and DATE column types and custom domains.
Provides optimized plans for queries on a partitioned table, and uses strategies such as joins and subqueries in the plans.
Enables dynamic partition selection by using custom plan nodes: RuntimeAppend and RuntimeMergeAppend.
Uses the PartitionFilter feature to dynamically filter partitions based on query conditions.
Automatically adds new partitions when the inserted data exceeds the current partition boundaries. This feature is available only in range partitioning.
Directly reads data from and writes data into partitioned tables by using the COPY FROM/TO statement.
Allows you to update partition keys by adding a trigger. If you do not need to update partition keys, we recommend that you do not add the trigger because the trigger may have negative impacts on performance.
Allows you to configure custom callback functions that are automatically invoked when partitions are created.
Allows you to create partitioned tables and migrate data from a primary table to partitions in a non-blocking manner.
Allows you to insert data into foreign tables managed by Foreign Data Wrappers (FDWs) of PostgreSQL or other FDWs. You can use the pg_pathman.insert_into_fdw=(disabled | postgres | any_fdw) parameter to configure this feature.

For more information, visit GitHub.

Views and tables

The pg_pathman extension uses functions to manage partitioned tables and creates views for you to view the status of partitioned tables.

pathman_config

CREATE TABLE IF NOT EXISTS pathman_config (
    partrel         REGCLASS NOT NULL PRIMARY KEY,  -- The object identifier (OID) of the primary table.
    attname         TEXT NOT NULL,  -- The name of the partition key column.
    parttype        INTEGER NOT NULL,  -- The partitioning type (hash or range).
    range_interval  TEXT,  -- The range or span of values that each partition covers.

    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,  -- The 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 add new partitions.
    init_callback REGPROCEDURE NOT NULL DEFAULT 0); -- The OID of the initialization callback function that is invoked each time partitions are created.

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

Perform range partitioning

Four management functions are used to create range partitions. Two functions are used to specify the start value, interval, and number of partitions. You can define these two functions by using the following syntax:

create_range_partitions(relation       REGCLASS,  -- The OID of the primary table.
                        attribute      TEXT,      -- The name of the partition key column.
                        start_value    ANYELEMENT,  -- The start value.
                        p_interval     ANYELEMENT,  -- The interval of all data types, which is applicable to all types of partitioned tables.
                        p_count        INTEGER DEFAULT NULL,   --  The number of partitions.
                        partition_data BOOLEAN DEFAULT TRUE)   --  Specifies whether to immediately migrate data from the primary table to partitions. We recommend that you call the partition_table_concurrently() function to run non-blocking data migration.

create_range_partitions(relation       REGCLASS,  -- The OID of the primary table.
                        attribute      TEXT,      -- The name of the partition key column.
                        start_value    ANYELEMENT,  -- The start value.
                        p_interval     INTERVAL,    -- The interval of an interval data type, which is applicable to ingestion-time partitioned tables.
                        p_count        INTEGER DEFAULT NULL,   --  The number of partitions.
                        partition_data BOOLEAN DEFAULT TRUE)   --  Specifies whether to immediately migrate data from the primary table to partitions. We recommend that you invoke the partition_table_concurrently() function to perform non-blocking data migration.

The other two functions are used to specify the start value, end value, and interval. You can define the functions by using the following syntax:

create_partitions_from_range(relation       REGCLASS,  -- The OID of the primary table.
                             attribute      TEXT,      -- The name of the partition key column.
                             start_value    ANYELEMENT,  -- The start value.
                             end_value      ANYELEMENT,  -- The end value.
                             p_interval     ANYELEMENT,  -- The interval of all data types, which is applicable to all types of partitioned tables.
                             partition_data BOOLEAN DEFAULT TRUE)   --  Specifies whether to immediately migrate data from the primary table to partitions. We recommend that you call the partition_table_concurrently() function to run non-blocking data migration.

create_partitions_from_range(relation       REGCLASS,  -- The OID of the primary table.
                             attribute      TEXT,      -- The name of the partition key column.
                             start_value    ANYELEMENT,  -- The start value.
                             end_value      ANYELEMENT,  -- The end value.
                             p_interval     INTERVAL,    -- The interval of an interval data type, which is applicable to ingestion-time partitioned tables.
                             partition_data BOOLEAN DEFAULT TRUE)   --  Specifies whether to immediately migrate data from the primary table to partitions. We recommend that you invoke the partition_table_concurrently() function to perform non-blocking data migration.

Examples:

Create a primary table that you want to partition and insert test data.

--- Create a primary table that you want to partition.
CREATE TABLE part_test(id int, info text, crt_time timestamp not null);  -- -- The partition key column must contain the NOT NULL constraint.  

--- Insert test data into the primary table to simulate a primary table that already has 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 of the primary table.

SELECT * FROM part_test limit 10;

Sample result:

 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 and ensure that each partition contains one month of data.

--- Create partitions and ensure that each partition contains one month of data.  
SELECT                                             
create_range_partitions('part_test'::regclass,             -- The OID of the primary table.
                        'crt_time',                        -- The name of the partition key column.
                        '2016-10-25 00:00:00'::timestamp,  -- The start value of the range for the first partition.
                        interval '1 month',                -- The range or span of values that each partition covers.
                        24,                                -- The number of partitions.
                        false) ;                            -- The data is not migrated.

-- Perform non-blocking data migration.

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


--- Non-blocking migration API  
partition_table_concurrently(relation   REGCLASS,              -- The OID of the primary table.
                             batch_size INTEGER DEFAULT 1000, -- The number of records in a transaction that you want to migrate from the primary table.
                             sleep_time FLOAT8 DEFAULT 1.0)    -- The retry interval following a failed attempt to acquire a row lock. After 60 failed attempts, the task is ended. 


-- Perform non-blocking data migration.
SELECT partition_table_concurrently('part_test'::regclass,
                             10000,
                             1.0);


--- After the migration is completed, all data is migrated to the partitions, and the primary table is empty.
SELECT count(*) FROM ONLY part_test;
 count 
-------
     0
(1 row)

After you migrate the data, we recommend that you disable the primary table. This way, the parent table is not included in the execution plan.

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

--- Verify the execution plan.
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 partitioning, take note of the following items:

The partition key column must contain the NOT NULL constraint.
The number of partitions must be sufficient to cover all existing records.
Perform non-blocking data migration.
After data migration is completed, disable the primary table.

Perform hash partitioning

You can use the following management function to create range partitions and specify the start value, interval, and number of partitions:

create_hash_partitions(relation         REGCLASS,  -- The OID of the primary table.
                       attribute        TEXT,      -- The name of the partition key column.
                       partitions_count INTEGER,   -- The number of partitions.
                       partition_data   BOOLEAN DEFAULT TRUE)   --  Specifies whether to immediately migrate data from the primary table to the partitions. We recommend that you invoke the partition_table_concurrently() function to perform non-blocking data migration.

Examples:

Create a primary table that you want to partition and insert test data.

--- Create a primary table that you want to partition.
CREATE TABLE part_test(id int, info text, crt_time timestamp not null);    -- The partition key column must contain the NOT NULL constraint.  

--- Insert test data into the primary table to simulate a primary table that already has 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 of the primary table.

SELECT * FROM part_test limit 10;

Sample result:

 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,              The OID of the primary table.
                        'crt_time',                        -- The name of the partition key column.
                        128,                               -- The number of partitions that you want to create.
                        false) ;                            -- The data is not migrated.

-- Perform non-blocking data migration.

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


--- Non-blocking migration API  
partition_table_concurrently(relation   REGCLASS,              -- The OID of the primary table.
                             batch_size INTEGER DEFAULT 1000, -- The number of records in a transaction that you want to migrate from the primary table.
                             sleep_time FLOAT8 DEFAULT 1.0)    -- The retry interval following a failed attempt to acquire a row lock. After 60 failed attempts, the task is ended. 

-- Perform non-blocking data migration.
SELECT partition_table_concurrently('part_test'::regclass,
                             10000,
                             1.0);

--- After the migration is completed, all data is migrated to the partitions, and the primary table is empty.
SELECT count(*) FROM ONLY part_test;
 count 
-------
     0
(1 row)

After you migrate the data, we recommend that you disable the primary table. This way, the parent table is not included in the execution plan.

--- 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 partitioned table has the following restrictions. The pg_pathman extension automatically converts the statement. In traditional inheritance-based partitioning, you cannot filter partitions by using a statement like 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 partitioning, take note of the following items:

The partition key column must contain the NOT NULL constraint.
Perform non-blocking data migration.
After data migration is completed, disable the primary table.
The pg_pathman extension can work regardless of how the statement is written. For example, a statement such as select * from part_test where crt_time = '2016-10-25 00:00:00'::timestamp; can be recognized and executed even in hash partitioning.
HASH partition columns are not limited to int type columns. The column types are automatically converted by a hash function.

Migrate data to partitions

If data remains in the parent table, perform non-blocking data migration to migrate data to partitions. Function:

WITH tmp AS (DELETE FROM <Primary table> limit xx nowait returning *) INSERT INTO <Partitions> SELECT * FROM tmp;

You can also use the following statement to label rows. Then, perform DELETE and INSERT operations.

SELECT array_agg(ctid) FROM <Primary table> limit xx FOR UPDATE nowati;

Function:

partition_table_concurrently(relation   REGCLASS,              -- The OID of the primary table.
                             batch_size INTEGER DEFAULT 1000, -- The number of records in a transaction that you want to migrate from the primary table.
                             sleep_time FLOAT8 DEFAULT 1.0)    -- The retry interval following a failed attempt to acquire a row lock. After 60 failed attempts, the task is ended.

Examples:

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

View the background data migration task.

SELECT * FROM pathman_concurrent_part_tasks;

Split range partitions

To split an excessively large range partition into smaller partitions, use the following function:

split_range_partition(partition      REGCLASS,            -- The OID of the partition.
                      split_value    ANYELEMENT,          -- The split value.
                      partition_name TEXT DEFAULT NULL) -- The name of the added partition

Examples:

Use the table in the Perform range partitioning section with the following structure.

\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,              -- The OID of the partition.
                      '2016-11-10 00:00:00'::timestamp,     -- The point in time at which the partition is split.
                      'part_test_1_2');                     -- The name of the new partition.

The partition is split into the following partitions:

\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

Data is automatically migrated to the other partition.

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

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

The following example describes the inheritance relationship.

\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,    -- The added partition.
              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

Only range partitions are supported and they must be adjacent partitions. Call the following function to merge range partitions:

--- Specify two partitions that you want to merge.  
merge_range_partitions(partition1 REGCLASS, partition2 REGCLASS)

Examples:

Use the table in the Split range partitions section to merge partitions.

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

Note

The following error is returned If you merge the partitions that are not adjacent.

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 you merge the partitions, one of the 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 new range partition

You can use several methods to add partitions to a primary table that was partitioned. Three methods are described here: append a new range partition, prepend a new range partition, and specify the start value of partitions.

Append a new range partition

When you append a new partition to the primary table, the interval that is specified when the partitioned table was created is used. You can query the interval value of each partitioned table from the pathman_config table.

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

Call the following function to append a new range partition (you cannot specify the tablespace).

append_range_partition(parent         REGCLASS,            -- The OID of the primary table.
                       partition_name TEXT DEFAULT NULL,   -- The name of the new partition. This parameter is not required.
                       tablespace     TEXT DEFAULT NULL)   -- The tablespace for the new partition. This parameter is not required.

Examples:

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 new range partition

Call the following function to prepend a new range partition.

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

Examples:

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

Specify the start value of partitions to add a new range partition

You can create new partitions by specifying the start value of the partitions. If the ranges of the partitions that you want to create do not overlap with existing partitions, the partitions are created. This method allows you to create nonadjacent partitions. For example, if the range of existing partitions is from 2010 to 2015, you can create a partition from 2020. You do not need to create a partition between 2015 and 2020. Function:

add_range_partition(relation       REGCLASS,    -- The OID of the primary table
                    start_value    ANYELEMENT,  -- The start value.
                    end_value      ANYELEMENT,  -- The end value.
                    partition_name TEXT DEFAULT NULL,  -- The name of the partition.
                    tablespace     TEXT DEFAULT NULL)  -- The tablespace for the partition.

Examples:

postgres=# select add_range_partition('part_test'::regclass,    -- The OID of the primary table.
                    '2020-01-01 00:00:00'::timestamp,  -- The start value.
                    '2020-02-01 00:00:00'::timestamp); -- The 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 partition range, call the following function:

drop_range_partition(partition TEXT,   -- The name of the partition.
                    delete_data BOOLEAN DEFAULT TRUE) -- Specifies whether to delete data of the partition. If you set the value to FALSE, data of the partition is migrated to the primary table.   

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

Delete all partitions and specify whether to migrate data to the primary table. 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.

Examples:

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

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

Query the data of the current primary table.

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

Delete a partition and data of the partition without migrating the data to the parent table.

SELECT drop_range_partition('part_test_3',true);

Query the data of the current 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 partitions and migrate the data of partitions to the primary table.

Query the data of the primary table.

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

Attach a table to a partition

Attach an existing table to a partition of a primary table. The table that you want to attach must have the same schema as the primary table. For example, the two tables must have the same dropped columns. The pg_attribute parameter specifies the schema of a table. Function:

attach_range_partition(relation    REGCLASS,    -- The OID of the primary table.
                       partition   REGCLASS,    -- The OID of the partition.
                       start_value ANYELEMENT,  -- The start value of the range for the partition.
                       end_value   ANYELEMENT)  -- The end value of the range for the partition.

Examples:

Create a table.

CREATE TABLE part_test_1 (like part_test including all);

Attach the table to a 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 the table, inheritance relationships and constraints are automatically created.

\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

When you detach a partition, the data is not deleted while the inheritance and constraints are deleted. Function:

detach_range_partition(partition REGCLASS)  -- Specify the partition that you want to convert into a common table.

Examples:

Query the data of the current primary table and partitioned table.

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

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

Unbind a partition.

SELECT detach_range_partition('part_test_2');

Query the data of the current primary table and partitioned table.

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

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

Disable the pg_pathman extension

You can disable the pg_pathman extension for a parent table. Function:

Important

The disable_pathman_for operation is irreversible. Proceed 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$

Examples:

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 and constraints remain unchanged. The pg_pathman extension does not intervene in the custom scan execution plan. The following example describes the execution plan after the pg_pathman extension is disabled:

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 a primary table

After all data of a primary table is migrated to the partitions, you can disable the primary table. Function:

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.

Examples:

SELECT set_enable_parent('part_test', false);

Enable automatic partition propagation

You can enable automatic partition propagation for a range-partitioned table. If the inserted data is not within the range of the existing partitions, a partition is automatically created.

set_auto(relation REGCLASS, value BOOLEAN)

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

It is enabled by default.

Examples:

Query the partitions of the current 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

If you insert data that is outside the range of existing partitions, new partitions are created based on the interval specified when the table was partitioned. This operation may require an extended period of time to complete.

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

Query the partitions.

\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 disable automatic partition propagation for range-partitioned tables because inappropriate automatic partition propagation may require an extended period of time to complete.

Configure a callback function

A callback function is automatically called each time a partition is created. For example, you can configure a callback function for DDL logical replication to store the statements in a table. Function:

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

Examples:

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 the test table.

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

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

--- Create partitions.
SELECT                                                           
create_range_partitions('tt'::regclass,                    -- The OID of the primary table.
                        'crt_time',                        -- The name of the partition key column.
                        '2016-10-25 00:00:00'::timestamp,  -- The start value of the range for the first partition.
                        interval '1 month',                -- The range or span of values that each partition covers.
                        24,                                -- The number of partitions.
                        false) ;

-- Check whether the callback function is called.

SELECT * FROM rec_part_ddl;

Sample result:

 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)