【PostgreSQL 11】外部テーブル (postgres_fdw) を利用したパーティションテーブルを作成してみた (その2)

◆ Live配信スケジュール ◆
サイオステクノロジーでは、Microsoft MVPの武井による「わかりみの深いシリーズ」など、定期的なLive配信を行っています。
⇒ 詳細スケジュールはこちらから
⇒ 見逃してしまった方はYoutubeチャンネルをご覧ください
【3/22開催】テックブログを書こう!アウトプットのススメ
1年で100本ブログを出した新米エンジニアがPV数が伸びなくてもTech Blogを書き続ける理由とは?
https://tech-lab.connpass.com/event/312805/

【4/18開催】VSCode Dev Containersで楽々開発環境構築祭り〜Python/Reactなどなど〜
Visual Studio Codeの拡張機能であるDev Containersを使ってReactとかPythonとかSpring Bootとかの開発環境をラクチンで構築する方法を紹介するイベントです。
https://tech-lab.connpass.com/event/311864/

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

今回は、PostgreSQL 11 にて postgres_fdw (外部テーブル) を利用したパーティションテーブルと、ローカルで作成した (外部テーブルを利用せず、同一サーバ上で作成した) パーティションテーブルの動作を検証してみました。(※以下の内容は CentOS 7.6/PostgreSQL 10.6/PostgreSQL 11.1 にて検証しています。)

■はじめに

前回の記事では、PostgreSQL 11 にて postgres_fdw (外部テーブル) を利用したパーティションテーブルの作成と、親テーブルからのデータの INSERT を検証してみました。

今回は、postgres_fdw (外部テーブル) を利用したパーティションテーブルと、ローカルで作成した (外部テーブルを利用せず、同一サーバ上で作成した) パーティションテーブルで UPDATE を実行した際の動作の違いについて検証してみました。

■環境構成

それではさっそく、検証してみます。

今回は以下の様な構成の 2つのパーティションテーブル (RANGE パーティション) を作成します。(外部テーブルを利用したパーティションテーブルの作成については、前回の記事を参照して下さい。)

[サーバ構成 (外部テーブルを利用)]
----------------------------------------------------------------------
                                               +--> [pgsql11_child1.example.com]
[pgsql11_parent.example.com] --(postgres_fdw)--+--> [pgsql11_child2.example.com]
                                               +--> [pgsql11_child3.example.com]
----------------------------------------------------------------------

[テーブル構成 (外部テーブルを利用)]
----------------------------------------------------------------------
                                             +--> [子テーブル (foreign_hoge_1)]
[親テーブル (foreign_hoge)] --(postgres_fdw)--+--> [子テーブル (foreign_hoge_2)]
                                             +--> [子テーブル (foreign_hoge_3)]
----------------------------------------------------------------------
[サーバ構成 (同一サーバ上に各テーブルを作成)]
----------------------------------------------------------------------
                               +--> [pgsql11_parent.example.com]
[pgsql11_parent.example.com] --+--> [pgsql11_parent.example.com]
                               +--> [pgsql11_parent.example.com]
----------------------------------------------------------------------


[テーブル構成 (同一サーバ上に各テーブルを作成)]
----------------------------------------------------------------------
                           +--> [子テーブル (local_hoge_1)]
[親テーブル (local_hoge)] --+--> [子テーブル (local_hoge_2)]
                           +--> [子テーブル (local_hoge_3)]
----------------------------------------------------------------------

■ローカルでのパーティションテーブル作成

外部テーブルを利用したパーティションテーブルは前回の記事で作成したものをそのまま利用するため、今回はローカル (同一サーバ上) でのパーティションテーブルを追加で作成します。

以下の様に、親テーブル/子テーブルを作成します。

[postgres@pgsql11_parent ~]$ psql -c "CREATE TABLE local_hoge (key int, data text) PARTITION BY RANGE (key)"
CREATE TABLE
[postgres@pgsql11_parent ~]$ 
[postgres@pgsql11_parent ~]$ psql -c "CREATE TABLE local_hoge_1 PARTITION OF local_hoge FOR VALUES FROM (1) TO (4)"
CREATE TABLE
[postgres@pgsql11_parent ~]$ 
[postgres@pgsql11_parent ~]$ psql -c "CREATE TABLE local_hoge_2 PARTITION OF local_hoge FOR VALUES FROM (4) TO (7)"
CREATE TABLE
[postgres@pgsql11_parent ~]$ 
[postgres@pgsql11_parent ~]$ psql -c "CREATE TABLE local_hoge_3 PARTITION OF local_hoge FOR VALUES FROM (7) TO (10)"
CREATE TABLE
[postgres@pgsql11_parent ~]$ 
[postgres@pgsql11_parent ~]$ psql -c "\d+ local_hoge"
                                 Table "public.local_hoge"
 Column |  Type   | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+---------+-----------+----------+---------+----------+--------------+-------------
 key    | integer |           |          |         | plain    |              | 
 data   | text    |           |          |         | extended |              | 
Partition key: RANGE (key)
Partitions: local_hoge_1 FOR VALUES FROM (1) TO (4),
            local_hoge_2 FOR VALUES FROM (4) TO (7),
            local_hoge_3 FOR VALUES FROM (7) TO (10)

これで、ローカルでのパーティションテーブルの作成は完了です。

■データの INSERT

さて、現在検証用のサーバ (PostgreSQL 11) 上には、前述した方法で作成したパーティションテーブル (ローカルに作成) と、前回作成したパーティションテーブル (外部テーブルを利用) が存在しています。(基本的に、テーブルの定義は同じです。)

postgres=# \d
                 List of relations
 Schema |      Name      |     Type      |  Owner   
--------+----------------+---------------+----------
 public | foreign_hoge   | table         | postgres
 public | foreign_hoge_1 | foreign table | postgres
 public | foreign_hoge_2 | foreign table | postgres
 public | foreign_hoge_3 | foreign table | postgres
 public | local_hoge     | table         | postgres
 public | local_hoge_1   | table         | postgres
 public | local_hoge_2   | table         | postgres
 public | local_hoge_3   | table         | postgres
(8 rows)

postgres=# 
postgres=# \d+ foreign_hoge
                                Table "public.foreign_hoge"
 Column |  Type   | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+---------+-----------+----------+---------+----------+--------------+-------------
 key    | integer |           |          |         | plain    |              | 
 data   | text    |           |          |         | extended |              | 
Partition key: RANGE (key)
Partitions: foreign_hoge_1 FOR VALUES FROM (1) TO (4),
            foreign_hoge_2 FOR VALUES FROM (4) TO (7),
            foreign_hoge_3 FOR VALUES FROM (7) TO (10)

postgres=# 
postgres=# \d+ local_hoge  
                                 Table "public.local_hoge"
 Column |  Type   | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+---------+-----------+----------+---------+----------+--------------+-------------
 key    | integer |           |          |         | plain    |              | 
 data   | text    |           |          |         | extended |              | 
Partition key: RANGE (key)
Partitions: local_hoge_1 FOR VALUES FROM (1) TO (4),
            local_hoge_2 FOR VALUES FROM (4) TO (7),
            local_hoge_3 FOR VALUES FROM (7) TO (10)

これらのテーブルに対して、それぞれテスト用のデータを INSERT します。

postgres=# INSERT INTO foreign_hoge SELECT a, md5(clock_timestamp()::text) FROM generate_series(1,9) AS a;
INSERT 0 9
postgres=# 
postgres=# SELECT * FROM foreign_hoge;
 key |               data               
-----+----------------------------------
   1 | f4a623ee2ad94ef7132b929e2d31ff81
   2 | 0a234b4c35d32fd8583bd578d5bc2446
   3 | a385f544a0e4442100f60194a94b45a7
   4 | 76bd4050760e236fe641f423d65ebb66
   5 | 7103e14f72db14d0715475acfa4ff16f
   6 | a844c1c49c5296b7b0989121a46cebbc
   7 | 6d17cfaa36065b62f30532d0c6a84009
   8 | c080bac4339edeefad0a3e92b5f86fec
   9 | 93262647cc75b6901d44369d0de4373e
(9 rows)

postgres=# 
postgres=# SELECT * FROM foreign_hoge_1;
 key |               data               
-----+----------------------------------
   1 | f4a623ee2ad94ef7132b929e2d31ff81
   2 | 0a234b4c35d32fd8583bd578d5bc2446
   3 | a385f544a0e4442100f60194a94b45a7
(3 rows)

postgres=# 
postgres=# SELECT * FROM foreign_hoge_2;
 key |               data               
-----+----------------------------------
   4 | 76bd4050760e236fe641f423d65ebb66
   5 | 7103e14f72db14d0715475acfa4ff16f
   6 | a844c1c49c5296b7b0989121a46cebbc
(3 rows)

postgres=# 
postgres=# SELECT * FROM foreign_hoge_3;
 key |               data               
-----+----------------------------------
   7 | 6d17cfaa36065b62f30532d0c6a84009
   8 | c080bac4339edeefad0a3e92b5f86fec
   9 | 93262647cc75b6901d44369d0de4373e
(3 rows)
postgres=# INSERT INTO local_hoge SELECT a, md5(clock_timestamp()::text) FROM generate_series(1,9) AS a;
INSERT 0 9
postgres=# 
postgres=# SELECT * FROM local_hoge;
 key |               data               
-----+----------------------------------
   1 | 3859054d6ef644ee17995db1abd6184b
   2 | a7e902a628541d2db2248261b4ded233
   3 | 60048e7fd0b0607e2c510a84351ee5e7
   4 | dff554c90afed584239fab122f412168
   5 | a74107d25fe744e7b0b6e429294a1f78
   6 | 8a896920b5e331768cc17761eb49ab8d
   7 | 3c851f729dc6c384d708d9df4d9c16d4
   8 | 2a62a81f178ef15dad965eaf8ca0c211
   9 | a65ebec1f7b2f6d775aec593c4c70ba7
(9 rows)

postgres=# 
postgres=# SELECT * FROM local_hoge_1;
 key |               data               
-----+----------------------------------
   1 | 3859054d6ef644ee17995db1abd6184b
   2 | a7e902a628541d2db2248261b4ded233
   3 | 60048e7fd0b0607e2c510a84351ee5e7
(3 rows)

postgres=# 
postgres=# SELECT * FROM local_hoge_2;
 key |               data               
-----+----------------------------------
   4 | dff554c90afed584239fab122f412168
   5 | a74107d25fe744e7b0b6e429294a1f78
   6 | 8a896920b5e331768cc17761eb49ab8d
(3 rows)

postgres=# 
postgres=# SELECT * FROM local_hoge_3;
 key |               data               
-----+----------------------------------
   7 | 3c851f729dc6c384d708d9df4d9c16d4
   8 | 2a62a81f178ef15dad965eaf8ca0c211
   9 | a65ebec1f7b2f6d775aec593c4c70ba7
(3 rows)

上記の様に INSERT/SELECT に関しては、どちらのパーティションテーブルでも問題なく実施することができました。

次に、パーティションキー以外のカラム (data) の値を UPDATE してみます。

postgres=# UPDATE foreign_hoge SET data = 'foreign hogehoge' WHERE key = 2;
UPDATE 1
postgres=# 
postgres=# UPDATE foreign_hoge SET data = 'foreign hogehoge' WHERE key = 5;
UPDATE 1
postgres=# 
postgres=# UPDATE foreign_hoge SET data = 'foreign hogehoge' WHERE key = 8;
UPDATE 1
postgres=# 
postgres=# SELECT * FROM foreign_hoge ORDER BY key;
 key |               data               
-----+----------------------------------
   1 | f4a623ee2ad94ef7132b929e2d31ff81
   2 | foreign hogehoge
   3 | a385f544a0e4442100f60194a94b45a7
   4 | 76bd4050760e236fe641f423d65ebb66
   5 | foreign hogehoge
   6 | a844c1c49c5296b7b0989121a46cebbc
   7 | 6d17cfaa36065b62f30532d0c6a84009
   8 | foreign hogehoge
   9 | 93262647cc75b6901d44369d0de4373e
(9 rows)

postgres=# 
postgres=# SELECT * FROM foreign_hoge_1 ORDER BY key;
 key |               data               
-----+----------------------------------
   1 | f4a623ee2ad94ef7132b929e2d31ff81
   2 | foreign hogehoge
   3 | a385f544a0e4442100f60194a94b45a7
(3 rows)

postgres=# 
postgres=# SELECT * FROM foreign_hoge_2 ORDER BY key;
 key |               data               
-----+----------------------------------
   4 | 76bd4050760e236fe641f423d65ebb66
   5 | foreign hogehoge
   6 | a844c1c49c5296b7b0989121a46cebbc
(3 rows)

postgres=# 
postgres=# SELECT * FROM foreign_hoge_3 ORDER BY key;
 key |               data               
-----+----------------------------------
   7 | 6d17cfaa36065b62f30532d0c6a84009
   8 | foreign hogehoge
   9 | 93262647cc75b6901d44369d0de4373e
(3 rows)
postgres=# UPDATE local_hoge SET data = 'local hogehoge' WHERE key = 2;
UPDATE 1
postgres=# 
postgres=# UPDATE local_hoge SET data = 'local hogehoge' WHERE key = 5;
UPDATE 1
postgres=# 
postgres=# UPDATE local_hoge SET data = 'local hogehoge' WHERE key = 8;
UPDATE 1
postgres=# 
postgres=# SELECT * FROM local_hoge ORDER BY key;
 key |               data               
-----+----------------------------------
   1 | 3859054d6ef644ee17995db1abd6184b
   2 | local hogehoge
   3 | 60048e7fd0b0607e2c510a84351ee5e7
   4 | dff554c90afed584239fab122f412168
   5 | local hogehoge
   6 | 8a896920b5e331768cc17761eb49ab8d
   7 | 3c851f729dc6c384d708d9df4d9c16d4
   8 | local hogehoge
   9 | a65ebec1f7b2f6d775aec593c4c70ba7
(9 rows)

postgres=# 
postgres=# SELECT * FROM local_hoge_1 ORDER BY key;
 key |               data               
-----+----------------------------------
   1 | 3859054d6ef644ee17995db1abd6184b
   2 | local hogehoge
   3 | 60048e7fd0b0607e2c510a84351ee5e7
(3 rows)

postgres=# 
postgres=# SELECT * FROM local_hoge_2 ORDER BY key;
 key |               data               
-----+----------------------------------
   4 | dff554c90afed584239fab122f412168
   5 | local hogehoge
   6 | 8a896920b5e331768cc17761eb49ab8d
(3 rows)

postgres=# 
postgres=# SELECT * FROM local_hoge_3 ORDER BY key;
 key |               data               
-----+----------------------------------
   7 | 3c851f729dc6c384d708d9df4d9c16d4
   8 | local hogehoge
   9 | a65ebec1f7b2f6d775aec593c4c70ba7
(3 rows)

パーティションキー以外のカラム (data) の更新も、問題無く実行することができました。

今度は、パーティションキー (key) の値を更新してみます。(先に結論を言ってしまうと、このパーティションキーの値の更新時の動作に一部違いがあります。)

まずは、更新後のパーティションキーの値が、各子テーブルの条件の範囲内 (*_hoge_1 の場合 1 =< key < 4) に収まる様な値に更新してみます。

postgres=# UPDATE foreign_hoge SET key = 1 WHERE key = 3;^C
postgres=# 
postgres=# SELECT * FROM foreign_hoge_1;
 key |               data               
-----+----------------------------------
   1 | f4a623ee2ad94ef7132b929e2d31ff81
   3 | a385f544a0e4442100f60194a94b45a7
   2 | foreign hogehoge
(3 rows)

postgres=# 
postgres=# UPDATE foreign_hoge SET key = 1 WHERE key = 3;
UPDATE 1
postgres=# 
postgres=# SELECT * FROM foreign_hoge_1;
 key |               data               
-----+----------------------------------
   1 | f4a623ee2ad94ef7132b929e2d31ff81
   2 | foreign hogehoge
   1 | a385f544a0e4442100f60194a94b45a7
(3 rows)

postgres=# 
postgres=# SELECT * FROM foreign_hoge ORDER BY key;
 key |               data               
-----+----------------------------------
   1 | f4a623ee2ad94ef7132b929e2d31ff81
   1 | a385f544a0e4442100f60194a94b45a7
   2 | foreign hogehoge
   4 | 76bd4050760e236fe641f423d65ebb66
   5 | foreign hogehoge
   6 | a844c1c49c5296b7b0989121a46cebbc
   7 | 6d17cfaa36065b62f30532d0c6a84009
   8 | foreign hogehoge
   9 | 93262647cc75b6901d44369d0de4373e
(9 rows)
postgres=# SELECT * FROM local_hoge_1;
 key |               data               
-----+----------------------------------
   1 | 3859054d6ef644ee17995db1abd6184b
   3 | 60048e7fd0b0607e2c510a84351ee5e7
   2 | local hogehoge
(3 rows)

postgres=# 
postgres=# UPDATE local_hoge SET key = 1 WHERE key = 3;
UPDATE 1
postgres=# 
postgres=# SELECT * FROM local_hoge_1;
 key |               data               
-----+----------------------------------
   1 | 3859054d6ef644ee17995db1abd6184b
   2 | local hogehoge
   1 | 60048e7fd0b0607e2c510a84351ee5e7
(3 rows)

postgres=# 
postgres=# SELECT * FROM local_hoge ORDER BY key;
 key |               data               
-----+----------------------------------
   1 | 60048e7fd0b0607e2c510a84351ee5e7
   1 | 3859054d6ef644ee17995db1abd6184b
   2 | local hogehoge
   4 | dff554c90afed584239fab122f412168
   5 | local hogehoge
   6 | 8a896920b5e331768cc17761eb49ab8d
   7 | 3c851f729dc6c384d708d9df4d9c16d4
   8 | local hogehoge
   9 | a65ebec1f7b2f6d775aec593c4c70ba7
(9 rows)

更新後のパーティションキーの値が、各子テーブルの条件の範囲内 (*_hoge_1 の場合 1 =< key < 4) に収まる様な UPDATE は、どちらのパーティションテーブルでも問題なく実行することができました。

次に、更新後のパーティションキーの値が、異なる子テーブルの条件に合致する値になる様な更新を実行してみます。

最初に、外部テーブルを利用したパーティションテーブルにて、*_hoge_2 に格納されている key = 6 のレコードの key の値を “7” (*_hoge_3 の条件に合致する値) に更新します。

postgres=# SELECT * FROM foreign_hoge_2;
 key |               data               
-----+----------------------------------
   4 | 76bd4050760e236fe641f423d65ebb66
   6 | a844c1c49c5296b7b0989121a46cebbc
   5 | foreign hogehoge
(3 rows)

postgres=# 
postgres=# UPDATE foreign_hoge SET key = 7 WHERE key = 6;
UPDATE 1
postgres=# 
postgres=# SELECT * FROM foreign_hoge_2;
 key |               data               
-----+----------------------------------
   4 | 76bd4050760e236fe641f423d65ebb66
   5 | foreign hogehoge
   7 | a844c1c49c5296b7b0989121a46cebbc
(3 rows)

postgres=# 
postgres=# SELECT * FROM foreign_hoge_3;
 key |               data               
-----+----------------------------------
   7 | 6d17cfaa36065b62f30532d0c6a84009
   9 | 93262647cc75b6901d44369d0de4373e
   8 | foreign hogehoge
(3 rows)

postgres=# 
postgres=# SELECT * FROM foreign_hoge ORDER BY key;
 key |               data               
-----+----------------------------------
   1 | f4a623ee2ad94ef7132b929e2d31ff81
   1 | a385f544a0e4442100f60194a94b45a7
   2 | foreign hogehoge
   4 | 76bd4050760e236fe641f423d65ebb66
   5 | foreign hogehoge
   7 | a844c1c49c5296b7b0989121a46cebbc
   7 | 6d17cfaa36065b62f30532d0c6a84009
   8 | foreign hogehoge
   9 | 93262647cc75b6901d44369d0de4373e
(9 rows)

すると、key の値の UPDATE 自体は正常に実行されましたが、子テーブル間でのレコードの移動は行われず、子テーブル foreign_hoge_2 の中に条件 (4 <= key < 7) に合致しないレコード (key = 7) が格納されている状態になりました。

次に、ローカルのパーティションテーブル (外部テーブルを利用していないパーティションテーブル) にて、同じ様に *_hoge_2 に格納されている key = 6 のレコードの key の値を “7” (*_hoge_3 の条件に合致する値) に更新してみます。

postgres=# SELECT * FROM local_hoge_2;
 key |               data               
-----+----------------------------------
   4 | dff554c90afed584239fab122f412168
   6 | 8a896920b5e331768cc17761eb49ab8d
   5 | local hogehoge
(3 rows)

postgres=# 
postgres=# UPDATE local_hoge SET key = 7 WHERE key = 6;
UPDATE 1
postgres=# 
postgres=# SELECT * FROM local_hoge_2;
 key |               data               
-----+----------------------------------
   4 | dff554c90afed584239fab122f412168
   5 | local hogehoge
(2 rows)

postgres=# 
postgres=# SELECT * FROM local_hoge_3;
 key |               data               
-----+----------------------------------
   7 | 3c851f729dc6c384d708d9df4d9c16d4
   9 | a65ebec1f7b2f6d775aec593c4c70ba7
   8 | local hogehoge
   7 | 8a896920b5e331768cc17761eb49ab8d
(4 rows)

postgres=# 
postgres=# SELECT * FROM local_hoge ORDER BY key;
 key |               data               
-----+----------------------------------
   1 | 3859054d6ef644ee17995db1abd6184b
   1 | 60048e7fd0b0607e2c510a84351ee5e7
   2 | local hogehoge
   4 | dff554c90afed584239fab122f412168
   5 | local hogehoge
   7 | 3c851f729dc6c384d708d9df4d9c16d4
   7 | 8a896920b5e331768cc17761eb49ab8d
   8 | local hogehoge
   9 | a65ebec1f7b2f6d775aec593c4c70ba7
(9 rows)

すると今度は、子テーブル間でレコードが移動し、子テーブル local_hoge_2 の中から key = 6 だったレコード (data = ‘8a896920b5e331768cc17761eb49ab8d’ のレコード) が消え、local_hoge_3 に UPDATE 後の key = 7 のレコード (data = ‘8a896920b5e331768cc17761eb49ab8d’ のレコード) が格納されている状態になりました。

上記の様にパーティションキーの値を UPDATE した場合、ローカルのパーティションテーブルであれば、条件に合致する子テーブルに自動的にレコードを移動してくれます。

しかし、外部テーブルを利用したパーティションテーブルの場合、条件に合致する子テーブルへのレコードの移動は実施されません。

現時点 (PostgreSQL 11) における外部テーブルを利用したパーティションテーブルには、上記の様な制約があるようです。

私の個人的な見解ですが、外部テーブルを利用したパーティションテーブルの場合、複数ノードに跨る様なトランザクションを管理する (複数ノード間で原始性等を担保する) ための、2層 COMMIT のような仕組みがまだ実装されていないため、このような動作になっているのではないかと推測しています。

■おまけ

ちなみに、PostgreSQL 10 の場合はローカルのパーティションテーブルであっても、更新後のパーティションキーの値が、異なる子テーブルの条件に合致する様な更新を実行しようとすると、以下の様にエラーになってしまいます。

[postgres@pgsql10_parent ~]$ psql -c "CREATE TABLE local_hoge (key int, data text) PARTITION BY RANGE (key)"
CREATE TABLE
[postgres@pgsql10_parent ~]$ 
[postgres@pgsql10_parent ~]$ psql -c "CREATE TABLE local_hoge_1 PARTITION OF local_hoge FOR VALUES FROM (1) TO (4)"
CREATE TABLE
[postgres@pgsql10_parent ~]$ 
[postgres@pgsql10_parent ~]$ psql -c "CREATE TABLE local_hoge_2 PARTITION OF local_hoge FOR VALUES FROM (4) TO (7)"
CREATE TABLE
[postgres@pgsql10_parent ~]$ 
[postgres@pgsql10_parent ~]$ psql -c "CREATE TABLE local_hoge_3 PARTITION OF local_hoge FOR VALUES FROM (7) TO (10)"
CREATE TABLE
[postgres@pgsql10_parent ~]$ 
[postgres@pgsql10_parent ~]$ psql
psql (10.6)
Type "help" for help.

postgres=# 
postgres=# \d+ local_hoge
                                 Table "public.local_hoge"
 Column |  Type   | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+---------+-----------+----------+---------+----------+--------------+-------------
 key    | integer |           |          |         | plain    |              | 
 data   | text    |           |          |         | extended |              | 
Partition key: RANGE (key)
Partitions: local_hoge_1 FOR VALUES FROM (1) TO (4),
            local_hoge_2 FOR VALUES FROM (4) TO (7),
            local_hoge_3 FOR VALUES FROM (7) TO (10)

postgres=# 
postgres=# INSERT INTO local_hoge SELECT a, md5(clock_timestamp()::text) FROM generate_series(1,9) AS a;
INSERT 0 9
postgres=# 
postgres=# SELECT * FROM local_hoge;
 key |               data               
-----+----------------------------------
   1 | 89519f06939fe5d8109a89b0e81c2b2c
   2 | 4ca3212f5452aff3650c8e91af765c7e
   3 | f3c4dd1f3e8a2e8b9363f9baba5d4026
   4 | 7863ad08269ec451d25e0c9689dfdd3b
   5 | 63b2f56205847bb07d94ac7f7d320da6
   6 | e131864353b16e6c34e0c8f53a9c0706
   7 | 3d76e8d5891656cd97a0b9f2f3c5298a
   8 | 1e7235bba8505e740067f4141ed50a70
   9 | 0588257b0d37580295f5c391b0c6623b
(9 rows)

postgres=# 
postgres=# SELECT * FROM local_hoge_1;
 key |               data               
-----+----------------------------------
   1 | 89519f06939fe5d8109a89b0e81c2b2c
   2 | 4ca3212f5452aff3650c8e91af765c7e
   3 | f3c4dd1f3e8a2e8b9363f9baba5d4026
(3 rows)

postgres=# 
postgres=# SELECT * FROM local_hoge_2;
 key |               data               
-----+----------------------------------
   4 | 7863ad08269ec451d25e0c9689dfdd3b
   5 | 63b2f56205847bb07d94ac7f7d320da6
   6 | e131864353b16e6c34e0c8f53a9c0706
(3 rows)

postgres=# 
postgres=# SELECT * FROM local_hoge_3;
 key |               data               
-----+----------------------------------
   7 | 3d76e8d5891656cd97a0b9f2f3c5298a
   8 | 1e7235bba8505e740067f4141ed50a70
   9 | 0588257b0d37580295f5c391b0c6623b
(3 rows)

postgres=# 
postgres=# UPDATE local_hoge SET key = 7 WHERE key = 6;
ERROR:  new row for relation "local_hoge_2" violates partition constraint
DETAIL:  Failing row contains (7, e131864353b16e6c34e0c8f53a9c0706).

上記の通り、ローカルのパーティションテーブルにおいて、パーティションキーを更新した際の子テーブル間のレコードの移動は、PostgreSQL 11 から実装されている機能 (PostgreSQL 11 で強化された機能) になっています。

■最後に

今回は、PostgreSQL 11 にて外部テーブルを利用したパーティションテーブルと、ローカルで作成した (外部テーブルを利用せず、同一サーバ上で作成した) パーティションテーブルの動作を違いを検証してみました。

前述した通り、ローカルのパーティションテーブルにおけるパーティションキー更新時の子テーブル間のレコードの移動は、PostgreSQL 11 で実装 (強化) された機能となっており、バージョンが上がる毎に、少しずつ宣言的パーティショニングの機能が強化されています。

宣言的パーティショニングはユーザの期待も高く、注目されている機能であるため、今後のバージョン (PostgreSQL 12 以降) での更なる機能強化にも期待できるのではないでしょうか。

アバター画像
About サイオステクノロジーの中の人 41 Articles
サイオステクノロジーで働く中の人です。
ご覧いただきありがとうございます! この投稿はお役に立ちましたか?

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

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


ご覧いただきありがとうございます。
ブログの最新情報はSNSでも発信しております。
ぜひTwitterのフォロー&Facebookページにいいねをお願い致します!



>> 雑誌等の執筆依頼を受付しております。
   ご希望の方はお気軽にお問い合わせください!

Be the first to comment

Leave a Reply

Your email address will not be published.


*


質問はこちら 閉じる