このトピックでは、pg_pathman プラグインの一般的な使用シナリオについて説明します。
このタスクについて
パーティション分割テーブルのパフォーマンスを向上させるため、POLARDB for PostgreSQL に pg_pathman プラグインが導入されました。 このプラグインは、パーティションの管理および最適化に使用できます。
pg_pathman 拡張を作成する
test=# create extension pg_pathman;
CREATE EXTENSIONインストール済みの拡張を表示する
以下のコマンドを実行して、インストール済みの拡張と pg_pathman プラグインのバージョンを表示します。
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)プラグインをアップグレードする
POLARDB for PostgreSQL は、プラグインを定期的にアップグレードして、データベースサービスを良好な状態に保ちます。 プラグインをアップグレードするには、以下の手順を実行します。
- 対応するクラスターを最新バージョンにアップグレードします。 なお、この機能は開発段階にあるため、アップグレードを実行するにはチケットを起票する必要があります。
- 以下のステートメントを実行して、更新を完了します。
ALTER EXTENSION pg_pathman UPDATE; SET pg_pathman.enable = t;
機能
- ハッシュおよび範囲分割をサポートします。
- 自動および手動パーティション管理をサポートします。 自動パーティション管理では、システムは関数を使用してパーティションを作成し、プライマリテーブルのデータをパーティションに移行します。 手動パーティション管理では、関数を使用して既存のテーブルをパーティションテーブルにアタッチしたり、テーブルをパーティション分割テーブルからデタッチしたりできます。
- カスタムドメインや、整数、浮動小数点、日付などの一般的なデータ型を含む、複数の分割フィールドをサポートします。
- 結合とサブクエリを使用して、分割テーブルの効果的なクエリ計画を作成できます。
RuntimeAppendおよびRuntimeMergeAppendカスタムプランノードで、実行時にパーティションを選択します。PartitionFilter：効率の高い、INSERT トリガー互換の機能です。- 新しく挿入されたデータに対して自動的にパーティションを作成します (範囲パーティションのみ)。
COPY FROM/TOステートメントをサポートし、パーティションでの直接の読み書きが可能です。- 分割フィールドを更新できます。 分割フィールドを更新するには、トリガーを追加する必要があります。 分割フィールドを更新する必要がない場合は、パフォーマンスへの悪影響を避けるためにトリガーを追加しないことを推奨します。
- ユーザー定義のコールバック関数は、パーティションが作成されると自動的にトリガーされます。
- ブロッキングなしで同時にテーブルを分割し、バックグラウンドでプライマリテーブルからパーティションへ自動的にデータを移行します。
- postgres_fdw または
pg_pathman.insert_into_fdw=(disabled | postgres | any_fdw)パラメーターの設定により外部データラッパー (FDW) をサポートします。
使用方法
詳細については、「 https://github.com/postgrespro/pg_pathman」をご参照ください。
ビューおよびテーブル
pg_pathman プラグインは、関数を使用して分割テーブルを維持し、分割テーブルのステータスを表示するためのビューを作成します。以下に例を示します。
- 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();
パーティション管理
- 範囲パーティション
範囲パーティションの作成には、4 つの管理関数を使用します。 開始値、間隔、およびパーティション数の指定には、2 つの関数を使用します。 これら 2 つの関数は以下のように定義されます。
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, applicable to any type 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 the interval type, 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.他の 2 つの関数は、開始値、終了値、および間隔の指定に使用します。 定義は以下のとおりです。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, applicable to any type 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 the interval type, 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.例：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 the interval type, 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) Run 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)注 範囲分割を使用する場合は、以下の点にご注意ください。- すべてのパーティション列が NOT NULL 制約付きである必要があります。
- 既存のすべてのレコードに十分対応できるパーティション数とする必要があります。
- ノンブロッキングデータ移行を実行します。
- データ移行の完了後、プライマリテーブルを無効化します。
- ハッシュ分割
ハッシュパーティションの作成には、管理関数を使用します。 開始値、間隔、およびパーティション数を指定できます。以下に例を示します。
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.例：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) Run 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 constraints on partitions are as follows: 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_test注 ハッシュパーティションを使用する場合は、以下の点に注意してください。- すべてのパーティション列は NOT NULL 制約付きである必要があります。
- ノンブロッキングデータ移行を実行します。
- データ移行の完了後、プライマリテーブルを無効化する必要があります。
- pg_pathman は式の対象ではありません。 そのため、ハッシュパーティションの作成にはコマンド
select * from part_test where crt_time = '2016-10-25 00:00:00'::timestamp;も使用できます。 - ハッシュパーティション列は、int 型の列に限定されません。 列の型はハッシュ関数によって自動的に変換されます。
- データをパーティションに移行する
パーティションの作成時にプライマリテーブルのデータがパーティションに移行されない場合、非ブロッキング移行関数を呼び出すことにより、データをパーティションに移行できます。 以下のコマンドを実行します。
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.関数は以下のとおりです。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)移行タスクを停止するには、以下の関数を呼び出します。stop_concurrent_part_task(relation REGCLASS)バックグラウンドで実行されているデータ移行タスクを確認します。postgres=# select * from pathman_concurrent_part_tasks; userid | pid | dbid | relid | processed | status --------+-----+------+-------+-----------+-------- (0 rows) - 範囲パーティションの分割
パーティションが大きくなったため 2 つのパーティションに分割する場合は、以下の方法を使用します (範囲パーティションのみサポートされています)。
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.例：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_test分割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 partitioned table. split_range_partition ----------------------------------------------- {"2016-10-25 00:00:00","2016-11-25 00:00:00"} (1 row)分割によって作成される 2 つのテーブルは以下のとおりです。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_testデータは自動的に他のパーティションに移行されます。postgres=# select count(*) from part_test_1; count ------- 373 (1 row) postgres=# select count(*) from part_test_1_2; count ------- 360 (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_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_9 - 範囲パーティションのマージ
範囲パーティションのみサポートされています。 以下の関数を呼び出します。
Specify two partitions to be merged, which must be adjacent partitions. merge_range_partitions(partition1 REGCLASS, partition2 REGCLASS)例：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)マージが完了すると、パーティションの 1 つが削除されます。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) - 最後のパーティションの後にパーティションを追加する
以前にパーティション分割されたプライマリテーブルにパーティションを追加するには、いくつかの方法があります。 最後のパーティションの後にパーティションを追加する方法もそのひとつです。
新しいパーティションが追加されると、以前に分割されたテーブルの間隔が使用されます。 pathman_config コマンドを実行して、パーティション分割テーブルを初めて作成したときの間隔を照会できます。postgres=# select * from pathman_config; partrel | attname | parttype | range_interval -----------+----------+----------+---------------- part_test | crt_time | 2 | 1 mon (1 row)新しい範囲パーティションを追加します (テーブルスペースは指定できません)。append_range_partition(parent REGCLASS, -- The OID of the primary table. partition_name TEXT DEFAULT NULL, -- (Optional) The name of the new partition. Default value: null. tablespace TEXT DEFAULT NULL) -- (Optional) The tablespace where the new partition is stored. Default value: null.例：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_test - パーティションの先頭に範囲パーティションを追加する
パーティションの先頭にパーティションを追加します。 関数は以下のとおりです。
prepend_range_partition(parent REGCLASS, partition_name TEXT DEFAULT NULL, tablespace TEXT DEFAULT NULL)例：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_test - パーティションを追加する
パーティションの開始値を指定して、新しいパーティションを作成できます。 範囲が既存のパーティションと重複しない場合は、新しいパーティションを作成できます。 この方法では、非連続パーティションを作成できます。 たとえば、既存のパーティションの範囲が2010 から 2015の場合でも、2020から新しいパーティションを作成できます。 2015 から 2020 の間のパーティションを作成する必要はありません。 関数は以下のとおりです。
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.例：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_test - パーティションを削除する
単一のパーティション範囲を削除するには、以下の関数を呼び出します。
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.例：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)すべてのパーティションを削除し、データをプライマリテーブルに移行するかどうかを指定します。 関数は以下のとおりです。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.例：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. - テーブルをパーティション分割テーブルにアタッチする
テーブルをパーティション分割されたプライマリテーブルにアタッチします。 テーブルには、削除された列を含め、プライマリテーブルと同じスキーマが必要です (pg_attributeの整合性を確認してください)。 関数は以下のとおりです。
attach_range_partition(relation REGCLASS, -- The OID of the primary table. partition REGCLASS, -- The OID of the partition table. start_value ANYELEMENT, -- The start value. end_value ANYELEMENT) -- The start value.例：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 created automatically. 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_test - プライマリテーブルからパーティションをデタッチする (パーティションを通常のテーブルに変換する)
プライマリテーブルの継承からパーティションを削除します。 データは削除されません。 継承と制約が削除されます。 関数は以下のとおりです。
detach_range_partition(partition REGCLASS) -- Specify the name of the partition and convert the partition to a normal table.例：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) - パーティション分割テーブルのpg_pathman プラグインを永続的に無効にする
単一の分割されたプライマリテーブルの pg_pathman プラグインを無効化できます。 関数は以下のとおりです。
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$例：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_testpg_pathman プラグインを無効にしても、継承と制約は変更されません。 pg_pathman プラグインは、カスタムスキャン実行プランに介入しません。 pg_pathman プラグインを無効化した後の実行プラン：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)重要disable_pathman_forは取り消しできません。 実行には注意が必要です。
高度なパーティション管理
- プライマリテーブルを無効化する
プライマリテーブルの全データをパーティションに移行後、プライマリテーブルを無効にできます。 関数は以下のとおりです。
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.例：select set_enable_parent('part_test', false); - 自動分割の継続
自動分割の継続は、範囲パーティションテーブルでサポートされています。 挿入されたデータが既存のパーティションの範囲を超える場合、パーティションが自動的に作成されます。
set_auto(relation REGCLASS, value BOOLEAN) Enable/disable auto partition propagation (only for RANGE partitioning). It is enabled by default.例：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 When the inserted data is beyond the partitioning range, a large number of new partitions are created based on the interval when the primary table is partitioned. 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 too 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 partitions注 不適切な自動連続分割は時間を浪費する可能性があるため、自動連続分割は無効化することを推奨します。 - パーティションの作成ごとにトリガーされるコールバック関数
コールバック関数は、パーティションが作成されるたびに自動的にトリガーされる関数です。 たとえば、論理レプリケーションを実行してステートメントをテーブルに格納する DDL ステートメントをコールバック関数で記録できます。 コールバック関数は以下のとおりです。
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" }例：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 a partition 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 the interval type, 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)