This topic describes the common usage scenarios of the pg_pathman plug-in.
Background information
To improve the performance of partitioned tables, the pg_pathman plug-in is introduced to . This plug-in allows you to manage partitions and optimize the partitioning scheme.
Create the pg_pathman extension
test=# create extension pg_pathman;
CREATE EXTENSIONView installed extensions
Run the following commands to view installed extensions and the version of the pg_pathman plug-in.
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 plug-in
upgrades the plug-in on a regular basis to improve database services. To upgrade the plug-in, perform the following steps:
Upgrade the corresponding cluster to the latest version.
Features
Support for hash and range partitioning.
Support for automatic and manual partition management. In automatic partition management, the system uses functions to create partitions and migrate data in primary tables to partitioned tables. In manual partition management, you can use functions to attach existing tables to partitioned tables or detach tables from partitioned tables.
Support for several partition fields including custom domains and common data types such as INT, FLOAT, and DATE.
Effective query planning for partitioned tables by using joins and subselects.
The
RuntimeAppendandRuntimeMergeAppendparameters enable the system to select partitions in a dynamic way.PartitionFilter: an efficient drop-in replacement for INSERT triggers.Automatic partition creation for newly inserted data. This feature applies only to range partitioning.
Support for the
COPY FROM and COPY TOstatements that allow efficient read or write operations on partitioned tables.Partition fields can be updated. To update partition fields, add a trigger. If you do not need to update partition fields, we recommend that you do not add the trigger because the trigger may have negative impacts on performance.
User-defined callback functions are automatically triggered when partitions are created.
Non-blocking table partitioning is supported. Non-blocking data migration from primary tables to partitioned tables is automatically performed in the background.
Support for postgres_fdw or other foreign data wrappers (FDWs) by configuring the
pg_pathman.insert_into_fdw=(disabled | postgres | any_fdw)parameter.
Usage
For more information, visit GitHub.
Views and tables
The pg_pathman plug-in uses functions to maintain partitioned tables and creates views that allow you to view the status of partitioned tables, as described in the following examples:
pathman_config
CREATE TABLE IF NOT EXISTS pathman_config ( partrel REGCLASS NOT NULL PRIMARY KEY, -- The OID of the primary table. attname TEXT NOT NULL, -- The column name of the partition. parttype INTEGER NOT NULL, -- The type of the partition (hash or range). range_interval TEXT, -- The interval of range partitions. CHECK (parttype IN (1, 2)) /* check for allowed part types */ );pathman_config_params
CREATE TABLE IF NOT EXISTS pathman_config_params ( partrel REGCLASS NOT NULL PRIMARY KEY, -- 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 expand partitions that do not exist during INSERT operations. init_callback REGPROCEDURE NOT NULL DEFAULT 0); -- The OID of the callback function when the partition is 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 column name of the partition. 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 column name of the partition. 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 call the partition_table_concurrently() function to run 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 column name of the partition. 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 column name of the partition. 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 call the partition_table_concurrently() function to run non-blocking data migration.Example:
Create a primary table that needs to be partitioned. postgres=# create table part_test(id int, info text, crt_time timestamp not null); -- All partition columns must contain the NOT NULL constraint. CREATE TABLE Insert a large amount of test data to simulate a primary table that already contains data. 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. All partition columns 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 primary table. 'crt_time', -- The column name of the partition. '2016-10-25 00:00:00'::timestamp, -- The start value. interval '1 month', -- The interval of an interval data type, which is applicable to ingestion-time partitioned tables. 24, -- The number of partitions. 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 primary table because it 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 primary table. batch_size INTEGER DEFAULT 1000, -- The number of records to copy from the primary table at a time. sleep_time FLOAT8 DEFAULT 1.0) -- The time interval between migration attempts if one or more rows in the batch are locked by other queries. pg_pathman waits for the specified time and tries again up to 60 times before quitting. 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, all data is migrated to the partitions, and the primary table is empty. postgres=# select count(*) from only part_test; count ------- 0 (1 row) After the data is migrated, we recommend that you disable the primary table so that the primary table will not be 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:
All partition columns 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 a management function to create range partitions. You can specify the start value, interval, and number of partitions, as described in the following examples:
create_hash_partitions(relation REGCLASS, -- The OID of the primary table. attribute TEXT, -- The column name of the partition. partitions_count INTEGER, -- The number of partitions to be created. 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.Example:
Create a primary table that needs to be partitioned. postgres=# create table part_test(id int, info text, crt_time timestamp not null); -- All partition columns must contain the NOT NULL constraint. CREATE TABLE Insert a large amount of test data to simulate a primary table that already contains data. 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. All partition columns must contain the NOT NULL constraint. Create 128 partitions postgres=# select create_hash_partitions('part_test'::regclass, -- The OID of the primary table. 'crt_time', -- The column name of the partition. 128, -- The number of partitions to be created. 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 primary table because it 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 primary table. batch_size INTEGER DEFAULT 1000, -- The number of records to copy from the primary table at a time. sleep_time FLOAT8 DEFAULT 1.0) -- The time interval between migration attempts if one or more rows in the batch are locked by other queries. pg_pathman waits for the specified time and tries again up to 60 times before quitting. 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, all data is migrated to the partitions, and the primary table is empty. postgres=# select count(*) from only part_test; count ------- 0 (1 row) After the data is migrated, we recommend that you disable the primary table so that the primary table will not be included in the execution plan. postgres=# select set_enable_parent('part_test'::regclass, false); set_enable_parent ------------------- (1 row) Query only 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 following content describes the constraints on the partitioned tables: pg_pathman automatically completes the conversion. For traditional inheritance, expressions similar to select * from part_test where crt_time = '2016-10-25 00:00:00'::timestamp; cannot filter partitions. 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 | | 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:
All partition columns must contain the NOT NULL constraint.
Perform non-blocking data migration.
After data migration is completed, disable the primary table.
pg_pathman is not subject to expressions. So the command
select * from part_test where crt_time = '2016-10-25 00:00:00'::timestamp;can also be used for 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 a partition.
If the data of the primary table is not migrated to partitioned tables when the partitioned tables are created, the data can be migrated to the partitions by calling a non-blocking migration function. Usage:
with tmp as (delete from a primary table limit xx nowait returning *) insert into a partition select * from tmp You can also use select array_agg(ctid) from a primary table limit xx for update nowati. Then, execute the DELETE and INSERT statements.You can define the function by using the following syntax:
partition_table_concurrently(relation REGCLASS, -- The OID of the primary table. batch_size INTEGER DEFAULT 1000, -- The number of records to copy from the primary table at a time. sleep_time FLOAT8 DEFAULT 1.0) -- The time interval between migration attempts if one or more rows in the batch are locked by other queries. pg_pathman waits for the specified time and tries again up to 60 times before quitting.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 migration task, call 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.
If a partition is too large and you want to split the partition into two partitions, use the following method. This method is supported only if range partitioning is used.
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 new partition.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_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 split value. 'part_test_1_2'); -- The name of the partition. split_range_partition ----------------------------------------------- {"2016-10-25 00:00:00","2016-11-25 00:00:00"} (1 row)The table is split into the following two tables:
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 shows 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 table. part_test_2, part_test_20, part_test_21, part_test_22, part_test_23, part_test_24, part_test_3, part_test_4, part_test_5, part_test_6, part_test_7, part_test_8, part_test_9Merge range partitions.
Only range partitions are supported. Call the following function:
Specify two partitions to be merged, which must be adjacent partitions. 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. Adjacent partitions can be merged. postgres=# select merge_range_partitions('part_test_1'::regclass, 'part_test_1_2'::regclass) ; merge_range_partitions ------------------------ (1 row)After the merge is complete, one of the partitions are 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)Add a range partition following the last partition.
You can use several methods to add partitions for primary tables that have been partitioned. One method is to add partitions following the last partition.
When a new partition is added, the interval that is specified when the partitioned table was first created is used. You can query the interval of each partitioned table when it is created for the first time by running the pathman_config command:
postgres=# select * from pathman_config; partrel | attname | parttype | range_interval -----------+----------+----------+---------------- part_test | crt_time | 2 | 1 mon (1 row)Add a new range partition (the tablespace cannot be specified).
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 where the new partition is stored. 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_testAdd a range partition at the beginning of partitions.
Use the following syntax to add a partition at the beginning of the 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. New partitions can be created if the ranges do not overlap with existing partitions. This method allows you to create non-continuous partitions. For example, if the range of existing partitions are from 2010 to 2015, you can create a partition from 2020. You do not need to create a partition between 2015 and 2020. Use the following syntax:
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 name of the tablespace in which a partition resides.Example:
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. 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, call the following function:
drop_range_partition(partition TEXT, -- The name of the partition to be deleted. delete_data BOOLEAN DEFAULT TRUE) -- Specifies whether to delete the data of the partition. If you set the value to FALSE, the data of the partition is migrated to the primary 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 primary 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 the data of the partition without migrating the data to the primary 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 data to the primary table. Use the following syntax:
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 to the primary 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 a table to a partition.
Attach a table to a partitioned primary table. The table 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. Use the following syntax:
attach_range_partition(relation REGCLASS, -- The OID of the primary table. partition REGCLASS, -- The OID of the partition. start_value ANYELEMENT, -- The start value. end_value ANYELEMENT) -- The start value.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 the table is attached, 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 primary table (convert the partition into a normal table).
Delete a partition from the primary table inheritance. The data is not deleted. The inheritance and constraints are deleted. Use the following syntax:
detach_range_partition(partition REGCLASS) -- Specify the name of the partition and convert the partition to a normal 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 plug-in for a partitioned table.
You can disable the pg_pathman plug-in for a single partitioned primary table. Use the following syntax to define the 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 the pg_pathman plug-in is disabled, the inheritance and constraints remain unchanged. The pg_pathman plug-in does not intervene in the custom scan execution plan. The execution plan after the pg_pathman plug-in 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
disable_pathman_foroperation is irreversible. Proceed with caution.
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. Use the following syntax to define the 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);Automatic partition propagation
Automatic partition propagation is supported for range partitioned tables. 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 a value to be inserted is beyond all the existing range partitions, some new partitions are created based on the interval when the table is partitioned. This operation may take a long time. postgres=# insert into part_test values (1,'test','2222-01-01'::timestamp); After the data is inserted, a large number of partitions are created because the range of the inserted values is large. 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, ..................................... A large number of partitionsNoteWe recommend that you disable auto partition propagation for range partitioning because inappropriate auto propagation may consume a lot of time.
Callback functions that are triggered for each partition creation
A callback function is a function that is automatically triggered for each partition creation. For example, a callback function can record the DDL statements that you use to run logical replication and store the statements in a table. Use the following syntax to define the callback 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 Set the 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 primary table. 'crt_time', -- The column name of the partition. '2016-10-25 00:00:00'::timestamp, -- The start value. interval '1 month', -- The interval of an interval data type, which is applicable to ingestion-time partitioned tables. 24, -- The number of partitions. false) ; create_range_partitions ------------------------- 24 (1 row) Check whether the callback function is called. 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)