PolarDB for PostgreSQL (Distributed Edition) クラスターでは、INSERT、UPDATE、DELETE などの標準的な PostgreSQL データ操作言語 (DML) コマンドを使用してデータを管理できます。構文は標準の PostgreSQL と一致していますが、実行動作とパフォーマンスは、データがノード間でどのように分散されるかと密接に関連しています。DML 操作が正しく効率的であることを保証するためには、以下のコア原則を理解する必要があります。
DML 操作のコア原則と制限
挿入には分布列を指定する:
INSERT文には分布列を含める必要があります。システムは分布列の値を使用してハッシュ値を計算し、これにより新しいデータのターゲットとなる物理シャードが決定されます。分布列が省略された場合、操作は失敗します。例:
idが分布列であると仮定します。挿入操作でid列を含めないと、エラーが返されます。INSERT INTO t (data) VALUES ('TEST');次の結果が返されます。
ERROR: cannot perform an INSERT without a partition column value分布列の値を変更しない: 行が書き込まれると、その物理的な場所は分布列の値によって決定されます。したがって、
UPDATEまたはINSERT ... ON CONFLICT DO UPDATE文で分布列の値を変更することはできません。この種の操作は、物理ノード間でデータを移動する必要があるため、サポートされていません。例:
idが分布列であると仮定します。id=1で行を挿入し、その後で分布列の値を変更しようとすると、エラーが発生します。-- 元のデータを挿入 INSERT INTO t (id, data) VALUES (1, 'TEST'); -- 分布列の値を変更しようとする UPDATE t SET id = 10 WHERE id = 1;次の結果が返されます。
ERROR: modifying the partition value of rows is not allowedノード間のトランザクション整合性に関する注意: PolarDB for PostgreSQL (Distributed Edition) は、2 フェーズコミット (2PC) プロトコルを使用して分散トランザクションの原子性を保証します。ただし、デフォルトの Read Committed 分離レベルでは、ノード間のスナップショット分離 (SI) はサポートされていません。
これは、複数のデータノード (DN) を含むトランザクションの場合、各ノードでのコミット時間がわずかに異なる可能性があることを意味します。この短い間隔の間に、同時実行の
SELECTクエリが不整合な中間状態を読み取る可能性があります。たとえば、あるクエリがあるノードでは新しいデータを、別のノードでは古いデータを読み取るかもしれません。アプリケーションが厳密なノード間のデータ整合性を必要とする場合は、ロックを使用したり、最近書き込まれたデータの読み取りを避けたりするなど、アプリケーション層で特別な処理を実装する必要があります。
一般的な DML 操作
INSERT: データの挿入
単一行および複数行の挿入を実行できます。また、ON CONFLICT 句を使用してデータ競合を処理することもできます。
単一行の挿入
-- 暗黙的な列構文。テーブルの列の順序に従ってデータを挿入します。 INSERT INTO t VALUES (1, 'TEST'); -- 明示的な列構文。データを挿入する列を明確に指定します。 INSERT INTO t (id, data) VALUES (1, 'TEST');バッチ挿入
INSERT INTO t VALUES (1, 'TEST'), (2, 'TEST'), (3, 'TEST');INSERT...SELECT: ソーステーブルからターゲットテーブルへのデータのインポートソーステーブルとターゲットテーブルを作成します。
-- ソーステーブル CREATE TABLE source (id int, data text); SELECT create_distributed_table('source', 'id'); -- ターゲットテーブル CREATE TABLE target (id int, data text); SELECT create_distributed_table('target', 'id');データをインポートします。
-- ソーステーブルとターゲットテーブルのスキーマが同じ場合 INSERT INTO target SELECT * FROM source; -- ターゲット列セットとデータ列セットを手動で指定する INSERT INTO target(id, data) SELECT id, data FROM source;
ON CONFLICT: データ競合の処理問題: 挿入操作のターゲットテーブルに PRIMARY KEY、UNIQUE、または EXCLUDE 制約がある場合、競合が発生する可能性があります。次のようなエラーが返されます。
ERROR: duplicate key value violates unique constraint "xxx"解決策:
ON CONFLICT DO NOTHING: 競合が発生した場合、新しいデータを無視します。INSERT INTO target VALUES (1, 'NEW_DATA') ON CONFLICT(id) DO NOTHING;ON CONFLICT DO UPDATE: 競合が発生した場合、指定された列を更新します。さらに、PolarDB for PostgreSQL (Distributed Edition) は、競合により拒否された新しい値を参照するためにEXCLUDEDの使用をサポートしています。-- 競合が発生した場合、指定された列を 'ERROR' などの静的な値に更新します。 INSERT INTO target VALUES (1, 'NEW_DATA') ON CONFLICT(id) DO UPDATE SET data='ERROR';-- 競合により除外された新しい値を参照するために EXCLUDED を使用します INSERT INTO target VALUES (1, 'NEW_DATA') ON CONFLICT(id) DO UPDATE SET data = EXCLUDED.data;
UPDATE/DELETE: データの更新と削除
これらのコマンドの構文は、標準の PostgreSQL と同じです。WHERE 句を使用して、ターゲットデータを効率的に特定します。
データの更新:
UPDATE t SET data = 'CHANGED' WHERE id = 1;データの削除:
DELETE FROM t WHERE id = 1;
COPY: データのバッチロード
COPY コマンドを使用して、ファイルから大量のデータを効率的にロードできます。サーバー側の COPY コマンドと、psql のクライアント側の \COPY メタコマンドの違いに注意してください。
COPY ... FROM 'file_path': コーディネーターノード (CN) やデータノード (DN) などのデータベースノードのファイルシステムからファイルを読み取ります。COPY target FROM '/path/to/data.csv' WITH CSV HEADER;\COPY ... FROM 'file_path':psqlコマンドが実行されるクライアントマシンからファイルを読み取り、データベースにストリーミングします。\COPY target FROM '/path/on/client/data.csv' WITH CSV HEADER;
DML パフォーマンスの最適化
複雑な DML 操作、特に INSERT...SELECT や複数テーブルの UPDATE または DELETE 文は、パフォーマンスに大きな違いがある複数のモードで実行できます。EXPLAIN コマンドを使用して実行計画を表示し、どの実行モードが使用されているかを確認できます。
DML 実行モード
実行モード | パフォーマンス評価 | トリガー条件 | コアコンセプト |
プッシュダウン並列実行 | ★★★★★ |
| 計算は各データノードに完全にプッシュダウンされ、並列実行されます。ノード間のデータ転送はありません。 |
再パーティション実行 | ★★ |
| ターゲットテーブルの分散ルールに合わせて、ソーステーブルのデータをノード間で再配布します。 |
プルアップおよびリダイレクト実行 | ★ | DML 文に | 中間結果をコーディネーターノードにプルして処理し、その結果をターゲットノードに送り返します。これにより、高いネットワークオーバーヘッドが発生します。 |
プッシュダウン並列実行
DML 操作には分散テーブルが 1 つだけ含まれます。
-- 単純な挿入文 INSERT INTO t VALUES (1, 'TEST'), (2, 'TEST'); -- 単純な更新文 UPDATE t SET data = 'CHANGE' WHERE id = 1; -- 単純な削除文 DELETE FROM t WHERE id = 1;DML 操作には複数の分散テーブルが含まれます。
例:
sourceテーブルとtargetテーブルが次のテーブルスキーマで存在すると仮定します。両方のテーブルは同じコロケーショングループにあり、それらの分布列は `id` です。-- ソーステーブル CREATE TABLE source (id int, data text); SELECT create_distributed_table('source', 'id'); -- ターゲットテーブル CREATE TABLE target (id int, data text); SELECT create_distributed_table('target', 'id');コロケーショングループ情報を表示します。
SELECT logicalrelid, colocationid FROM pg_dist_partition;次の結果が返されます。
logicalrelid | colocationid --------------+-------------- target | 1 source | 1次の DML 操作が実行されると、それらは単一ノード内で実行できるため、並列実行のためにプッシュダウンできます。
-- 挿入文の例 INSERT INTO target(id, data) SELECT id, data FROM source; -- 更新文の例 UPDATE target SET data = source.data FROM source WHERE target.id = source.id; -- 削除文の例 DELETE FROM target USING source WHERE target.id = source.id;INSERT...SELECTコマンドを例にとります。プッシュダウンできる典型的なコマンドは、同様にプッシュダウンできるいくつかの独立したサブタスクを生成します。EXPLAIN INSERT INTO target(id, data) SELECT id, data FROM source;次の結果が返されます。
QUERY PLAN -------------------------------------------------------------------------------------------------- Custom Scan (PolarCluster Adaptive) (cost=0.00..0.00 rows=0 width=0) Task Count: 4 -- タスクは 4 つのシャードに分散されて実行されます Tasks Shown: One of 4 -- 各シャードは INSERT...SELECT をローカルで実行します -> Task Node: host=10.xxx.xxx.xxx port=3006 dbname=testdb -> Insert on target_102105 polar_cluster_table_alias (cost=0.00..22.70 rows=0 width=0) -> Seq Scan on source_102101 source (cost=0.00..22.70 rows=1264 width=36) Filter: (id IS NOT NULL) (8 rows)
再パーティション実行
INSERT...SELECT 文のソーステーブルとターゲットテーブルの分散ルールが一致しない場合、DML 操作は単一ノード内で実行できません。これにより、データ再パーティションがトリガーされます。再パーティション中、ソースノードは必要なデータをパッケージ化し、コマンドが実行されるように適切な宛先ノードに転送します。
例:
source2テーブルとtarget2テーブルが次のテーブルスキーマで存在すると仮定します。-- ソーステーブル CREATE TABLE source2 (id int, t_id int, data text); SELECT create_distributed_table('source2', 'id'); -- ターゲットテーブル CREATE TABLE target2 (id int, data text); SELECT create_distributed_table('target2', 'id');次の
INSERT...SELECTコマンドを実行すると、再パーティションがトリガーされます。実行計画は、コマンドがrepartitionメソッドを使用して実行されることを示しています。これは、INSERT文がid列をターゲットにしているのに対し、SELECT文がt_id列を使用してid列を埋めるためです。EXPLAIN INSERT INTO target2(id, data) SELECT t_id, data FROM source2;次の結果が返されます。
QUERY PLAN --------------------------------------------------------------------------------------------- Custom Scan (PolarCluster INSERT ... SELECT) (cost=0.00..0.00 rows=0 width=0) INSERT/SELECT method: repartition -- データ再パーティションが発生したことを明確に示します -> Custom Scan (PolarCluster Adaptive) (cost=0.00..0.00 rows=100000 width=36) Task Count: 4 Tasks Shown: One of 4 -> Task Node: host=10.188.91.26 port=3006 dbname=testdb -> Seq Scan on source2_102117 source2 (cost=0.00..22.00 rows=1200 width=36) (8 rows)
プルアップおよびリダイレクト実行
DML 文に ORDER BY、LIMIT、OFFSET、または GROUP BY (グループ化キーに分布列が含まれていない場合) など、プッシュダウンできない句が含まれている場合、データはまずコーディネーターノード (CN) にプルされる必要があります。CN がマージ操作を完了した後、結果をターゲットノードに送り返します。このプロセスは、CN でボトルネックを作成し、大量のネットワークトラフィックを生成する可能性があります。
例:
source3テーブルとtarget3テーブルが次のテーブルスキーマで存在すると仮定します。-- ソーステーブル CREATE TABLE source3 (id int, t_id int, data text); SELECT create_distributed_table('source3', 'id'); -- ターゲットテーブル CREATE TABLE target3 (id int, data text); SELECT create_distributed_table('target3', 'id');次の
INSERT...SELECTコマンドが実行されると、プルアップおよびリダイレクト実行がトリガーされます。実行計画は、実行メソッドがpull to coordinatorであることを示しています。EXPLAIN INSERT INTO target3(id, data) SELECT t_id, data FROM source3 LIMIT 1;次の結果が返されます。
QUERY PLAN --------------------------------------------------------------------------------------------------------- Custom Scan (PolarCluster INSERT ... SELECT) (cost=0.00..0.00 rows=0 width=0) INSERT/SELECT method: pull to coordinator -- データがコーディネーターノードにプルされたことを明確に示します -> Limit (cost=0.00..0.00 rows=1 width=36) -> Custom Scan (PolarCluster Adaptive) (cost=0.00..0.00 rows=100000 width=36) Task Count: 4 Tasks Shown: One of 4 -> Task Node: host=10.188.91.26 port=3006 dbname=testdb -> Limit (cost=0.00..0.02 rows=1 width=36) -> Seq Scan on source3_102125 source3 (cost=0.00..22.00 rows=1200 width=36) (10 rows)