AnalyticDB for PostgreSQL における単一行更新は、分散トランザクションのオーバーヘッドが大きくなります。複数の更新を単一の SQL ステートメントにバッチ処理することで、そのオーバーヘッドを大幅に削減し、スループットを向上させることができます。
AnalyticDB for PostgreSQL は、更新 (マージとも呼ばれます) をアップサート操作として扱います。受信データ行が既存のレコードと一致する場合、古いバージョンを置き換えます。一致するものが存在しない場合、その行は挿入されます。
仕組み
すべての UPDATE ステートメントは、完全な分散トランザクションサイクルをトリガーします。
マスターノードは UPDATE ステートメントを受信し、分散トランザクションを開始し、ターゲットテーブルをロックします。同じテーブルへの同時更新は許可されません。
マスターノードは、リクエストを一致するセグメントノードにルーティングします。
各セグメントノードは、インデックスをスキャンしてターゲット行を特定します。列指向テーブルの場合、更新は物理的には古い行の削除と、その後のテーブルの末尾への新しい行の追加です。更新されたデータページはメモリキャッシュに書き込まれ、テーブルファイル長の変更はライトアヘッドログ (WAL) に記録されます。
トランザクションがコミットされる前に、更新されたデータページと WAL はミラーに同期されます。同期が完了した後、マスターノードは分散トランザクションを終了し、成功応答を返します。
各ステートメントには、SQL 解析、分散トランザクション管理、テーブルロック、マスターノードからセグメントノードへのネットワークラウンドトリップ、およびセグメントノードからミラーへのログ同期といった固定オーバーヘッドが発生します。単一行更新の場合、このオーバーヘッドが実際の作業を支配します。
オーバーヘッドを削減するには、可能な限り複数の更新を単一の SQL ステートメントと単一のトランザクションにバッチ処理してください。
一括更新
ステージングテーブルを使用して、複数の行更新を1つのステートメントにバッチ処理します。
ステップ1: ターゲットテーブルの準備
CREATE TABLE target_table (c1 INT, c2 INT, PRIMARY KEY (c1));
INSERT INTO target_table SELECT generate_series(1, 10000000);ターゲットテーブルは通常は大きいです。プライマリキーは、オプティマイザーが更新中に効率的に行を特定するために使用する一意なインデックスを作成します。
ステップ2: 新しいデータをステージングテーブルにロード
ステージングテーブルは、ターゲットテーブルにマージされる前の受信データを保持します。COPY コマンド、OSS 外部テーブル、または直接 INSERT を使用して、データをステージングテーブルにロードします。
CREATE TABLE source_table (c1 INT, c2 INT);
INSERT INTO source_table SELECT generate_series(1, 100), generate_series(1, 100);データをロードした後、オプティマイザーが正確な行数統計を持つように ANALYZE source_table を実行します。
ステップ3: 一括 UPDATE の実行
SET optimizer = ON;
UPDATE target_table
SET c2 = source_table.c2
FROM source_table
WHERE target_table.c1 = source_table.c1;インデックス使用を最大化するために、UPDATE の前にSET optimizer = ONで ORCA オプティマイザーを有効にしてください。ORCA が利用できない場合は、SET enable_nestloop = ONを実行して、インデックススキャンを伴うネステッドループ結合を強制してください。複数のインデックスフィールドやパーティションテーブルを含む複雑なケースの場合、インデックスを正しく一致させるには ORCA が必要です。
オプティマイザーがプランを選択する方法
ステージングテーブルが小さい場合、オプティマイザーはネステッドループ結合とインデックススキャンを使用します。
EXPLAIN UPDATE target_table SET c2 = source_table.c2
FROM source_table WHERE target_table.c1 = source_table.c1; QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Update (cost=0.00..586.10 rows=25 width=1)
-> Result (cost=0.00..581.02 rows=50 width=26)
-> Redistribute Motion 4:4 (slice1; segments: 4) (cost=0.00..581.02 rows=50 width=22)
Hash Key: public.target_table.c1
-> Assert (cost=0.00..581.01 rows=50 width=22)
Assert Cond: NOT public.target_table.c1 IS NULL
-> Split (cost=0.00..581.01 rows=50 width=22)
-> Nested Loop (cost=0.00..581.01 rows=25 width=18)
Join Filter: true
-> Table Scan on source_table (cost=0.00..431.00 rows=25 width=8)
-> Index Scan using target_table_pkey on target_table (cost=0.00..150.01 rows=1 width=14)
Index Cond: public.target_table.c1 = source_table.c1ステージングテーブルが大きくなるにつれて、オプティマイザーは、それがより効率的であると推定する場合、全表スキャンを伴うハッシュ結合に切り替える場合があります。
INSERT INTO source_table SELECT generate_series(1, 1000), generate_series(1, 1000);
ANALYZE source_table;
EXPLAIN UPDATE target_table SET c2 = source_table.c2
FROM source_table WHERE target_table.c1 = source_table.c1; QUERY PLAN
------------------------------------------------------------------------------------------------------
Update (cost=0.00..1485.82 rows=275 width=1)
-> Result (cost=0.00..1429.96 rows=550 width=26)
-> Assert (cost=0.00..1429.94 rows=550 width=22)
Assert Cond: NOT public.target_table.c1 IS NULL
-> Split (cost=0.00..1429.93 rows=550 width=22)
-> Hash Join (cost=0.00..1429.92 rows=275 width=18)
Hash Cond: public.target_table.c1 = source_table.c1
-> Table Scan on target_table (cost=0.00..477.76 rows=2500659 width=14)
-> Hash (cost=431.01..431.01 rows=275 width=8)
-> Table Scan on source_table (cost=0.00..431.01 rows=275 width=8)一括削除
DELETE 操作には同じステージングテーブルパターンを使用します。DELETE ... USING 構文は、ターゲットテーブルとステージングテーブルを結合することで、削除する行を特定します。
DELETE FROM target_table
USING source_table
WHERE target_table.c1 = source_table.c1;クエリプランは、ターゲットテーブルでインデックススキャンを使用します。
EXPLAIN DELETE FROM target_table
USING source_table WHERE target_table.c1 = source_table.c1; QUERY PLAN
-----------------------------------------------------------------------------------------------------
Delete (slice0; segments: 4) (rows=50 width=10)
-> Nested Loop (cost=0.00..41124.40 rows=50 width=10)
-> Seq Scan on source_table (cost=0.00..6.00 rows=50 width=4)
-> Index Scan using target_table_pkey on target_table (cost=0.00..205.58 rows=1 width=14)
Index Cond: target_table.c1 = source_table.c1削除 + 挿入によるデータマージ
データをマージする場合、まずデータをステージングテーブルにロードします。
マージするすべてのデータがすでにターゲットテーブルに存在することが事前にわかっている場合は、UPDATE ステートメントを使用してデータをマージします。
ほとんどの場合、マージするデータの一部はすでにターゲットテーブルに存在し、一部は一致するレコードのない新しいデータです。この場合、単一トランザクション内で一括削除と一括挿入の組み合わせを使用します。
SET optimizer = ON;
DELETE FROM target_table USING source_table WHERE target_table.c1 = source_table.c1;
INSERT INTO target_table SELECT * FROM source_table;これにより、ターゲットテーブルから一致するすべての行が削除され、その後ステージングテーブルからすべての行が挿入され、実質的にステージングテーブル全体を一度にアップサートします。
VALUES 式を使用したリアルタイム更新
ステージングテーブルパターンでは、バッチごとにテーブルの作成、データ投入、および削除 (または切り詰め) が必要です。継続的な低遅延データ同期の場合、代わりにインライン VALUES 式を使用します。
-- Bulk update without a staging table
UPDATE target_table
SET c2 = t.c2
FROM (VALUES (1, 1), (2, 2), (3, 3), ..., (2000, 2000)) AS t(c1, c2)
WHERE target_table.c1 = t.c1;
-- Bulk delete without a staging table
DELETE FROM target_table
USING (VALUES (1, 1), (2, 2), (3, 3), ..., (2000, 2000)) AS t(c1, c2)
WHERE target_table.c1 = t.c1;アプリケーションで更新する行を VALUES リストに結合し、ステートメントを送信してください。SET optimizer = ON と SET enable_nestloop = ON の両方が、VALUES ベースの更新にインデックスを使用するクエリプランを生成します。複数のインデックスフィールドまたはパーティションテーブルを含む複雑なケースの場合、SET optimizer = ON を使用してください。