はじめに
にゃーん
今回は、PostgreSQL 17develの「パーティションの分割＆マージ」機能を簡単に調べてみた。
従来のPostgreSQLパーティーション
PostgreSQL 10からDDL（CREATE TABLE, ALTER TABLE）でテーブルパーティションが可能になったが、PostgreSQL 16までは、既存パーティションの分割や、パーティションのマージはDDLとしてはサポートされていなかった。
Oracle等の商用データベースでは対応している機能であり、OracleからPostgreSQL移行時の長年の問題になっていた。
PostgreSQL 17での機能追加
PostgreSQL 17では、ALTER TABLEコマンドに、パーティションの分割を行うオプションと、マージを行うオプションが追加される。
この機能に関する開発時の議論は、以下のCommitdestページから参照できる。
Add SPLIT PARTITION/MERGE PARTITIONS commands
コマンドの構文
PostgreSQL 17ではALTER TABLEに以下のオプションが追加される。
ALTER TABLE [ IF EXISTS ] name
SPLIT PARTITION partition_name INTO
(PARTITION partition_name1 { FOR VALUES partition_bound_spec | DEFAULT },
PARTITION partition_name2 { FOR VALUES partition_bound_spec | DEFAULT } [, ...])
SPLIT PARTITIONはレンジパーティションおよびリストパーティションのみに対応している。ハッシュパーティションには使えない。
partition_bound_specは、CREATE TABLEコマンドと同じものだろう。
ALTER TABLE [ IF EXISTS ] name
MERGE PARTITIONS (partition_name1, partition_name2 [, ...])
INTO partition_name
MERGE PARTITIONはシンプルにパーティション名を列挙するものになっている。
SPLIT PARTITIONと同様に、MERGE PARTITIONはレンジパーティションおよびリストパーティションのみに対応している。ハッシュパーティションには使えない。
動作検証
今回は、レンジパーティションで、分割/マージする例を示す。
検証バージョン
5/5にコミットされた(commit 713cfaf2a576a9896fdd9b5aad51f6ebeb91a3c7)を使って今回は検証した。
検証パターン
- date型のパーティションキーを持つ親テーブルを定義する。
- 子パーティションを2023年の四半期単位で4つのパーティション（test_1q, test_2q, test_3q, test_4q)に分割する。（2023年以外の日付はtest_defaultパーティションにしておく）
- DDLは参考：パーティション定義とデータ挿入例を参照。
- 適当にデータを挿入して、全パーティションに値を格納しておく。
- この状態で、第1四半期（1月～3月）のパーティション
test_1qを月単位の3つのパーティション（test_01m, test_02m, test_03m)に分割する。 - 分割後、今度は1月～3月のパーティションを第1四半期のパーティション（test_1q）にマージする。
検証前の状態
testテーブルとパーティションテーブルの定義
$ psql test -c "\d+ test"
Null display is "(null)".
Partitioned table "public.test"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
----------+---------+-----------+----------+---------+----------+-------------+--------------+-------------
id | integer | | | | plain | | |
part_key | date | | | | plain | | |
data | text | | | | extended | | |
Partition key: RANGE (part_key)
Partitions: test_1q FOR VALUES FROM ('2024-01-01') TO ('2024-03-31'),
test_2q FOR VALUES FROM ('2024-04-01') TO ('2024-06-30'),
test_3q FOR VALUES FROM ('2024-07-01') TO ('2024-09-30'),
test_4q FOR VALUES FROM ('2024-10-01') TO ('2024-12-31'),
test_default DEFAULT
$
testテーブルの内容
$ psql test -c "TABLE test ORDER BY part_key"
Null display is "(null)".
id | part_key | data
----+------------+--------------------------------
9 | 2023-12-31 | ゆく年
1 | 2024-01-03 | なんで正月すぐ終わっちゃうん？
2 | 2024-01-31 | 月末事務処理だるい
3 | 2024-02-03 | 節分
4 | 2024-03-31 | 年度〆の地獄
5 | 2024-04-27 | 連休だ、うおおお！
6 | 2024-05-06 | なんで連休すぐ終わっちゃうん？
7 | 2024-06-09 | にゃーん（age++）
8 | 2024-06-30 | 何の成果もえられませんでした
10 | 2025-01-01 | あけおめ
(10 rows)
$
パーティションの分割
ALTER TABLEコマンドのSPLIT PARTITIONを使って、2024年第1四半期のパーティション（test_1q）を月別の3つのパーティション（test_01m, test_02m, test_03m）に分割する例を示す。
$ psql test -e -f split.sql
Null display is "(null)".
ALTER TABLE test SPLIT PARTITION test_1q INTO
(
PARTITION test_01m FOR VALUES FROM ('2024-01-01') TO ('2024-02-01'),
PARTITION test_02m FOR VALUES FROM ('2024-02-01') TO ('2024-03-01'),
PARTITION test_03m FOR VALUES FROM ('2024-03-01') TO ('2024-04-01')
);
ALTER TABLE
$
分割後に、psqlの\d+メタコマンドでテーブル定義を再確認する。
$ psql test -c "\d+ test"
Null display is "(null)".
Partitioned table "public.test"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
----------+---------+-----------+----------+---------+----------+-------------+--------------+-------------
id | integer | | | | plain | | |
part_key | date | | | | plain | | |
data | text | | | | extended | | |
Partition key: RANGE (part_key)
Partitions: test_01m FOR VALUES FROM ('2024-01-01') TO ('2024-02-01'),
test_02m FOR VALUES FROM ('2024-02-01') TO ('2024-03-01'),
test_03m FOR VALUES FROM ('2024-03-01') TO ('2024-04-01'),
test_2q FOR VALUES FROM ('2024-04-01') TO ('2024-07-01'),
test_3q FOR VALUES FROM ('2024-07-01') TO ('2024-10-01'),
test_4q FOR VALUES FROM ('2024-10-01') TO ('2025-01-01'),
test_default DEFAULT
$
test_1qパーティションが削除され、代わりにtest_01m,test_02m,test_03mパーティションが追加されている。
test_01mパーティション内を参照すると1月のデータのみ格納されていることがわかる。
$ psql test -c "TABLE test_01m ORDER BY part_key"
Null display is "(null)".
id | part_key | data
----+------------+--------------------------------
1 | 2024-01-03 | なんで正月すぐ終わっちゃうん？
2 | 2024-01-31 | 月末事務処理だるい
(2 rows)
$
パーティションのマージ
ALTER TABLEコマンドのMERGE PARTITIONSを使って、さっき月単位で分割したtest_01m,test_02m,test_03mのテーブルをtest_1qという四半期単位のパーティションに再度マージする。
psql test -e -f merge.sql
Null display is "(null)".
ALTER TABLE test MERGE PARTITIONS
(
test_01m, test_02m, test_03m
)
INTO test_1q;
ALTER TABLE
$
マージされたか確認する。
$ psql test -e -f merge.sql$ psql test -c "\d+ test"
Null display is "(null)".
Partitioned table "public.test"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
----------+---------+-----------+----------+---------+----------+-------------+--------------+-------------
id | integer | | | | plain | | |
part_key | date | | | | plain | | |
data | text | | | | extended | | |
Partition key: RANGE (part_key)
Partitions: test_1q FOR VALUES FROM ('2024-01-01') TO ('2024-04-01'),
test_2q FOR VALUES FROM ('2024-04-01') TO ('2024-07-01'),
test_3q FOR VALUES FROM ('2024-07-01') TO ('2024-10-01'),
test_4q FOR VALUES FROM ('2024-10-01') TO ('2025-01-01'),
test_default DEFAULT
$ psql test -c "TABLE test_1q ORDER BY part_key"
Null display is "(null)".
id | part_key | data
----+------------+--------------------------------
1 | 2024-01-03 | なんで正月すぐ終わっちゃうん？
2 | 2024-01-31 | 月末事務処理だるい
3 | 2024-02-03 | 節分
4 | 2024-03-31 | 年度〆の地獄
(4 rows)
$
デフォルトパーティションからの分割/デフォルトパーティションへのマージ
デフォルトパーティションをSPLIT PARTITIONで分割することもできる。
例えば、デフォルトパーティションtest_defaultに2023年のデータと2025年のデータが入っている状態
$ psql test -c "\d+ test";
Null display is "(null)".
Partitioned table "public.test"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
----------+---------+-----------+----------+---------+----------+-------------+--------------+-------------
id | integer | | | | plain | | |
part_key | date | | | | plain | | |
data | text | | | | extended | | |
Partition key: RANGE (part_key)
Partitions: test_1q FOR VALUES FROM ('2024-01-01') TO ('2024-04-01'),
test_2q FOR VALUES FROM ('2024-04-01') TO ('2024-07-01'),
test_3q FOR VALUES FROM ('2024-07-01') TO ('2024-10-01'),
test_4q FOR VALUES FROM ('2024-10-01') TO ('2025-01-01'),
test_default DEFAULT
$ psql test -c "TABLE test_default";
Null display is "(null)".
id | part_key | data
----+------------+----------
11 | 2023-12-31 | ゆく年
12 | 2025-01-01 | あけおめ
(2 rows)
$
から、2023年用のパーティションtest_2023と2025年用パーティションtest_2025に分割する例を示す。
$ psql test -e -f split_from_default.sql
Null display is "(null)".
ALTER TABLE test SPLIT PARTITION test_default INTO
(
PARTITION test_2023 FOR VALUES FROM ('2023-01-01') TO ('2024-01-01'),
PARTITION test_2025 FOR VALUES FROM ('2025-01-01') TO ('2026-01-01'),
PARTITION test_default DEFAULT
);
ALTER TABLE
$
SPLIT PARTITIONに分割元となるデフォルトパーティション名を、分割先のパーティションリストにPARTITION test_default DEFAULTとして指定するところがポイント。
なお、SPLIT PARTITIONのときに、デフォルトパーティションを分割先に指定しないと、以下のように怒られる。
$ psql test -e -f split_from_default_err.sql
Null display is "(null)".
ALTER TABLE test SPLIT PARTITION test_default INTO
(
PARTITION test_2023 FOR VALUES FROM ('2023-01-01') TO ('2024-01-01'),
PARTITION test_2025 FOR VALUES FROM ('2025-01-01') TO ('2026-01-01')
);
psql:split_from_default_err.sql:5: ERROR: one partition in the list should be DEFAULT because split partition is DEFAULT
LINE 3: PARTITION test_2023 FOR VALUES FROM ('2023-01-01') TO ('...
^
$
デフォルトパーティションへのマージも同様に行う。
$ psql test -e -f merge_default.sql
Null display is "(null)".
ALTER TABLE test MERGE PARTITIONS
(
test_2023, test_2025, test_default
)
INTO test_default;
ALTER TABLE
$
これもMERGE SPLIT先とMERGE対象のリストのどちらにも、デフォルトパーティション名を指定するのがポイント。
おわりに
今回はレンジ/リストパーティションの分割とマージを行う、ALTER TABLEコマンドの改善について調査してみた。
この機能の追加により、パーティションの運用がより柔軟にできることが期待できるし、パーテイションを使っているOracle案件からPostgreSQLへの移行もちょっと楽になるかもしれない。
参考：パーティション定義とデータ挿入例
本文の説明で端折った各パーティションの定義を示す。
パーティションテーブルの定義
CREATE TABLE test
(id int, part_key date, data text)
PARTITION BY RANGE (part_key);
CREATE TABLE test_1q
PARTITION OF test (id primary key)
FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');
CREATE TABLE test_2q
PARTITION OF test (id primary key)
FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');
CREATE TABLE test_3q
PARTITION OF test (id primary key)
FOR VALUES FROM ('2024-07-01') TO ('2024-10-01');
CREATE TABLE test_4q
PARTITION OF test (id primary key)
FOR VALUES FROM ('2024-10-01') TO ('2025-01-01');
CREATE TABLE test_default
PARTITION OF test DEFAULT;
データ挿入例
INSERT INTO test VALUES
(1, '2024-01-03', 'なんで正月すぐ終わっちゃうん？'),
(2, '2024-01-31', '月末事務処理だるい'),
(3, '2024-02-03', '節分'),
(4, '2024-03-31', '年度〆の地獄'),
(5, '2024-04-01', '2024年度開始'),
(6, '2024-04-27', '連休だ、うおおお！'),
(7, '2024-05-06', 'なんで連休すぐ終わっちゃうん？'),
(8, '2024-06-09', 'にゃーん（age++）'),
(9, '2024-06-30', '何の成果もえられませんでした'),
(10, '2024-07-01', '夏が来る～'),
(11, '2023-12-31', 'ゆく年'),
(12, '2025-01-01', 'あけおめ')
;