PostgreSQL Built-in Sharding PostgreSQL Unconferecnce #42 (2023-07-03)

自己紹介 ● ぬこ＠横浜 , @nuko_yokohama ● にゃーん ● 趣味でポスグレをやってる者だ ● ねこなのでシャーディングむずかしいです

PostgreSQL シャーディング

シャーディングって何？ ● データの水平分散 ● 複数のノードにデータを分割して格納 ● 複数ノード化することで処理分散効果を見込める

PostgreSQL におけるシャーディング ● Citus – PostgreSQL 拡張機能で実装 ● Azure Cosmos DB for PostgreSQL – Citus ベースのマネージドサービス ● Built-in Sharding → 今日はこっちを話します

PostgreSQL Built-in Sharding ● パーティション＋ postgres_fdw ● PostgreSQL の標準機能「だけ」で使える パーティションノード パーティション（親） 外部テーブル（子） 外部テーブル（子） 外部テーブル（子） テーブル シャードノード シャードノード シャードノード テーブル テーブル

Built-in Sharding の例 ● パーティション機能＋ postgres_fdw パーティションノード パーティション（親） 外部テーブル（子） 外部テーブル（子） 外部テーブル（子） テーブル シャードノード シャードノード シャードノード テーブル テーブル CREATE TABLE ... PARTITION BY ... CREATE TABLE ... CREATE SERVER... CREATE USER MAPPING... CREATE FOREIGN TABLE PATITION BY... データの実体は シャードノードにある

Built-in Sharding の課題 ● パーティションノードへの負荷集中 ● 冗長化 ● バックアップ / リカバリ ● 2 Phase Commit 未対応

Built-in Sharding の課題 (1) ● パーティションノードへの負荷集中 パーティションノード パーティション（親） 外部テーブル（子） 外部テーブル（子） 外部テーブル（子） テーブル シャードノード シャードノード シャードノード テーブル テーブル AP AP AP AP AP ここがリソースネックになると シャードへ負荷がかけきれない ヒマだ～

Built-in Sharding の課題 (1) ● パーティーションノードの複数化 テーブル シャードノード シャードノード シャードノード テーブル テーブル AP AP AP AP AP パーティションノード （詳細略） パーティションノード （詳細略） Load balancer パーティションノード （詳細略） 面倒そう・・・

Built-in Sharding の課題 (1) ● パーティションノードの複数化の問題 – コスト上昇 – 設定面倒（自動化の検討） – 通信量増加→ NW 帯域にも留意

Built-in Sharding の課題 (2) ● 冗長化 – ダウンタイム短縮 ● Streaming Replication ● Pacemaker 等による自動 F/O の仕組み – 構築 / 運用コストの増加 – ねこなので Pacemaker にがてです

Built-in Sharding の課題 (2) ● シャードの冗長化 (PG-REX 構成） パーティションノード パーティション（親） 外部テーブル（子） 外部テーブル（子） 外部テーブル（子） テーブル シャードノード シャードノード シャードノード テーブル テーブル テーブル シャードノード Streaming Raplication シャードノード テーブル シャードノード テーブル Streaming Raplication Streaming Raplication Pacemaker Pacemaker 構築も運用も 大変そうだ・・・ PM PM PM PM パーティションノード （詳細略） Pacemaker Pacemaker Streaming Raplication

Built-in Sharding の課題 (3) ● バックアップ – 厳密に考え出すと、パーティションノード / 全シャードの 同一タイミングでのオンラインバックアップが必要 – PostgreSQL 自体には複数ノードの静止点をとる機能はない – グローバル XID のような管理情報が本当は必要なのかな？ ● リストア時→全ノードの同時リストアが必要？ ● Azure Cosmos DB for PostgreSQL のバックアップ / リストアってどうやっ て実現しているんだろう・・・？

Built-in Sharding の課題 (4) ● 2 Phase Commit 未対応 – トランザクション実行中に一部のノードがクラッシュした 場合、トランザクションとしては Rollback しているにもか かわらず一部のシャードは Commit される

Built-in Sharding の課題 (4) ● 一部ノードのクラッシュ問題 (0) node0 addr addr_kanagawa addr_tokyo addr_others addr_kanagawa node1 addr_tokyo addr_others node2 node3 パーティションキーは pref 列です

Built-in Sharding の課題 (4) ● 一部ノードのクラッシュ問題 (1) $ psql -p 16000 -U postgres testdb -a -f append-insert-pf.sql TABLE addr; id | pref | area | addr -------+----------+------------------+----------- 20001 | 東京都 | 千代田区 | 大手町 Z-Z 20002 | 東京都 | 武蔵野市 | 緑町 Y-X 10001 | 神奈川県 | 横浜市中区 | 寿町 X-XX 10002 | 神奈川県 | 横須賀市 | 光の丘 X-X 30001 | 埼玉県 | さいたま市大宮区 | 下町 X-XX 40001 | 千葉県 | 千葉市中央区 | 中央 X-X (6 rows) パーティションキーは pref です

Built-in Sharding の課題 (4) ● 一部ノードのクラッシュ問題 (2) BEGIN; BEGIN INSERT INTO addr VALUES (10003, ' 神奈川県 ', ' 町田市 ', ' 中央 C-C'); INSERT 0 1 INSERT INTO addr VALUES (20003, ' 東京都 ', ' 港区 ', ' 港南 K-K'); INSERT 0 1 INSERT INTO addr VALUES (30003, ' 埼玉県 ', ' 行田市 ', ' 十万石 J-J'); INSERT 0 1 SELECT pg_sleep(10); pg_sleep ---------- (1 row) -- sleep 中に node2 を再起動する 神奈川県は node1 東京都は node2 埼玉県は node3

Built-in Sharding の課題 (4) ● 一部ノードのクラッシュ問題 (3) COMMIT; psql:append-insert-pf.sql:9: ERROR: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. CONTEXT: remote SQL command: COMMIT TRANSACTION $ psql -p 16000 -U postgres testdb -c "TABLE addr" Null display is "(null)". id | pref | area | addr -------+----------+------------------+----------- 20001 | 東京都 | 千代田区 | 大手町 Z-Z 20002 | 東京都 | 武蔵野市 | 緑町 Y-X 10001 | 神奈川県 | 横浜市中区 | 寿町 X-XX 10002 | 神奈川県 | 横須賀市 | 光の丘 X-X 10003 | 神奈川県 | 町田市 | 中央 C-C 30001 | 埼玉県 | さいたま市大宮区 | 下町 X-XX 40001 | 千葉県 | 千葉市中央区 | 中央 X-X (7 rows) COMMIT 時に、単純に シャードノードごとに COMMIT を 発行するのでこうなる・・・ トランザクションは Rollback しているのに 神奈川県町田市のデータのみ COMMIT されている！

Built-in Sharding の課題 (4) ● 2 Phase Commit 対応： postgres_fdw_plus – 藤井さん作成の拡張機能 https://github.com/MasaoFujii/postgresql/tree/postgres_fdw_plus/contrib/postgres_fdw ↓ 今はこっちが開発リポジトリらしいです https://github.com/pgfdwplus/postgres_fdw_plus – 2 Phase Commit 対応

Built-in Sharding の課題 (4) ● 一部ノードのクラッシュ問題の対応例 (1) $ psql -p 16000 -U postgres testdb -a -f append-insert-pf.sql TABLE addr; id | pref | area | addr -------+----------+------------------+----------- 20001 | 東京都 | 千代田区 | 大手町 Z-Z 20002 | 東京都 | 武蔵野市 | 緑町 Y-X 10001 | 神奈川県 | 横浜市中区 | 寿町 X-XX 10002 | 神奈川県 | 横須賀市 | 光の丘 X-X 30001 | 埼玉県 | さいたま市大宮区 | 下町 X-XX 40001 | 千葉県 | 千葉市中央区 | 中央 X-X (6 rows) PostgreSQL 16 beta1 環境で 検証しています。 各ノードの max_prepared_transactions を 1 以上にするのも忘れずに

Built-in Sharding の課題 (4) ● 一部ノードのクラッシュ問題の対応例 (2) $ psql -p 16000 -U postgres testdb -a -f append-insert.sql LOAD 'postgres_fdw_plus'; LOAD SET postgres_fdw.two_phase_commit = on; SET TABLE addr; id | pref | area | addr -------+----------+------------------+----------- 20001 | 東京都 | 千代田区 | 大手町 Z-Z 20002 | 東京都 | 武蔵野市 | 緑町 Y-X 10001 | 神奈川県 | 横浜市中区 | 寿町 X-XX 10002 | 神奈川県 | 横須賀市 | 光の丘 X-X 30001 | 埼玉県 | さいたま市大宮区 | 下町 X-XX 40001 | 千葉県 | 千葉市中央区 | 中央 X-X (6 rows) postgres_fdw.two_phase_commit を on にしないと postgres_fdw と 同じ動作になります。

Built-in Sharding の課題 (4) ● 一部ノードのクラッシュ問題の対応例 (3) BEGIN INSERT INTO addr VALUES (10003, ' 神奈川県 ', ' 町田市 ', ' 中央 C-C'); INSERT 0 1 INSERT INTO addr VALUES (20003, ' 東京都 ', ' 港区 ', ' 港南 K-K'); INSERT 0 1 INSERT INTO addr VALUES (30003, ' 埼玉県 ', ' 行田市 ', ' 十万石 J-J'); INSERT 0 1 SELECT pg_sleep(10); pg_sleep ---------- (1 row) -- sleep 中に node2 を再起動する

Built-in Sharding の課題 (4) ● 一部ノードのクラッシュ問題の対応例 (4) COMMIT; psql:append-insert.sql:11: ERROR: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. CONTEXT: remote SQL command: PREPARE TRANSACTION 'pgfdw_768_16415_29446_null' $ psql -p 16000 -U postgres testdb -c "TABLE addr" id | pref | area | addr -------+----------+------------------+----------- 20001 | 東京都 | 千代田区 | 大手町 Z-Z 20002 | 東京都 | 武蔵野市 | 緑町 Y-X 10001 | 神奈川県 | 横浜市中区 | 寿町 X-XX 10002 | 神奈川県 | 横須賀市 | 光の丘 X-X 30001 | 埼玉県 | さいたま市大宮区 | 下町 X-XX 40001 | 千葉県 | 千葉市中央区 | 中央 X-X (6 rows) $ トランザクションは Rollback して 神奈川県町田市の INSERT も Rollback されている！

Built-in Sharding の課題 (4) ● postgres_fdw_plus 利用時の課題 – PostgreSQL 標準の配布物ではない – 利用するためにはビルドが必要 – 実案件導入の壁がある – AWS 等の DBaaS では使えない

Built-in Sharding の課題 ( おまけ ) ● パーティションキーを UPDATE すると奇妙なことに $ psql -p 16000 -U postgres testdb -e -f update-partkey.txt SELECT * FROM addr; id | pref | area | addr -------+----------+------------------+----------- 20001 | 東京都 | 千代田区 | 大手町 Z-Z 20002 | 東京都 | 武蔵野市 | 緑町 Y-X 10001 | 神奈川県 | 横浜市中区 | 寿町 X-XX 10002 | 神奈川県 | 横須賀市 | 光の丘 X-X 10003 | 神奈川県 | 町田市 | 中央 C-C 30001 | 埼玉県 | さいたま市大宮区 | 下町 X-XX 40001 | 千葉県 | 千葉市中央区 | 中央 X-X (7 rows) UPDATE addr SET pref = ' 東京都 ' WHERE area = ' 町田市 '; UPDATE 1 SELECT * FROM addr WHERE pref = ' 東京都 '; id | pref | area | addr -------+--------+----------+----------- 20001 | 東京都 | 千代田区 | 大手町 Z-Z 20002 | 東京都 | 武蔵野市 | 緑町 Y-X (2 rows) UPDATE したレコードが消える！？ UPDATE 結果が addr_kanagawa にあるため

Built-in Sharding の課題 ( おまけ ) ● WITH ... (DELETE ... RETURING) INSERT ... なら OK $ psql -p 16000 -U postgres testdb -e -f delete-returing-insert.txt SELECT * FROM addr; id | pref | area | addr -------+----------+------------------+----------- 20001 | 東京都 | 千代田区 | 大手町 Z-Z 20002 | 東京都 | 武蔵野市 | 緑町 Y-X 10001 | 神奈川県 | 横浜市中区 | 寿町 X-XX 10002 | 神奈川県 | 横須賀市 | 光の丘 X-X 10003 | 神奈川県 | 町田市 | 中央 C-C 30001 | 埼玉県 | さいたま市大宮区 | 下町 X-XX 40001 | 千葉県 | 千葉市中央区 | 中央 X-X (7 rows) WITH del AS ( DELETE FROM addr WHERE area = ' 町田市 ' RETURNING * ) INSERT INTO addr SELECT del.id, ' 東京都 ', del.area, del.addr FROM del; INSERT 0 1 SELECT * FROM addr WHERE pref = ' 東京都 '; id | pref | area | addr -------+--------+----------+----------- 20001 | 東京都 | 千代田区 | 大手町 Z-Z 20002 | 東京都 | 武蔵野市 | 緑町 Y-X 10003 | 東京都 | 町田市 | 中央 C-C (3 rows) addr_kanagawa から DELETE した結果を addr_tokyo に INSERT する

まとめ ● Built-in シャーディングは可能 ● 課題 – 親側の負荷集中、冗長化、バックアップ、 2PC 対応 ● 運用面が不安→マネージドサービスが安全？ ● postgres_fdw_plus の標準機能化

おしまい