本トピックでは、pg_pathman 拡張機能の一般的なユースケースを説明します。
背景情報
PolarDB for PostgreSQL は、パーティションテーブルのパフォーマンスを向上させる pg_pathman 拡張を含んでいます。この拡張は、パーティション管理と最適化のメカニズムを提供します。
pg_pathman 拡張機能の作成
説明
pg_pathman 拡張機能のパーティション管理機能を使用するには、お問い合わせください。
CREATE EXTENSION IF NOT EXISTS pg_pathman;拡張機能を作成した後、次の SQL ステートメントを実行してバージョンを確認できます。
SELECT extname,extversion FROM pg_extension WHERE extname = 'pg_pathman';以下の結果が返されます：
extname | extversion
------------+------------
pg_pathman | 1.5
(1 行)拡張のアップグレード
PolarDB for PostgreSQL は、データベースサービスを改善するため、定期的に拡張をアップグレードしています。拡張をアップグレードする前に、クラスターを最新バージョンにアップグレードする必要があります。
拡張機能
ハッシュパーティショニングとレンジパーティショニングをサポートしています。
関数によるパーティションの作成とプライマリテーブルからのデータ移行を行う、自動パーティション管理をサポートしています。また、関数を使用して既存のテーブルをアタッチまたはデタッチできる手動パーティション管理もサポートしています。
パーティションキー列として、カスタムドメインを含む int、float、date などの一般的なデータ型をサポートしています。
JOIN やサブセレクトを含む、パーティションテーブルに対する効率的なクエリプランを生成します。
カスタムプランノード
RuntimeAppendとRuntimeMergeAppendを使用して、動的パーティション選択を実装しています。PartitionFilterは、INSERT トリガーに代わる効率的な手段を提供します。新しいパーティションの自動作成をサポートしています。この機能は現在、レンジパーティションテーブルでのみ利用可能です。
copy from/toを使用したパーティションテーブルへの直接的な読み書き操作をサポートし、効率を向上させます。トリガーを介したパーティションキー列の更新をサポートしています。パフォーマンスへの影響を避けるため、パーティションキー列を更新する必要がない場合は、このトリガーを追加しないでください。
パーティションが作成されたときに自動的にトリガーされるカスタムコールバック関数を定義できます。
操作をブロックすることなく、バックグラウンドでパーティションテーブルを作成し、プライマリテーブルからパーティションへデータを移行します。
外部データラッパー (FDW) をサポートしています。
pg_pathman.insert_into_fdw=(disabled | postgres | any_fdw)パラメーターを設定することで、postgres_fdw またはその他の FDW をサポートできます。
使用方法
詳細については、「GitHub 上のプロジェクト」をご参照ください。
関連ビューおよびテーブル
pg_pathman は、関数を使用してパーティションテーブルを維持し、これらのテーブルのステータスを確認するための複数のビューを提供します。ビューは次のとおりです：
pathman_config
CREATE TABLE IF NOT EXISTS pathman_config ( partrel REGCLASS NOT NULL PRIMARY KEY, -- プライマリテーブルの OID attname TEXT NOT NULL, -- パーティションキー列の名前 parttype INTEGER NOT NULL, -- パーティショニング種類 (ハッシュまたは範囲) range_interval TEXT, -- 範囲パーティションの間隔 CHECK (parttype IN (1, 2)) /* 許可されているパーティショニング種類のチェック */ );pathman_config_params
CREATE TABLE IF NOT EXISTS pathman_config_params ( partrel REGCLASS NOT NULL PRIMARY KEY, -- プライマリテーブルの OID enable_parent BOOLEAN NOT NULL DEFAULT TRUE, -- オプティマイザでプライマリテーブルをフィルターするかどうかを指定します auto BOOLEAN NOT NULL DEFAULT TRUE, -- 挿入操作中にパーティションが存在しない場合、新しいパーティションを自動的に作成するかどうかを指定します init_callback REGPROCEDURE NOT NULL DEFAULT 0); -- パーティション作成用コールバック関数の OIDpathman_concurrent_part_tasks
-- 集合を返す関数のヘルパー関数 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
-- 集合を返す関数のヘルパー関数 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 つでは、開始値、間隔、パーティション数を指定できます。定義は次のとおりです：
create_range_partitions(relation REGCLASS, -- プライマリテーブルの OID
attribute TEXT, -- パーティションキー列の名前
start_value ANYELEMENT, -- 開始値
p_interval ANYELEMENT, -- 間隔。あらゆる種類のパーティションテーブルに適した任意のデータ型を使用できます。
p_count INTEGER DEFAULT NULL, -- 作成するパーティションの数
partition_data BOOLEAN DEFAULT TRUE) -- プライマリテーブルからパーティションにデータをすぐに移行するかどうかを指定します。これは推奨されません。代わりに非ブロッキング移行関数 partition_table_concurrently() を使用してください。
create_range_partitions(relation REGCLASS, -- プライマリテーブルの OID
attribute TEXT, -- パーティションキー列の名前
start_value ANYELEMENT, -- 開始値
p_interval INTERVAL, -- 間隔。interval データ型は、時間ベースのパーティションテーブルに使用します。
p_count INTEGER DEFAULT NULL, -- 作成するパーティションの数
partition_data BOOLEAN DEFAULT TRUE) -- プライマリテーブルからパーティションにデータをすぐに移行するかどうかを指定します。これは推奨されません。代わりに非ブロッキング移行関数 partition_table_concurrently() を使用してください。他の 2 つの関数では、開始値、終了値、および間隔を指定できます。定義は次のとおりです：
create_partitions_from_range(relation REGCLASS, -- プライマリテーブルの OID
attribute TEXT, -- パーティションキー列の名前
start_value ANYELEMENT, -- 開始値
end_value ANYELEMENT, -- 終了値
p_interval ANYELEMENT, -- 間隔。あらゆる種類のパーティションテーブルに適した任意のデータ型を使用できます。
partition_data BOOLEAN DEFAULT TRUE) -- プライマリテーブルからパーティションにデータをすぐに移行するかどうかを指定します。これは推奨されません。代わりに非ブロッキング移行関数 partition_table_concurrently() を使用してください。
create_partitions_from_range(relation REGCLASS, -- プライマリテーブルの OID
attribute TEXT, -- パーティションキー列の名前
start_value ANYELEMENT, -- 開始値
end_value ANYELEMENT, -- 終了値
p_interval INTERVAL, -- 間隔。interval データ型は、時間ベースのパーティションテーブルに使用します。
partition_data BOOLEAN DEFAULT TRUE) -- プライマリテーブルからパーティションにデータをすぐに移行するかどうかを指定します。これは推奨されません。代わりに非ブロッキング移行関数 partition_table_concurrently() を使用してください。例：
パーティション化するプライマリテーブルを作成し、テストデータを挿入します。
--- パーティション化するプライマリテーブルを作成 CREATE TABLE part_test(id int, info text, crt_time timestamp not null); -- パーティションキー列には NOT NULL 制約が必要です --- プライマリテーブルに既にデータが含まれている状態をシミュレートするためにテストデータを挿入 INSERT INTO part_test SELECT id,md5(random()::text),clock_timestamp() + (id||' hour')::interval from generate_series(1,10000) t(id);プライマリテーブルのデータをクエリします：
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 行)パーティションを作成します。各パーティションには 1 か月分のデータが含まれます。
--- パーティションを作成します。各パーティションには 1 か月分のデータが含まれます。 SELECT create_range_partitions('part_test'::regclass, -- プライマリテーブルの OID 'crt_time', -- パーティションキー列の名前 '2016-10-25 00:00:00'::timestamp, -- 開始値 interval '1 month', -- 間隔。interval データ型は、時間ベースのパーティションテーブルに使用します。 24, -- 作成するパーティションの数 false) ; -- データを移行しない非ブロッキング移行関数を使用して、プライマリテーブルからデータを移行します。
--- データ移行前は、データはまだプライマリテーブルにあります SELECT count(*) FROM ONLY part_test; count ------- 10000 (1 行) --- 非ブロッキング移行関数 partition_table_concurrently(relation REGCLASS, -- プライマリテーブルの OID batch_size INTEGER DEFAULT 1000, -- 1 つのトランザクションバッチで移行するレコード数 sleep_time FLOAT8 DEFAULT 1.0) -- 行ロックの取得に失敗した場合に再試行するまでのスリープ時間。タスクは 60 回の再試行後に終了します。 --- 非ブロッキング移行関数を使用してプライマリテーブルからデータを移行 SELECT partition_table_concurrently('part_test'::regclass, 10000, 1.0); --- 移行後、プライマリテーブルは空になります。すべてのデータがパーティションに移動しています。 SELECT count(*) FROM ONLY part_test; count ------- 0 (1 行)データ移行が完了したら、プライマリテーブルを無効にして、実行計画に表示されないようにします。
--- プライマリテーブルを無効化 SELECT set_enable_parent('part_test'::regclass, false); --- 検証 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 行)
説明
レンジパーティションテーブルを使用する場合は、次のベストプラクティスに従ってください：
パーティションキー列には NOT NULL 制約が必要です。
パーティションの数は、既存のすべてのレコードをカバーできる数にしてください。
非ブロッキング移行関数を使用してください。
データ移行が完了したら、プライマリテーブルを無効にしてください。
ハッシュパーティショニング
管理関数を使用すると、次のようにパーティション数を指定してハッシュパーティションを作成できます：
create_hash_partitions(relation REGCLASS, -- プライマリテーブルの OID
attribute TEXT, -- パーティションキー列の名前
partitions_count INTEGER, -- 作成するパーティションの数
partition_data BOOLEAN DEFAULT TRUE) -- プライマリテーブルからパーティションにデータをすぐに移行するかどうかを指定します。これは推奨されません。代わりに非ブロッキング移行関数 partition_table_concurrently() を使用してください。例：
パーティション化するプライマリテーブルを作成し、テストデータを挿入します。
--- パーティション化するプライマリテーブルを作成 CREATE TABLE part_test(id int, info text, crt_time timestamp not null); -- パーティションキー列には NOT NULL 制約が必要です --- プライマリテーブルに既にデータが含まれている状態をシミュレートするためにテストデータを挿入 INSERT INTO part_test SELECT id,md5(random()::text),clock_timestamp() + (id||' hour')::interval FROM generate_series(1,10000) t(id);プライマリテーブルのデータをクエリします：
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 行)パーティションを作成します。
--- 128個のパーティションを作成 SELECT create_hash_partitions('part_test'::regclass, -- プライマリテーブルの OID 'crt_time', -- パーティションキー列の名前 128, -- 作成するパーティションの数 false) ; -- データを移行しない非ブロッキング移行関数を使用して、プライマリテーブルからデータを移行します。
--- データ移行前は、データはまだプライマリテーブルにあります SELECT count(*) FROM ONLY part_test; count ------- 10000 (1 行) --- 非ブロッキング移行関数 partition_table_concurrently(relation REGCLASS, -- プライマリテーブルの OID batch_size INTEGER DEFAULT 1000, -- 1 つのトランザクションバッチで移行するレコード数 sleep_time FLOAT8 DEFAULT 1.0) -- 行ロックの取得に失敗した場合に再試行するまでのスリープ時間。タスクは 60 回の再試行後に終了します。 --- 非ブロッキング移行関数を使用してプライマリテーブルからデータを移行 SELECT partition_table_concurrently('part_test'::regclass, 10000, 1.0); --- 移行後、プライマリテーブルは空になります。すべてのデータがパーティションに移動しています。 SELECT count(*) FROM ONLY part_test; count ------- 0 (1 行)データ移行が完了したら、プライマリテーブルを無効にして、実行計画に表示されないようにします。
--- プライマリテーブルを無効化 SELECT set_enable_parent('part_test'::regclass, false);実行計画を検証します：
--- 単一パーティションのクエリ 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 行)パーティションテーブルに対する次の制約は、pg_pathman が自動的に変換を実行することを示しています。対照的に、従来の継承では、
SELECT * FROM part_test WHERE crt_time = '2016-10-25 00:00:00'::timestamp;のようなステートメントに対してパーティションプルーニングを実行できません。\d+ part_test_122 Table "public.part_test_122" Column | Type | Modifiers | Storage | Stats target | Description ----------+-----------------------------+-----------+----------+--------------+------------- id | integer | | plain | | info | text | | extended | | crt_time | timestamp without time zone | not null | plain | | Check constraints: "pathman_part_test_122_3_check" CHECK (get_hash_part_idx(timestamp_hash(crt_time), 128) = 122) Inherits: part_test
説明
ハッシュパーティションテーブルを使用する場合は、次のベストプラクティスに従ってください：
パーティションキー列には NOT NULL 制約が必要です。
非ブロッキング移行関数を使用してください。
データ移行が完了したら、プライマリテーブルを無効にしてください。
pg_pathman は式の形式に制限されません。したがって、
select * from part_test where crt_time = '2016-10-25 00:00:00'::timestamp;のようなステートメントは、ハッシュパーティショニングにも使用できます。ハッシュパーティショニングのパーティションキー列は int データ型に限定されません。ハッシュ関数が自動変換に使用されます。
パーティションへのデータ移行
パーティションテーブルの作成時にプライマリテーブルからデータを移行しなかった場合は、非ブロッキング移行関数を使用してデータを移行できます。
WITH tmp AS (DELETE FROM primary_table limit xx nowait returning *) INSERT INTO partition SELECT * FROM tmp;または、次のステートメントを使用して行をマークし、DELETE および INSERT 操作を実行することもできます。
SELECT array_agg(ctid) FROM primary_table limit xx FOR UPDATE nowait;この関数は次のように定義されます：
partition_table_concurrently(relation REGCLASS, -- プライマリテーブルの OID
batch_size INTEGER DEFAULT 1000, -- 1 つのトランザクションバッチで移行するレコード数
sleep_time FLOAT8 DEFAULT 1.0) -- 行ロックの取得に失敗した場合に再試行するまでのスリープ時間。タスクは 60 回の再試行後に終了します。例：
SELECT partition_table_concurrently('part_test'::regclass,
10000,
1.0);バックグラウンドのデータ移行タスクを表示できます。
SELECT * FROM pathman_concurrent_part_tasks;レンジパーティションの分割
パーティションが大きくなりすぎた場合は、2 つのパーティションに分割できます。この機能は現在、レンジパーティションテーブルでのみ使用できます。次の関数を使用できます：
split_range_partition(partition REGCLASS, -- パーティションの OID
split_value ANYELEMENT, -- 分割値
partition_name TEXT DEFAULT NULL) -- 分割後に作成される新しいパーティションテーブルの名前例：
レンジパーティショニングの例のパーティションテーブルを使用します。テーブルスキーマは次のとおりです。
\d+ part_test Table "public.part_test" Column | Type | Modifiers | Storage | Stats target | Description ----------+-----------------------------+-----------+----------+--------------+------------- id | integer | | plain | | info | text | | extended | | crt_time | timestamp without time zone | not null | plain | | Child tables: part_test_1, part_test_10, part_test_11, part_test_12, part_test_13, part_test_14, part_test_15, part_test_16, part_test_17, part_test_18, part_test_19, part_test_2, part_test_20, part_test_21, part_test_22, part_test_23, part_test_24, part_test_3, part_test_4, part_test_5, part_test_6, part_test_7, part_test_8, part_test_9 \d+ part_test_1 Table "public.part_test_1" Column | Type | Modifiers | Storage | Stats target | Description ----------+-----------------------------+-----------+----------+--------------+------------- id | integer | | plain | | info | text | | extended | | crt_time | timestamp without time zone | not null | plain | | Check constraints: "pathman_part_test_1_3_check" CHECK (crt_time >= '2016-10-25 00:00:00'::timestamp without time zone AND crt_time < '2016-11-25 00:00:00'::timestamp without time zone) Inherits: part_testパーティションを分割します。
SELECT split_range_partition('part_test_1'::regclass, -- パーティションの OID '2016-11-10 00:00:00'::timestamp, -- 分割値 'part_test_1_2'); -- パーティションテーブルの名前分割後の 2 つのテーブルは次のとおりです：
\d+ part_test_1 Table "public.part_test_1" Column | Type | Modifiers | Storage | Stats target | Description ----------+-----------------------------+-----------+----------+--------------+------------- id | integer | | plain | | info | text | | extended | | crt_time | timestamp without time zone | not null | plain | | Check constraints: "pathman_part_test_1_3_check" CHECK (crt_time >= '2016-10-25 00:00:00'::timestamp without time zone AND crt_time < '2016-11-10 00:00:00'::timestamp without time zone) Inherits: part_test \d+ part_test_1_2 Table "public.part_test_1_2" Column | Type | Modifiers | Storage | Stats target | Description ----------+-----------------------------+-----------+----------+--------------+------------- id | integer | | plain | | info | text | | extended | | crt_time | timestamp without time zone | not null | plain | | Check constraints: "pathman_part_test_1_2_3_check" CHECK (crt_time >= '2016-11-10 00:00:00'::timestamp without time zone AND crt_time < '2016-11-25 00:00:00'::timestamp without time zone) Inherits: part_testデータは自動的に新しいパーティションに移行されます。
SELECT count(*) FROM part_test_1; count ------- 373 (1 行) SELECT count(*) FROM part_test_1_2; count ------- 360 (1 行)継承関係は次のとおりです：
\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, -- 新しいテーブル 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
レンジパーティションのマージ
この機能は現在、レンジパーティショニングでのみ使用でき、パーティションは隣接している必要があります。次の関数を呼び出すことができます：
--- マージする 2 つのパーティションを指定
merge_range_partitions(partition1 REGCLASS, partition2 REGCLASS)次に例を示します：
レンジパーティションの分割の例のテーブルを使用して、マージ操作を実行します。
SELECT merge_range_partitions('part_test_1'::regclass, 'part_test_1_2'::regclass);説明隣接していないパーティションをマージしようとすると、エラーが報告されます。
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マージ後、元のパーティションの 1 つが削除されます。
\d part_test_1_2 Did not find any relation named "part_test_1_2". \d part_test_1 Table "public.part_test_1" Column | Type | Modifiers ----------+-----------------------------+----------- id | integer | info | text | crt_time | timestamp without time zone | not null Check constraints: "pathman_part_test_1_3_check" CHECK (crt_time >= '2016-10-25 00:00:00'::timestamp without time zone AND crt_time < '2016-11-25 00:00:00'::timestamp without time zone) Inherits: part_test SELECT count(*) FROM part_test_1; count ------- 733 (1 行)
レンジパーティションの追加
プライマリテーブルが既にパーティション化されている場合、いくつかの方法で新しいパーティションを追加できます。このセクションでは、レンジパーティションの追加、レンジパーティションの先頭追加、指定した開始値でのレンジパーティションの追加の 3 つの方法について説明します。
レンジパーティションの追加
レンジパーティションを追加 (末尾にパーティションを追加) する場合、パーティションテーブルの初期作成時に指定された間隔が使用されます。以下に示すように、pathman_config ビューをクエリして、各パーティションテーブルの初期間隔を確認できます：
SELECT * FROM pathman_config;
partrel | attname | parttype | range_interval
-----------+----------+----------+----------------
part_test | crt_time | 2 | 1 mon
(1 行)パーティションを追加する関数は次のとおりです：
append_range_partition(parent REGCLASS, -- プライマリテーブルの OID
partition_name TEXT DEFAULT NULL, -- 新しいパーティションテーブルの名前。これはオプションです。
tablespace TEXT DEFAULT NULL) -- 新しいパーティションテーブルのテーブルスペース。これはオプションです。次に例を示します：
SELECT append_range_partition('part_test'::regclass);
\d+ part_test_25
Table "public.part_test_25"
Column | Type | Modifiers | Storage | Stats target | Description
----------+-----------------------------+-----------+----------+--------------+-------------
id | integer | | plain | |
info | text | | extended | |
crt_time | timestamp without time zone | not null | plain | |
Check constraints:
"pathman_part_test_25_3_check" CHECK (crt_time >= '2018-10-25 00:00:00'::timestamp without time zone AND crt_time < '2018-11-25 00:00:00'::timestamp without time zone)
Inherits: part_test
\d+ part_test_24
Table "public.part_test_24"
Column | Type | Modifiers | Storage | Stats target | Description
----------+-----------------------------+-----------+----------+--------------+-------------
id | integer | | plain | |
info | text | | extended | |
crt_time | timestamp without time zone | not null | plain | |
Check constraints:
"pathman_part_test_24_3_check" CHECK (crt_time >= '2018-09-25 00:00:00'::timestamp without time zone AND crt_time < '2018-10-25 00:00:00'::timestamp without time zone)
Inherits: part_testレンジパーティションの先頭追加
レンジパーティションを先頭に追加するには、次の関数を使用します：
prepend_range_partition(parent REGCLASS,
partition_name TEXT DEFAULT NULL,
tablespace TEXT DEFAULT NULL)例：
SELECT prepend_range_partition('part_test'::regclass);
\d+ part_test_26
Table "public.part_test_26"
Column | Type | Modifiers | Storage | Stats target | Description
----------+-----------------------------+-----------+----------+--------------+-------------
id | integer | | plain | |
info | text | | extended | |
crt_time | timestamp without time zone | not null | plain | |
Check constraints:
"pathman_part_test_26_3_check" CHECK (crt_time >= '2016-09-25 00:00:00'::timestamp without time zone AND crt_time < '2016-10-25 00:00:00'::timestamp without time zone)
Inherits: part_test
\d+ part_test_1
Table "public.part_test_1"
Column | Type | Modifiers | Storage | Stats target | Description
----------+-----------------------------+-----------+----------+--------------+-------------
id | integer | | plain | |
info | text | | extended | |
crt_time | timestamp without time zone | not null | plain | |
Check constraints:
"pathman_part_test_1_3_check" CHECK (crt_time >= '2016-10-25 00:00:00'::timestamp without time zone AND crt_time < '2016-11-25 00:00:00'::timestamp without time zone)
Inherits: part_test指定した開始値でのパーティション追加
開始値と終了値を指定して、レンジパーティションを追加できます。パーティションは、既存のパーティションと重複しない場合に作成できます。この方法では、連続したパーティションを作成する必要はありません。たとえば、既存のパーティションが 2010 年から 2015 年の範囲をカバーしている場合、2015 年から 2020 年の範囲をカバーせずに、2020 年のパーティションを直接作成できます。関数は次のとおりです：
add_range_partition(relation REGCLASS, -- プライマリテーブルの OID
start_value ANYELEMENT, -- 開始値
end_value ANYELEMENT, -- 終了値
partition_name TEXT DEFAULT NULL, -- パーティションの名前
tablespace TEXT DEFAULT NULL) -- パーティションが作成されるテーブルスペース例：
SELECT add_range_partition('part_test'::regclass, -- プライマリテーブルの OID
'2020-01-01 00:00:00'::timestamp, -- 開始値
'2020-02-01 00:00:00'::timestamp); -- 終了値
\d+ part_test_27
Table "public.part_test_27"
Column | Type | Modifiers | Storage | Stats target | Description
----------+-----------------------------+-----------+----------+--------------+-------------
id | integer | | plain | |
info | text | | extended | |
crt_time | timestamp without time zone | not null | plain | |
Check constraints:
"pathman_part_test_27_3_check" CHECK (crt_time >= '2020-01-01 00:00:00'::timestamp without time zone AND crt_time < '2020-02-01 00:00:00'::timestamp without time zone)
Inherits: part_testパーティションの削除
単一のレンジパーティションを削除するには、次の関数を使用します：
delete_data が true の場合は、RANGE パーティションとそのすべてのデータが削除されます。drop_range_partition(partition TEXT, -- パーティションの名前 delete_data BOOLEAN DEFAULT TRUE) -- パーティションデータを削除するかどうかを指定します。false の場合、データはプライマリテーブルに移行されます。すべてのパーティションを削除し、データをプライマリテーブルに移行するかどうかを指定するには、次の関数を使用します：
delete_data が false の場合、データは最初に親テーブルにコピーされます。 デフォルト値は false です。drop_partitions(parent REGCLASS, delete_data BOOLEAN DEFAULT FALSE) 親テーブルのパーティション (外部リレーションとローカルリレーションの両方) を削除します。
例：
パーティションを削除し、そのデータをプライマリテーブルに移行します。
SELECT drop_range_partition('part_test_1',false); SELECT drop_range_partition('part_test_2',false);プライマリテーブルの現在のデータ件数を確認します：
SELECT count(*) FROM part_test; count ------- 10000 (1 行)パーティションとそのデータを削除します。データはプライマリテーブルに移行されません。
SELECT drop_range_partition('part_test_3',true);プライマリテーブルの現在のデータ件数を確認します：
SELECT count(*) FROM part_test; count ------- 9256 (1 行) SELECT count(*) FROM ONLY part_test; count ------- 1453 (1 行)すべてのパーティションを削除します。
SELECT drop_partitions('part_test'::regclass, false); -- すべてのパーティションテーブルを削除し、データをプライマリテーブルに移行プライマリテーブルのデータをクエリします：
SELECT count(*) FROM part_test; count ------- 9256 (1 行)
パーティションのアタッチ
既存のテーブルをパーティション化されたプライマリテーブルにアタッチできます。既存のテーブルは、削除された列を含め、プライマリテーブルと同じスキーマを持っている必要があります。整合性は pg_attribute ビューで確認できます。関数は次のとおりです：
attach_range_partition(relation REGCLASS, -- プライマリテーブルの OID
partition REGCLASS, -- パーティションテーブルの OID
start_value ANYELEMENT, -- 開始値
end_value ANYELEMENT) -- 終了値次に例を示します：
パーティションとして使用するテーブルを作成します。
CREATE TABLE part_test_1 (like part_test including all);既存のテーブルをプライマリテーブルにアタッチします。
\d+ part_test Table "public.part_test" Column | Type | Modifiers | Storage | Stats target | Description ----------+-----------------------------+-----------+----------+--------------+------------- id | integer | | plain | | info | text | | extended | | crt_time | timestamp without time zone | not null | plain | | \d+ part_test_1 Table "public.part_test_1" Column | Type | Modifiers | Storage | Stats target | Description ----------+-----------------------------+-----------+----------+--------------+------------- id | integer | | plain | | info | text | | extended | | crt_time | timestamp without time zone | not null | plain | | SELECT attach_range_partition('part_test'::regclass, 'part_test_1'::regclass, '2019-01-01 00:00:00'::timestamp, '2019-02-01 00:00:00'::timestamp);パーティションをアタッチすると、継承関係と制約が自動的に作成されます。
\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) -- 標準テーブルに変換するパーティション名を指定次に例を示します：
プライマリテーブルとパーティションの現在のデータ件数を確認します。
SELECT count(*) FROM part_test; count ------- 9256 (1 行) SELECT count(*) FROM part_test_2; count ------- 733 (1 行)パーティションをデタッチします。
SELECT detach_range_partition('part_test_2');プライマリテーブルとパーティションの現在のデータ件数を確認します。
SELECT count(*) FROM part_test_2; count ------- 733 (1 行) SELECT count(*) FROM part_test; count ------- 8523 (1 行)
pg_pathman の無効化
単一のパーティション化されたプライマリテーブルに対して pg_pathman を無効にできます。関数は次のとおりです：
重要
disable_pathman_for 操作は元に戻せません。注意して使用してください。
\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);
/* 変更をバックエンドに通知 */
PERFORM public.on_remove_partitions(parent_relid);
END
$function$以下に例を示します：
SELECT disable_pathman_for('part_test');
\d+ part_test
Table "public.part_test"
Column | Type | Modifiers | Storage | Stats target | Description
----------+-----------------------------+-----------+----------+--------------+-------------
id | integer | | plain | |
info | text | | extended | |
crt_time | timestamp without time zone | not null | plain | |
Child tables: part_test_10,
part_test_11,
part_test_12,
part_test_13,
part_test_14,
part_test_15,
part_test_16,
part_test_17,
part_test_18,
part_test_19,
part_test_20,
part_test_21,
part_test_22,
part_test_23,
part_test_24,
part_test_25,
part_test_26,
part_test_27,
part_test_28,
part_test_29,
part_test_3,
part_test_30,
part_test_31,
part_test_32,
part_test_33,
part_test_34,
part_test_35,
part_test_4,
part_test_5,
part_test_6,
part_test_7,
part_test_8,
part_test_9
\d+ part_test_10
Table "public.part_test_10"
Column | Type | Modifiers | Storage | Stats target | Description
----------+-----------------------------+-----------+----------+--------------+-------------
id | integer | | plain | |
info | text | | extended | |
crt_time | timestamp without time zone | not null | plain | |
Check constraints:
"pathman_part_test_10_3_check" CHECK (crt_time >= '2017-06-25 00:00:00'::timestamp without time zone AND crt_time < '2017-07-25 00:00:00'::timestamp without time zone)
Inherits: part_testpg_pathman 拡張機能を無効にした後も、継承関係と制約は変更されません。唯一の違いは、pg_pathman 拡張機能が実行計画でカスタムスキャンを提供しなくなることです。拡張機能を無効にした後の実行計画は次のとおりです：
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 行)高度なパーティション管理
親テーブルの無効化
親テーブルからパーティションにすべてのデータが移行された後、親テーブルを無効化できます。関数は次のとおりです。
set_enable_parent(relation REGCLASS, value BOOLEAN)
親テーブルをクエリプランに含めるか、除外するかを設定します。
PostgreSQL の標準プランナーでは、親テーブルが空であっても常にクエリプランに含まれるため、追加のオーバーヘッドが発生する可能性があります。
親テーブルをストレージとして使用しない場合は、set_enable_parent() を使用して親テーブルを無効にできます。
デフォルト値は、create_range_partitions() または create_partitions_from_range() 関数での初期パーティショニング時に指定された partition_data パラメーターによって決まります。
partition_data パラメーターが true の場合、すべてのデータはすでにパーティションに移行され、親テーブルは無効化されています。
それ以外の場合は有効化されています。例：
SELECT set_enable_parent('part_test', false);パーティションの自動拡張
レンジパーティションテーブルでは、自動パーティション作成を有効にできます。新しく挿入されるデータが既存のパーティションの範囲に含まれない場合、新しいパーティションが自動的に作成されます。
set_auto(relation REGCLASS, value BOOLEAN)
自動パーティション作成を有効または無効にします (レンジパーティショニングのみ)。
デフォルトで有効になっています。例：
1. テストテーブルの現在のパーティションを確認します。
\d+ part_test Table "public.part_test" Column | Type | Modifiers | Storage | Stats target | Description ----------+-----------------------------+-----------+----------+--------------+------------- id | integer | | plain | | info | text | | extended | | crt_time | timestamp without time zone | not null | plain | | Child tables: part_test_10, part_test_11, part_test_12, part_test_13, part_test_14, part_test_15, part_test_16, part_test_17, part_test_18, part_test_19, part_test_20, part_test_21, part_test_22, part_test_23, part_test_24, part_test_25, part_test_26, part_test_3, part_test_4, part_test_5, part_test_6, part_test_7, part_test_8, part_test_9 \d+ part_test_26 Table "public.part_test_26" Column | Type | Modifiers | Storage | Stats target | Description ----------+-----------------------------+-----------+----------+--------------+------------- id | integer | | plain | | info | text | | extended | | crt_time | timestamp without time zone | not null | plain | | Check constraints: "pathman_part_test_26_3_check" CHECK (crt_time >= '2018-09-25 00:00:00'::timestamp without time zone AND crt_time < '2018-10-25 00:00:00'::timestamp without time zone) Inherits: part_test \d+ part_test_25 Table "public.part_test_25" Column | Type | Modifiers | Storage | Stats target | Description ----------+-----------------------------+-----------+----------+--------------+------------- id | integer | | plain | | info | text | | extended | | crt_time | timestamp without time zone | not null | plain | | Check constraints: "pathman_part_test_25_3_check" CHECK (crt_time >= '2018-08-25 00:00:00'::timestamp without time zone AND crt_time < '2018-09-25 00:00:00'::timestamp without time zone) Inherits: part_test2. 既存のパーティションの範囲外の値を挿入します。この操作により、初期設定時に指定された間隔に基づいて、必要な数のパーティションが自動的に作成されます。この操作には時間がかかる場合があることに注意してください。
INSERT INTO part_test VALUES (1,'test','2222-01-01'::timestamp);現在のパーティションの状態を確認できます。
\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, ......
説明
レンジパーティションテーブルでは、自動パーティション作成を有効にしないことを推奨します。多数のパーティションを自動的に作成すると、非常に時間がかかる可能性があります。
コールバック関数
コールバック関数は、パーティションが作成されるたびに自動的にトリガーされる関数です。たとえば、DDL 論理レプリケーションで DDL ステートメントをテーブルに記録するために、コールバック関数を使用できます。コールバックを設定する関数は次のとおりです。
set_init_callback(relation REGCLASS, callback REGPROC DEFAULT 0)
アタッチまたは作成される各パーティション (ハッシュとレンジの両方) に対して呼び出されるパーティション作成コールバックを設定します。
コールバックは次のシグネチャを持つ必要があります。
part_init_callback(args JSONB) RETURNS VOID。
パラメーター arg は、パーティショニングタイプに応じた複数のフィールドで構成されます。
/* レンジパーティションテーブル abc (子テーブル abc_4) */
{
"parent": "abc",
"parttype": "2",
"partition": "abc_4",
"range_max": "401",
"range_min": "301"
}
/* ハッシュパーティションテーブル abc (子テーブル abc_0) */
{
"parent": "abc",
"parttype": "1",
"partition": "abc_0"
}例：
1. コールバック関数を作成します。
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;2. テストテーブルを準備します。
CREATE TABLE tt(id int, info text, crt_time timestamp not null); --- テストテーブルにコールバック関数を設定 SELECT set_init_callback('tt'::regclass, 'f_callback_test'::regproc); --- パーティションを作成 SELECT create_range_partitions('tt'::regclass, -- 親テーブルの OID 'crt_time', -- パーティションキー列の名前 '2016-10-25 00:00:00'::timestamp, -- 開始値 interval '1 month', -- 間隔。時間ベースのパーティションには interval データ型を使用します。 24, -- 作成するパーティション数 false) ;3. コールバック関数が呼び出されたかどうかを確認します。
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)