はじめに
にゃーん。
今回は、PostgreSQL 11で追加されたパーティション関係の細かい改善点を紹介してみる。
PostgreSQL 11のパーティション関連の改善事項
前回紹介したハッシュパーティションは、PostgreSQL 11のパーティション改善の目玉の一つだが、それ以外にもいろいろな改善がされている。
- クエリ処理中にパーティションを素早く削除する
- クエリの実行中にパーティションの削除を許可する
- キーをハッシュすることに基づいてパーティションを作成できるようにする→これは前回紹介したハッシュパーティションのこと。
- 更新された行を新しい行の内容に基づいて新しいパーティションに自動的に移動させる ⇒今日のネタ
- パーティション化された表にデフォルトのパーティションを許可する
- パーティションキーが一意性を保証する場合は、パーティションテーブルのユニークインデックスを許可する
- パーティション化されたテーブルのインデックスを任意の子パーティションに自動的に作成できるようにする→これも前回の記事でちょっと触れた。
- パーティション表に外部キーを許可する
- パーティション化された表に対してINSERT、UPDATEおよびCOPYを許可して、行を外部のパーティションに適切にルーティングする
- パーティション化された表に対してFOR EACH ROWトリガーを許可する
- 同一のパーティション化された子テーブルを持つパーティション化されたテーブル間の等価結合を許可し、子テーブルを直接結合する
- 各パーティションで集約を実行し、結果をマージする
- postgres_fdwがパーティションである外部表に集約をプッシュダウンできるようにする
あらためて見てみると大量の改善がされているなあ・・・。
パーティション間UPDATE
今回は、いろいろなパーティションの改善項目のうち、わかりやすそうな、パーティション間UPDATEについて検証してみた。
PostgreSQL 10まで
例えば、PostgreSQL 10上で、以下のようなリストパーティションが定義されているとする。
test=# \d+ japan
Table "public.japan"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+------+-----------+----------+---------+----------+--------------+-------------
pref | text | | | | extended | |
city | text | | | | extended | |
data | text | | | | extended | |
Partition key: LIST (pref)
Partitions: kanagawa FOR VALUES IN ('神奈川'),
shizuoka FOR VALUES IN ('静岡'),
tokyo FOR VALUES IN ('東京')
japanテーブル(とその子パーティションテーブル)には、こんなデータがロードされている。
test=# TABLE japan;
pref | city | data
--------+--------+-------------------------------
東京 | 町田市 | 蒙古タンメン中本 町田店
東京 | 港区 | ニュータンタンメン本舗 品達店
東京 | 八丈町 | 蓮華
神奈川 | 横浜市 | ラーメン二郎 関内店
神奈川 | 川崎市 | 蒙古タンメン中本 川崎店
神奈川 | 横浜市 | 吉村家
静岡 | 熱海市 | 雨風本舗
静岡 | 下田市 | 一品香
(8 rows)
町田市の都道府県が間違っているので、UPDATE文で更新しようとするが・・・
test=# UPDATE japan SET pref = '神奈川' WHERE city = '町田市';
ERROR: new row for relation "tokyo" violates partition constraint
DETAIL: Failing row contains (神奈川, 町田市, 蒙古タンメン中本 町田店).
test=#
都民の反対によりtokyo子テーブルの制約違反という理由でUPDATEはエラーになってしまう。このようにPostgreSQL 10では、パーティションキーをUPDATE文で変更することはできず、同様の更新結果となるように、同一トランザクション内で、DELETE文とINSERT文を組み合わせたり、WITH句＆RETURNING句を使ってDELETEとINSERTを実行する必要があった。
test=# WITH tmp AS
(DELETE FROM japan WHERE city = '町田市' RETURNING '神奈川', city, data)
INSERT INTO japan SELECT * FROM tmp;
INSERT 0 1
test=# TABLE japan;
pref | city | data
--------+--------+-------------------------------
東京 | 港区 | ニュータンタンメン本舗 品達店
東京 | 八丈町 | 蓮華
神奈川 | 横浜市 | ラーメン二郎 関内店
神奈川 | 川崎市 | 蒙古タンメン中本 川崎店
神奈川 | 横浜市 | 吉村家
神奈川 | 町田市 | 蒙古タンメン中本 町田店
静岡 | 熱海市 | 雨風本舗
静岡 | 下田市 | 一品香
(8 rows)
PostgreSQL 11から
PostgreSQL 11からは、パーティションキーを変更するUPDATE文でも、更新ができるようになった。
PostgreSQL 10の例で示したパーティション構成を作成しとデータを登録しておく。
test=# \d+ japan
Table "public.japan"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+------+-----------+----------+---------+----------+--------------+-------------
pref | text | | | | extended | |
city | text | | | | extended | |
data | text | | | | extended | |
Partition key: LIST (pref)
Partitions: kanagawa FOR VALUES IN ('神奈川'),
shizuoka FOR VALUES IN ('静岡'),
tokyo FOR VALUES IN ('東京')
test=# TABLE japan;
pref | city | data
--------+--------+-------------------------------
東京 | 町田市 | 蒙古タンメン中本 町田店
東京 | 港区 | ニュータンタンメン本舗 品達店
東京 | 八丈町 | 蓮華
神奈川 | 横浜市 | ラーメン二郎 関内店
神奈川 | 川崎市 | 蒙古タンメン中本 川崎店
神奈川 | 横浜市 | 吉村家
静岡 | 熱海市 | 雨風本舗
静岡 | 下田市 | 一品香
(8 rows)
この状態でUPDATEで更新してみよう。
test=# UPDATE japan SET pref = '神奈川' WHERE city = '町田市';
UPDATE 1
test=# TABLE japan;
pref | city | data
--------+--------+-------------------------------
東京 | 港区 | ニュータンタンメン本舗 品達店
東京 | 八丈町 | 蓮華
神奈川 | 横浜市 | ラーメン二郎 関内店
神奈川 | 川崎市 | 蒙古タンメン中本 川崎店
神奈川 | 横浜市 | 吉村家
神奈川 | 町田市 | 蒙古タンメン中本 町田店
静岡 | 熱海市 | 雨風本舗
静岡 | 下田市 | 一品香
(8 rows)
無事にUPDATE文で更新できました。やっぱり町田は神奈川ですよね。
しかし、どういう仕掛けなんだろうねえ。
仕掛け
まあ、仕掛けというほど大袈裟なものではなく、パーティションキーをUPDATE文で更新しようとする場合、内部的にDELETE⇒INSERTに書き換えて実行しているようだ。
ただ、パラメータlog_statement = 'all'とかを設定して、サーバログに出力してもログ上はフツーにUPDATE文が出力される。
2018-06-02 10:08:05.568 JST [3906] LOG: statement: UPDATE japan SET pref = '神奈川' WHERE city = '町田市';
じゃあ、どうやってDELETE⇒INSERTしていると言えるのかというと、更新ログ(WAL)を見てみればいい。
PostgreSQL 9.4から導入されたロジカル・デコーディングでWALを可視化してみる。そのために、contrib/test_decodingというモジュールを使う。
contribモジュールをビルド＆インストール(あるいはcontribパッケージをRPMやyumでインストール)して、パラメータwal_level = 'logical'に設定して起動する。
起動後に、ロジカルなWALへ変換するプラグイン(test_decoding)を設定したnuko_slotというレプリケーションスロットを作成する。
test=# SELECT * FROM pg_create_logical_replication_slot('nuko_slot', 'test_decoding');
slot_name | lsn
-----------+------------
nuko_slot | 0/14E31570
(1 row)
この状態で、パーティションキーの変更がないUPDATE文と、パーティションキーが変更されるパーティションキーの(test_decodingで可視化された)WALを比較する。WALの取り出しにはpg_logical_slot_get_changes()というSQL関数を使う。
まず、パーティションキーを変更しないUPDATE文を実行して、その直後にpg_logical_slot_get_changes()関数を実行して、論理WAL内容を参照する。
test=# UPDATE japan SET data = 'しおらーめん進化 町田駅前店' WHERE city = '町田市';
UPDATE 1
test=# SELECT * FROM pg_logical_slot_get_changes('nuko_slot', NULL, NULL);
lsn | xid | data
------------+-----+-----------------------------------------------------------------------------
------------------------------------
0/14E31650 | 650 | BEGIN 650
0/14E31650 | 650 | table public.kanagawa: UPDATE: pref[text]:'神奈川' city[text]:'町田市' data[
text]:'しおらーめん進化 町田駅前店'
0/14E318B0 | 650 | COMMIT 650
(3 rows)
このときの論理WALの内容は、kanagawaテーブルへのUPDATEが実行されていることがわかる。
では、次にパーティションキーを変更するUPDATE文（prefを東京に変更する）を実行して、その直後の論理WALを参照してみる。
test=# UPDATE japan SET pref = '東京' WHERE city = '町田市';
UPDATE 1
test=# SELECT * FROM pg_logical_slot_get_changes('nuko_slot', NULL, NULL);
lsn | xid | data
------------+-----+-----------------------------------------------------------------------------
-------------------------------
0/14E31980 | 652 | BEGIN 652
0/14E31980 | 652 | table public.kanagawa: DELETE: (no-tuple-data)
0/14E319B8 | 652 | table public.tokyo: INSERT: pref[text]:'東京' city[text]:'町田市' data[text]
:'しおらーめん進化 町田駅前店'
0/14E31BB8 | 652 | COMMIT 652
(4 rows)
まず、kanagawaテーブルに対してDELETEが実行され、そのあとにtokyoテーブルへのINSERTが実行されているのがわかる。
このように、UPDATE実行時の状況によって、自動的にUPDATE文をDELETE⇒INSERT相当に書き換えているのだ。やるじゃん！
おわりに
- PostgreSQL 10で課題となっていた、パーティションをまたがるUPDATEがPostgreSQL 11では対応された。いいね。
- UPDATEからDELETE/INSERTへの書き換えは、test_decodingモジュールで確認できる。
- 町田は神奈川