【PostgreSQL 11 新機能】デフォルトパーティションを検証してみた

こんにちは。サイオステクノロジー OSS サポート担当 Y です。

今回は PostgreSQL 11 (現時点ではまだ beta 版) の新機能である “デフォルトパーティション” を検証してみました。(※以下の内容は CentOS 7.5/PostgreSQL 10.5/PostgreSQL 11beta3 にて検証しています。)

■はじめに

昨年リリースされた PostgreSQL 10 の新機能である Declarative Partitioning には、いわゆる “デフォルトパーティション” の機能が存在していないのですが、PostgreSQL 11 ではこの “デフォルトパーティション” が実装される予定となっています。

今回は PostgreSQL 11beta3 を使って、このデフォルトパーティションを試してみようと思います。

■検証 (PostgreSQL 10 の動作確認)

まずは PostgreSQL 10 の動作を確認してみます。(以下の例では LIST パーティションを使用しています)

postgres=# CREATE TABLE hoge (list_key int, data text) PARTITION BY LIST (list_key);
CREATE TABLE
postgres=# CREATE TABLE hoge_child_1 PARTITION OF hoge FOR VALUES IN (1);
CREATE TABLE
postgres=# CREATE TABLE hoge_child_2 PARTITION OF hoge FOR VALUES IN (2);
CREATE TABLE
postgres=# CREATE TABLE hoge_child_3 PARTITION OF hoge FOR VALUES IN (3);
CREATE TABLE
postgres=# 
postgres=# \d+ hoge
                                     Table "public.hoge"
  Column  |  Type   | Collation | Nullable | Default | Storage  | Stats target | Description 
----------+---------+-----------+----------+---------+----------+--------------+-------------
 list_key | integer |           |          |         | plain    |              | 
 data     | text    |           |          |         | extended |              | 
Partition key: LIST (list_key)
Partitions: hoge_child_1 FOR VALUES IN (1),
            hoge_child_2 FOR VALUES IN (2),
            hoge_child_3 FOR VALUES IN (3)

上記の様にパーティショニングされたテーブルにて、”条件の範囲内” の値 (上記例の場合 “1”, “2”, “3”) を INSERT すると以下の様に正常に各子テーブルにレコードが振り分けられます。

postgres=# INSERT INTO hoge VALUES (1, 'hogehoge'), (2, 'hogehoge'), (3, 'hogehoge');
INSERT 0 3
postgres=# 
postgres=# SELECT * FROM hoge;
 list_key |   data   
----------+----------
        1 | hogehoge
        2 | hogehoge
        3 | hogehoge
(3 rows)

postgres=# 
postgres=# SELECT * FROM hoge_child_1;
 list_key |   data   
----------+----------
        1 | hogehoge
(1 row)

postgres=# 
postgres=# SELECT * FROM hoge_child_2;
 list_key |   data   
----------+----------
        2 | hogehoge
(1 row)

postgres=# 
postgres=# SELECT * FROM hoge_child_3;
 list_key |   data   
----------+----------
        3 | hogehoge
(1 row)

しかし、PostgreSQL 10 で “条件の範囲外” の値 (上記例の場合 “1”, “2”, “3” 以外の値) を INSERT しようとすると、以下の様に ERROR になってしまいます。

postgres=# INSERT INTO hoge VALUES (4, 'hogehoge');
ERROR:  no partition of relation "hoge" found for row
DETAIL:  Partition key of the failing row contains (list_key) = (4).

■検証 (PostgreSQL 11 の動作確認)

それでは、PostgreSQL 11beta3 でデフォルトパーティションを試してみます。

まずは、前述した PostgreSQL 10 の検証と同じテーブルを作成します。

postgres=# CREATE TABLE hoge (list_key int, data text) PARTITION BY LIST (list_key);
CREATE TABLE
postgres=# CREATE TABLE hoge_child_1 PARTITION OF hoge FOR VALUES IN (1);
CREATE TABLE
postgres=# CREATE TABLE hoge_child_2 PARTITION OF hoge FOR VALUES IN (2);
CREATE TABLE
postgres=# CREATE TABLE hoge_child_3 PARTITION OF hoge FOR VALUES IN (3);
CREATE TABLE
postgres=# 
postgres=# \d+ hoge
                                     Table "public.hoge"
  Column  |  Type   | Collation | Nullable | Default | Storage  | Stats target | Description 
----------+---------+-----------+----------+---------+----------+--------------+-------------
 list_key | integer |           |          |         | plain    |              | 
 data     | text    |           |          |         | extended |              | 
Partition key: LIST (list_key)
Partitions: hoge_child_1 FOR VALUES IN (1),
            hoge_child_2 FOR VALUES IN (2),
            hoge_child_3 FOR VALUES IN (3)

この状態で振り分け条件として設定していない値を INSERT すると PostgreSQL 10 と同じ様に ERROR になります。

postgres=# INSERT INTO hoge VALUES (4, 'hogehoge');
ERROR:  no partition of relation "hoge" found for row
DETAIL:  Partition key of the failing row contains (list_key) = (4).

しかし、PostgreSQL 11 ではデフォルトパーティションを作成することができるため、テーブル “hoge” にデフォルトパーティションを追加してみます。デフォルトパーティションを作成する場合は、CREATE TABLE 文にて “DEFAULT” を指定します。

postgres=# CREATE TABLE hoge_child_default PARTITION OF hoge DEFAULT;
CREATE TABLE
postgres=# 
postgres=# \d+ hoge
                                     Table "public.hoge"
  Column  |  Type   | Collation | Nullable | Default | Storage  | Stats target | Description 
----------+---------+-----------+----------+---------+----------+--------------+-------------
 list_key | integer |           |          |         | plain    |              | 
 data     | text    |           |          |         | extended |              | 
Partition key: LIST (list_key)
Partitions: hoge_child_1 FOR VALUES IN (1),
            hoge_child_2 FOR VALUES IN (2),
            hoge_child_3 FOR VALUES IN (3),
            hoge_child_default DEFAULT

上記の様にデフォルトパーティションを追加した後に、改めて振り分け条件として設定していない値を INSERT すると、今度は ERROR にならずにデータを INSERT することができます。

postgres=# INSERT INTO hoge VALUES (4, 'hogehoge');
INSERT 0 1
postgres=# 
postgres=# SELECT * FROM hoge;
 list_key |   data   
----------+----------
        4 | hogehoge
(1 row)

この状態で子テーブル “hoge_child_default” を確認してみると、list_key に “4” を指定したレコードが格納されていることが確認できます。

postgres=# SELECT * FROM hoge_child_default;
 list_key |   data   
----------+----------
        4 | hogehoge
(1 row)

この様に、PostgreSQL 11 ではデフォルトパーティションを設定できるので、振り分け条件に合致しないレコードを INSERT した場合でも ERROR にならずにレコードを格納することができます。

■最後に

業務上規定された範囲外の値を INSERT しようとした場合には ERROR を返した方が良い等、デフォルトパーティションが不要な場合もあるとは思いますが、PostgreSQL 10 ではパーティションキーの値をアプリ側で厳密に管理する必要があったので、デフォルトパーティションが有用な場面も多いのではないでしょうか。

PostgreSQL 11 ではパーティション関連の機能を含め様々な機能が強化されているので、次回も PostgreSQL 11 の新機能の検証を実施してみようと思います。