【PostgreSQL 11 新機能】パーティションキーを UPDATE した際の動作を検証してみた

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

今回は PostgreSQL 11 (現時点ではまだ beta 版) にて強化された “パーティションキーを UPDATE した際の動作” を検証してみました。(※以下の内容は CentOS 7.5/PostgreSQL 10.5/PostgreSQL 11beta3 にて検証しています。)

1 ■はじめに
2 ■検証 (PostgreSQL 10 の動作確認)
3 ■検証 (PostgreSQL 11 の動作確認)
4 ■最後に

■はじめに

昨年リリースされた PostgreSQL 10 の新機能である Declarative Partitioning では、一度特定の子テーブルに格納されたレコードの “パーティションキー” の値を、異なる子テーブルの条件に合致する値に更新しようとすると ERROR になり、子テーブル間でのデータ移動等は実施されない仕様になっていました。

しかし、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)

上記テーブルに対して、まず list_key = 1 のレコードを INSERT します。すると、パーティショニングされたテーブル “hoge” にレコードが INSERT され、実際には子テーブル “hoge_child_1” にレコードが格納されていることが確認できます。

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

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

postgres=# 
postgres=# SELECT * FROM hoge_child_2;
 list_key | data 
----------+------
(0 rows)

postgres=# 
postgres=# SELECT * FROM hoge_child_3;
 list_key | data 
----------+------
(0 rows)

この状態で list_key の値を “2” に UPDATE しようとすると、以下の通り ERROR となってしまいます。これが PostgreSQL 10 の宣言的パーティショニングの仕様動作でした。

postgres=# UPDATE hoge SET list_key = 2 WHERE data = 'hogehoge';
ERROR:  new row for relation "hoge_child_1" violates partition constraint
DETAIL:  Failing row contains (2, hogehoge).

■検証 (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)

前述した PostgreSQL 10 の検証と同様に、list_key = 1 のレコードを INSERT し、子テーブル “hoge_child_1” にレコードが格納されていることを確認します。

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

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

postgres=# 
postgres=# SELECT * FROM hoge_child_2;
 list_key | data 
----------+------
(0 rows)

postgres=# 
postgres=# SELECT * FROM hoge_child_3;
 list_key | data 
----------+------
(0 rows)

この状態で list_key の値を “2” に UPDATE してみます。すると、PostgreSQL 10 の様に ERROR にはならずに、正常に UPDATE が実行されます。

postgres=# UPDATE hoge SET list_key = 2 WHERE data = 'hogehoge';
UPDATE 1

上記 UPDATE 実行後に各テーブルの内容を参照してみると、list_key の値を “2” に UPDATE した “data = ‘hogehoge'” のレコードが、”hoge_child_1″ から “hoge_child_2” に移動していることが確認できます。

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

postgres=# 
postgres=# SELECT * FROM hoge_child_1;
 list_key | data 
----------+------
(0 rows)

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

postgres=# 
postgres=# SELECT * FROM hoge_child_3;
 list_key | data 
----------+------
(0 rows)

この様に、PostgreSQL 11 ではパーティションキーを UPDATE した際に適切な子テーブル (UPDATE 後の値が条件に合致する子テーブル) にレコードが移動されるようになっていることが確認できました。

■最後に

今回検証を実施した通り、PostgreSQL 11 ではパーティションキーを UPDATE した際に適切な子テーブルにレコードが移動されるため、パーティションキーを更新する可能性があるようなテーブルを作成することも可能になりました。PostgreSQL 11 では、その他にもパーティションに関連する複数の機能が強化されているため、PostgreSQL におけるパーティショニングがどんどん便利になっている様に感じます。

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

ご覧いただきありがとうございます！この投稿はお役に立ちましたか？

役に立った役に立たなかった

1人がこの投稿は役に立ったと言っています。