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 (Compatible with Oracle) cluster. The pg_pathman extension provides efficient partitioning capabilities and helps effectively manage table partitioning and improve the performance of partitioned tables.
Create the extension
test=# create extension pg_pathman;
CREATE EXTENSIONView the version of the extension
Run the following command to view installed extensions and the versions of the extensions. If the pg_pathman extension is installed, its name and version number are displayed in the command output.
test=# \dx
List of installed extensions
Name | Version | Schema | Description
------------+---------+------------+----------------------------------
pg_pathman | 1.5 | public | Partitioning tool for PostgreSQL
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(2 rows)Upgrade the extension
A PolarDB for PostgreSQL (Compatible with Oracle) cluster regularly upgrades the pg_pathman extension for better 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 partitioning management. In automatic partitioning management, use functions to automatically create partitions and migrate data from a parent table to partitions. In manual partitioning management, use functions to attach existing tables to a parent table or detach tables from a parent 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:
RuntimeAppendandRuntimeMergeAppend.Uses the
PartitionFilterfeature 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/TOstatement.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 parent 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.
Usage
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 parent 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 parent table. enable_parent BOOLEAN NOT NULL DEFAULT TRUE, -- Specifies whether to filter the parent 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();
Partitioning management
Perform range partitioning.
You can use four management functions to create range partitions. You can use two of the functions to specify the start value, interval, and number of partitions. The following section describes the functions.
create_range_partitions(relation REGCLASS, -- The OID of the parent table. attribute TEXT, -- The name of the partition key column. start_value ANYELEMENT, -- The start value of the range for the first partition. p_interval ANYELEMENT, -- The range or span of values that each partition covers. ANYELEMENT allows you to specify a data type as needed. p_count INTEGER DEFAULT NULL, -- The number of partitions that you want to create. partition_data BOOLEAN DEFAULT TRUE) -- Specifies whether to immediately migrate data from the parent table to partitions. We recommend that you invoke the partition_table_concurrently() function to perform non-blocking data migration. create_range_partitions(relation REGCLASS, -- The OID of the parent table. attribute TEXT, -- The name of the partition key column. start_value ANYELEMENT, -- The start value of the range for the first partition. p_interval INTERVAL, -- The range or span of values that each partition covers. INTERVAL specifies a specific period of time, which is used to partition tables based on time-related attributes. p_count INTEGER DEFAULT NULL, -- The number of partitions that you want to create. partition_data BOOLEAN DEFAULT TRUE) -- Specifies whether to immediately migrate data from the parent table to partitions. We recommend that you invoke the partition_table_concurrently() function to perform non-blocking data migration.You can use the other two functions to specify the start value, end value, and interval. The following section describes the functions:
create_partitions_from_range(relation REGCLASS, -- The OID of the parent table. attribute TEXT, -- The name of the partition key column. start_value ANYELEMENT, -- The start value of the range for the first partition. end_value ANYELEMENT, -- The end value of the range for the last partition. p_interval ANYELEMENT, -- The range or span of values that each partition covers. ANYELEMENT allows you to specify a data type as needed. partition_data BOOLEAN DEFAULT TRUE) -- Specifies whether to immediately migrate data from the parent table to partitions. We recommend that you invoke the partition_table_concurrently() function to perform non-blocking data migration. create_partitions_from_range(relation REGCLASS, -- The OID of the parent table. attribute TEXT, -- The name of the partition key column. start_value ANYELEMENT, -- The start value of the range for the first partition. end_value ANYELEMENT, -- The end value of the range for the last partition. p_interval INTERVAL, -- The range or span of values that each partition covers. INTERVAL specifies a specific period of time, which is used to partition tables based on time-related attributes. partition_data BOOLEAN DEFAULT TRUE) -- Specifies whether to immediately migrate data from the parent table to partitions. We recommend that you invoke the partition_table_concurrently() function to perform non-blocking data migration.Example:
Create a parent table that you want to partition. postgres=# create table part_test(id int, info text, crt_time timestamp not null); -- The partition key column must contain the NOT NULL constraint. CREATE TABLE Insert test data into the parent table. postgres=# insert into part_test select id,md5(random()::text),clock_timestamp() + (id||' hour')::interval from generate_series(1,10000) t(id); INSERT 0 10000 postgres=# select * from part_test limit 10; 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) Note: 1. The partition key column must contain the NOT NULL constraint. 2. The number of partitions must be sufficient to cover all existing records. Create partitions and ensure that each partition contains one month of data. postgres=# select create_range_partitions('part_test'::regclass, -- The OID of the parent 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 that you want to create. false) ; -- The data is not migrated. NOTICE: sequence "part_test_seq" does not exist, skipping create_range_partitions ------------------------- 24 (1 row) postgres-# \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 The data is still in the parent table because the data is not migrated. postgres=# select count(*) from only part_test; count ------- 10000 (1 row) Perform non-blocking data migration. partition_table_concurrently(relation REGCLASS, -- The OID of the parent table. batch_size INTEGER DEFAULT 1000, -- The number of records in a transaction that you want to migrate from the parent 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. postgres=# select partition_table_concurrently('part_test'::regclass, 10000, 1.0); NOTICE: worker started, you can stop it with the following command: select stop_concurrent_part_task('part_test'); partition_table_concurrently ------------------------------ (1 row) After the migration is completed, all data is migrated to the partitions, and the parent table is empty. postgres=# select count(*) from only part_test; count ------- 0 (1 row) After you migrate the data, we recommend that you disable the parent table. This way, the parent table is not included in the execution plan. postgres=# select set_enable_parent('part_test'::regclass, false); set_enable_parent ------------------- (1 row) postgres=# 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)NoteWhen 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 the data migration is completed, disable the parent 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 parent table. attribute TEXT, -- The name of the partition key column. partitions_count INTEGER, -- The number of partitions that you want to create. partition_data BOOLEAN DEFAULT TRUE) -- Specifies whether to immediately migrate data from the parent table to the partitions. We recommend that you invoke the partition_table_concurrently() function to perform non-blocking data migration.Example:
-- Create a parent table that you want to partition. postgres=# create table part_test(id int, info text, crt_time timestamp not null); -- The partition key column must contain the NOT NULL constraint. CREATE TABLE Insert test data into the parent table. postgres=# insert into part_test select id,md5(random()::text),clock_timestamp() + (id||' hour')::interval from generate_series(1,10000) t(id); INSERT 0 10000 postgres=# select * from part_test limit 10; 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) Note: 1. The partition key column must contain the NOT NULL constraint. Create 128 partitions postgres=# select create_hash_partitions('part_test'::regclass, -- The OID of the parent 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. create_hash_partitions ------------------------ 128 (1 row) postgres=# \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_0, part_test_1, part_test_10, part_test_100, part_test_101, part_test_102, part_test_103, part_test_104, part_test_105, part_test_106, part_test_107, part_test_108, part_test_109, part_test_11, part_test_110, part_test_111, part_test_112, part_test_113, part_test_114, part_test_115, part_test_116, part_test_117, part_test_118, part_test_119, part_test_12, part_test_120, part_test_121, part_test_122, part_test_123, part_test_124, part_test_125, part_test_126, part_test_127, 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_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_36, part_test_37, part_test_38, part_test_39, part_test_4, part_test_40, part_test_41, part_test_42, part_test_43, part_test_44, part_test_45, part_test_46, part_test_47, part_test_48, part_test_49, part_test_5, part_test_50, part_test_51, part_test_52, part_test_53, part_test_54, part_test_55, part_test_56, part_test_57, part_test_58, part_test_59, part_test_6, part_test_60, part_test_61, part_test_62, part_test_63, part_test_64, part_test_65, part_test_66, part_test_67, part_test_68, part_test_69, part_test_7, part_test_70, part_test_71, part_test_72, part_test_73, part_test_74, part_test_75, part_test_76, part_test_77, part_test_78, part_test_79, part_test_8, part_test_80, part_test_81, part_test_82, part_test_83, part_test_84, part_test_85, part_test_86, part_test_87, part_test_88, part_test_89, part_test_9, part_test_90, part_test_91, part_test_92, part_test_93, part_test_94, part_test_95, part_test_96, part_test_97, part_test_98, part_test_99 The data is still in the parent table because the data is not migrated. postgres=# select count(*) from only part_test; count ------- 10000 (1 row) Perform non-blocking data migration. partition_table_concurrently(relation REGCLASS, -- The OID of the parent table. batch_size INTEGER DEFAULT 1000, -- The number of records in a transaction that you want to migrate from the parent 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. postgres=# select partition_table_concurrently('part_test'::regclass, 10000, 1.0); NOTICE: worker started, you can stop it with the following command: select stop_concurrent_part_task('part_test'); partition_table_concurrently ------------------------------ (1 row) After the migration is completed, all data is migrated to the partitions, and the parent table is empty. postgres=# select count(*) from only part_test; count ------- 0 (1 row) After you migrate the data, we recommend that you disable the parent table. This way, the parent table is not included in the execution plan. postgres=# select set_enable_parent('part_test'::regclass, false); set_enable_parent ------------------- (1 row) Query a single partition. postgres=# 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 pg_pathman extension automatically converts the statement. In traditional inheritance-based partitioning, you cannot filter partitions by using a statement similar to select * from part_test where crt_time = '2016-10-25 00:00:00'::timestamp;. postgres=# \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 | | The following section describes the constraints on the partitioned tables: Check constraints: "pathman_part_test_122_3_check" CHECK (get_hash_part_idx(timestamp_hash(crt_time), 128) = 122) Inherits: part_testNoteWhen 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 the data migration is completed, disable the parent 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: Functions:
with tmp as (delete from <parent table> limit xx nowait returning *) insert into <partitions> select * from tmp select array_agg(ctid) from <parent table> limit xx for update nowati -- You can also use this statement to label rows. Then, perform DELETE and INSERT operationsFunction:
partition_table_concurrently(relation REGCLASS, -- The OID of the parent table. batch_size INTEGER DEFAULT 1000, -- The number of records in a transaction that you want to migrate from the parent 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.Example:
postgres=# select partition_table_concurrently('part_test'::regclass, 10000, 1.0); NOTICE: worker started, you can stop it with the following command: select stop_concurrent_part_task('part_test'); partition_table_concurrently ------------------------------ (1 row)To stop the data migration task, invoke the following function:
stop_concurrent_part_task(relation REGCLASS)View the background data migration task.
postgres=# select * from pathman_concurrent_part_tasks; userid | pid | dbid | relid | processed | status --------+-----+------+-------+-----------+-------- (0 rows)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 partitionExample:
postgres=# \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 postgres=# \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_testSplit the partition.
postgres=# 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. split_range_partition ----------------------------------------------- {"2016-10-25 00:00:00","2016-11-25 00:00:00"} (1 row)The partition is split into the following partitions:
postgres=# \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 postgres=# \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_testData is automatically migrated to the other partition.
postgres=# select count(*) from part_test_1; count ------- 373 (1 row) postgres=# select count(*) from part_test_1_2; count ------- 360 (1 row)The following example describes the inheritance relationship:
postgres=# \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_9Merge range partitions.
Invoke the following function to merge range partitions:
-- Specify two partitions that you want to merge. The partitions must be adjacent. merge_range_partitions(partition1 REGCLASS, partition2 REGCLASS)Example:
postgres=# 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 An error is returned because the partitions are not adjacent. You can merge adjacent partitions. postgres=# select merge_range_partitions('part_test_1'::regclass, 'part_test_1_2'::regclass) ; merge_range_partitions ------------------------ (1 row)After you merge the partitions, one of the partitions is deleted.
postgres=# \d part_test_1_2 Did not find any relation named "part_test_1_2". postgres=# \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 postgres=# select count(*) from part_test_1; count ------- 733 (1 row)Append a new range partition.
You can use several methods to add partitions to a parent table that was partitioned. One method is to add partitions to the end of the parent table.
When you add a new partition to a parent table, the interval that is specified when the partitioned table was created is used. You can query the interval from the pathman_config table:
postgres=# select * from pathman_config; partrel | attname | parttype | range_interval -----------+----------+----------+---------------- part_test | crt_time | 2 | 1 mon (1 row)Append a new range partition (you cannot specify the tablespace).
append_range_partition(parent REGCLASS, -- The OID of the parent 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.Example:
postgres=# select append_range_partition('part_test'::regclass); append_range_partition ------------------------ public.part_test_25 (1 row) postgres=# \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 postgres=# \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_testPrepend a new range partition.
Use the following function to prepend a partition to a table:
prepend_range_partition(parent REGCLASS, partition_name TEXT DEFAULT NULL, tablespace TEXT DEFAULT NULL)Example:
postgres=# select prepend_range_partition('part_test'::regclass); prepend_range_partition ------------------------- public.part_test_26 (1 row) postgres=# \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 postgres=# \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_testAdd a 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, you can create the partitions. This method allows you to create non-contiguous 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 parent table start_value ANYELEMENT, -- The start value of the range for the partition. end_value ANYELEMENT, -- The end value of the range for the partition. partition_name TEXT DEFAULT NULL, -- The name of the partition. tablespace TEXT DEFAULT NULL) -- The tablespace for the partitionExample:
postgres=# select add_range_partition('part_test'::regclass, -- The OID of the parent table '2020-01-01 00:00:00'::timestamp, -- The start value of the range for the partition. '2020-02-01 00:00:00'::timestamp); -- The end value of the range for the partition. add_range_partition --------------------- public.part_test_27 (1 row) postgres=# \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_testDelete a partition
To delete a single partition range, invoke the following function:
drop_range_partition(partition TEXT, -- The partition name. 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 parent table. Drop RANGE partition and all of its data if delete_data is true.Example:
-- Delete a partition and migrate data of the partition to the parent table. postgres=# select drop_range_partition('part_test_1',false); NOTICE: 733 rows copied from part_test_1 drop_range_partition ---------------------- part_test_1 (1 row) postgres=# select drop_range_partition('part_test_2',false); NOTICE: 720 rows copied from part_test_2 drop_range_partition ---------------------- part_test_2 (1 row) postgres=# 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. postgres=# select drop_range_partition('part_test_3',true); drop_range_partition ---------------------- part_test_3 (1 row) postgres=# select count(*) from part_test; count ------- 9256 (1 row) postgres=# select count(*) from only part_test; count ------- 1453 (1 row)Delete all partitions and specify whether to migrate the data of partitions to the parent 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.Example:
postgres=# select drop_partitions('part_test'::regclass, false); -- Delete all partitions and migrate the data of the partitions to the parent table. NOTICE: function public.part_test_upd_trig_func() does not exist, skipping NOTICE: 744 rows copied from part_test_4 NOTICE: 672 rows copied from part_test_5 NOTICE: 744 rows copied from part_test_6 NOTICE: 720 rows copied from part_test_7 NOTICE: 744 rows copied from part_test_8 NOTICE: 720 rows copied from part_test_9 NOTICE: 744 rows copied from part_test_10 NOTICE: 744 rows copied from part_test_11 NOTICE: 720 rows copied from part_test_12 NOTICE: 744 rows copied from part_test_13 NOTICE: 507 rows copied from part_test_14 NOTICE: 0 rows copied from part_test_15 NOTICE: 0 rows copied from part_test_16 NOTICE: 0 rows copied from part_test_17 NOTICE: 0 rows copied from part_test_18 NOTICE: 0 rows copied from part_test_19 NOTICE: 0 rows copied from part_test_20 NOTICE: 0 rows copied from part_test_21 NOTICE: 0 rows copied from part_test_22 NOTICE: 0 rows copied from part_test_23 NOTICE: 0 rows copied from part_test_24 NOTICE: 0 rows copied from part_test_25 NOTICE: 0 rows copied from part_test_26 NOTICE: 0 rows copied from part_test_27 drop_partitions ----------------- 24 (1 row) postgres=# select count(*) from part_test; count ------- 9256 (1 row) postgres=# \dt part_test_4 No matching relations found.Attach an existing table to a partitioned table.
Attach an existing table to a parent table. The table that you want to attach must have the same schema as the parent 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 parent 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.Example:
postgres=# create table part_test_1 (like part_test including all); CREATE TABLE postgres=# \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 | | postgres=# \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 | | postgres=# 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); attach_range_partition ------------------------ part_test_1 (1 row) When you attach the table, inheritance relationships and constraints are automatically created. postgres=# \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_testDetach a partition from the parent table (convert the partition into a regular table).
Remove a partition from the parent table inheritance. The data is not deleted. The inheritance and constraints are deleted. Function:
detach_range_partition(partition REGCLASS) -- Specify the partition that you want to convert into a regular table.Example:
postgres=# select count(*) from part_test; count ------- 9256 (1 row) postgres=# select count(*) from part_test_2; count ------- 733 (1 row) postgres=# select detach_range_partition('part_test_2'); detach_range_partition ------------------------ part_test_2 (1 row) postgres=# select count(*) from part_test_2; count ------- 733 (1 row) postgres=# select count(*) from part_test; count ------- 8523 (1 row)Permanently disable the pg_pathman extension for a parent table.
You can disable the pg_pathman extension for a single parent table. Function:
disable_pathman_for(relation TEXT) Permanently disable pg_pathman partitioning mechanism for the specified parent table and remove the insert trigger if it exists. All partitions and data remain unchanged. postgres=# \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$Example:
postgres=# select disable_pathman_for('part_test'); NOTICE: drop cascades to 23 other objects DETAIL: drop cascades to trigger part_test_upd_trig on table part_test_3 drop cascades to trigger part_test_upd_trig on table part_test_4 drop cascades to trigger part_test_upd_trig on table part_test_5 drop cascades to trigger part_test_upd_trig on table part_test_6 drop cascades to trigger part_test_upd_trig on table part_test_7 drop cascades to trigger part_test_upd_trig on table part_test_8 drop cascades to trigger part_test_upd_trig on table part_test_9 drop cascades to trigger part_test_upd_trig on table part_test_10 drop cascades to trigger part_test_upd_trig on table part_test_11 drop cascades to trigger part_test_upd_trig on table part_test_12 drop cascades to trigger part_test_upd_trig on table part_test_13 drop cascades to trigger part_test_upd_trig on table part_test_14 drop cascades to trigger part_test_upd_trig on table part_test_15 drop cascades to trigger part_test_upd_trig on table part_test_16 drop cascades to trigger part_test_upd_trig on table part_test_17 drop cascades to trigger part_test_upd_trig on table part_test_18 drop cascades to trigger part_test_upd_trig on table part_test_19 drop cascades to trigger part_test_upd_trig on table part_test_20 drop cascades to trigger part_test_upd_trig on table part_test_21 drop cascades to trigger part_test_upd_trig on table part_test_22 drop cascades to trigger part_test_upd_trig on table part_test_23 drop cascades to trigger part_test_upd_trig on table part_test_24 drop cascades to trigger part_test_upd_trig on table part_test_25 disable_pathman_for --------------------- (1 row) postgres=# \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 postgres=# \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_testAfter 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:
postgres=# 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)ImportantThe operation is irreversible. Proceed with caution.
Advanced partitioning management
Disable a parent table.
After you migrate all data of a parent table to the partitions, you can disable the parent 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.Example:
select set_enable_parent('part_test', false);Enable automatic partition propagation for a range-partitioned table.
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.Example:
postgres=# \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 postgres=# \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 postgres=# \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. postgres=# insert into part_test values (1,'test','2222-01-01'::timestamp); If a large number of partitions is created, the range of the inserted data is wide. postgres=# \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, ..................................... omittedNoteWe 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 invoked 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" }Example:
Callback function postgres=# 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; Test table postgres=# create table tt(id int, info text, crt_time timestamp not null); CREATE TABLE Configure a callback function for the test table. select set_init_callback('tt'::regclass, 'f_callback_test'::regproc); Create partitions. postgres=# select create_range_partitions('tt'::regclass, -- The OID of the parent 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) ; create_range_partitions ------------------------- 24 (1 row) Check whether the callback function is invoked. postgres=# select * from rec_part_ddl; 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)