マージとも呼ばれる更新は、AnalyticDB for PostgreSQL に最新のデータを更新することを示します。 更新されたデータが既に存在する場合は、古いバージョンが置き換えられます。 更新されたデータが存在しない場合は、データベースに挿入されます。 このようなデータのマージは、通常、オフラインで完了します。 たとえば、毎日 AnalyticDB for PostgreSQL にデータを更新するように設定できます。 あるユーザーは、遅延が分または秒レベルのリアルタイム更新が必要かもしれません。

ここでは、 AnalyticDB for PostgreSQL データをマージする方法とその原則について説明します。 さらに、一括操作を使用して複数のデータを更新する方法も学べます。

簡単なアップデート

データのマージは、データの変更、つまり更新、削除、挿入、またはコピー操作の実行に関するものです。 更新操作を例にとると、column-store テーブルの単一の行で記録を更新することです。 次の図は、AnalyticDB for PostgreSQL のデータ更新プロセスを示しています。



  1. <p class="p" id="p-4ni-ph6-vex">手順は次のとおりです。</p>
  2. <ol class="ol" id="ol-e2v-pzz-gfb">
  3. <li class="li" id="li-dph-8l4-utb">
  4. <p class="p" id="p-oku-f04-65b">ユーザーは、Update SQL リクエストをマスターノードに送信します。</p>
  5. </li>
  6. <li class="li" id="li-a44-hkf-0bx">
  7. <p class="p" id="p-fo4-thx-obx">マスターノードは、分散トランザクションを開始し、更新するテーブルをロックします (AnalyticDB for PostgreSQL は、同じテーブルへの同時更新を許可しません)。一致するスレーブノードに更新リクエストを配信します。</p>
  8. </li>
  9. <li class="li" id="li-v9v-qtq-sz5">
  10. <p class="p" id="p-ryk-l86-0mc">スレーブノードは、インデックスをスキャンして更新するデータを探し出し、データを更新します。 column-store テーブルの場合、古いデータ行を削除し、新しいデータ行をテーブルの末尾に書き込むことが更新のロジックです。
  11. column-store テーブル内の更新されたデータページがメモリキャッシュに書き込まれ、対応するテーブルファイルの長さの変更 (データがテーブルの終わりに書き込まれるため、対応するテーブルファイルの長さが増えます)
  12. がログ (xlog ファイル) に記載されます。
  13. </p>
  14. </li>
  15. <li class="li" id="li-1es-ipw-42e">
  16. <p class="p" id="p-j3z-6tp-q7a">更新プロセスが終了する前に、メモリ内の更新されたデータページと xlog ファイルは、両方ともミラーノードに同期されます。 同期が完了すると、マスターノードは分散トランザクションを終了し、成功した実行に関するメッセージをユーザーに返します。</p>
  17. </li>
  18. </ol>
  19. <p class="p" id="p-u2h-3b0-o82">プロセス全体は長く、SQL 文の解析、トランザクションの配布、ロック、マスターノードとスレーブノード間の接続、スレーブノードとミラーノード間のデータとログの同期など、多くの操作が含まれています。
  20. これらの操作はすべて CPU または I/O リソースを消費し、リクエストの応答が遅くなります。
  21. </p>
  22. <p class="p" id="p-ja8-sit-roo">したがって、 AnalyticDB for PostgreSQL の場合は、単一のデータ行への更新を避け、可能な限り一括操作を使用してデータを更新する以下のことを推奨します。</p>
  23. <ul class="ul" id="ul-f2v-pzz-gfb">
  24. <li class="li" id="li-m4y-syd-xc3">文の解析、ノード通信、およびデータ同期のオーバーヘッドを軽減するために、1 つの SQL 文を更新します。</li>
  25. <li class="li" id="li-en7-wqj-8v1">不要なオーバーヘッドを避けるために、1 つのトランザクションを更新します。</li>
  26. </ul>
  27. </section>
  28. <section class="section" id="section-g2v-pzz-gfb">
  29. <h2 class="title sectiontitle" id="title-2y4-urs-zxi">一括更新</h2>
  30. <p class="p" id="p-y0d-sep-esl">1 つの SQL 文を使用して複数の独立したデータ行を更新するには、次の手順に従います。 </p>
  31. <p class="p" id="p-xj5-kwe-arr"><strong class="ph b" id="b-zdd-uij-w3k">1. 対象テーブルの準備</strong></p>
  32. <p class="p" id="p-xc4-uoa-qwh">更新するテーブルが target_table であるとします。 target_table は以下のように定義されます。 </p>
  33. <pre class="pre codeblock" id="codeblock-7m4-rij-cr4"><code>create table target_table(c1 int, c2 int, primary key (c1));

insert into target_table select gen-USerate_series(1, 10000000);

対象テーブルは、通常非常に大きくなります。 target_table に 1,000万行のデータを挿入するとします。 target_table は、更新を容易にするために索引付けされています。 A プライマリーキーが定義され、その結果、ユニークなインデックスが含まれます。

  1. <p class="p" id="p-csi-jez-pko"><strong class="ph b" id="b-bsa-bvz-gi2">2. ステージテーブルの準備</strong></p>
  2. <p class="p" id="p-5b5-83x-mhb">一括更新にはステージテーブル (この例ではsource_table) が必要です。 これは、データを更新するために作成された一時テーブルです。 target_table
  3. のデータを更新するには、最初に新しいデータを source_table に挿入し、<a title="" href="~~35433#concept-wtt-rmr-52b~~">[COPY コマンド]</a><a title="" href="~~35457#concept-ofw-3mr-52b~~">[OSS 外部テーブル]</a>、または target_table への別の手段で、新しいデータをインポートします。
  4. </p>
  5. <p class="p" id="p-fyi-dr8-r4u">次の例では、データがいくつか source_table に直接生成されています。</p>
  6. <pre class="pre codeblock" id="codeblock-42v-46i-j1v"><code>create table source_table(c1 int, c2 int);

insert into source_table select gen-USerate_series(1, 100), gen-USerate_series(1,100);

3. 一括更新

  1. <p class="p" id="p-p69-46q-uac">source_table データの準備が完了したら、<code class="ph codeph" id="codeph-ge4-zf5-nqq"> update set … from … where ..</code>文を実行します。
  2. </p>
  3. <div class="note note note-note" id="note-xsm-ta6-bdx">
  4. <div class="note-icon-wrapper"><i class="icon-note note"></i></div>
  5. <div class="note-content"><strong></strong> インデックスを最大限に活用するには、更新操作の前に<code class="ph codeph" id="codeph-cpd-d3r-c25">set optimizer = on</code>を使用して ORCA オプティマイザを起動します。 ORCA オプティマイザが起動していない場合は、<code class="ph codeph" id="codeph-pqf-qmb-plm">set enable_nestloop = on </code>を実行してインデックスを使用できます。
  6. </div>
  7. </div>
  8. <pre class="pre codeblock" id="codeblock-dx0-pxb-qus"><code>set optimizer = on;

target_tableをsource_table.c1からsource_table.c2に設定します。

更新操作のクエリプランは次のとおりです。

  1. postgres=> explain update target_table set c2 = source_table.c2 from source_table where target_table.c1= source_table.c1; QUERY PLAN


  2. 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 Condpublic.target_table.c1 = source_table.c1

このプランから、 AnalyticDB for PostgreSQL はインデックスを使用します。 しかし、source_table にさらにデータを追加すると、オプティマイザは、Nest Loop 関連メソッドとインデックススキャンを使用する方がインデックスを削除するよりは効率的ではないと判断する可能性があります。 結果として、ハッシュ関連方法とテーブルスキャンを使用して実行するかもしれません。 たとえば、

  1. postgres=> insert into source_table select gen-USerate_series(1, 1000), gen-USerate_series(1,1000);INSERT 0 1000postgres => analyze source_table;ANALYZEpostgres=> explain update target_table set c2 = source_table.c2 from source_table where target_table.c1= source_table.c1; QUERY PLAN


  2. 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)

ここで説明している一括更新アプローチでは、SQLのコンパイル、ノード間通信、トランザクション、およびその他のオーバーヘッドが削減され、データ更新パフォーマンスが大幅に向上し、リソース消費が削減されます。

  1. </p>
  2. </section>
  3. <section class="section" id="section-z2v-pzz-gfb">
  4. <h2 class="title sectiontitle" id="title-9ri-3gr-z29">一括削除</h2>
  5. <p class="p" id="p-pei-yqs-518">削除操作に関して、一括更新に使用されるのと同様のステージテーブルを使用し、次の削除コマンドと &amp;quot;Using&amp;quot; 構文でデータを一括削除できます。</p>
  6. <pre class="pre codeblock" id="codeblock-lnx-99l-ha2"><code>delete from target_table using source_table where target_table.c1 = source_table.c1;</code></pre>
  7. <p class="p" id="p-9pm-6lm-l7h">一括削除操作でもインデックスが使用されます。</p>
  8. <pre class="pre codeblock" id="codeblock-qnx-5un-lsc"><code>explain delete from target_table using source_table where target_table.c1 = source_table.c1;
  9. QUERY PLAN

Delete (slice0; segmen-USts: 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

  1. <section class="section" id="section-efv-pzz-gfb">
  2. <h2 class="title sectiontitle" id="title-xy5-9hp-yqv">削除と挿入を使ってデータをマージする</h2>
  3. <p class="p" id="p-pmf-bpb-ihb">データをマージするには、まずデータをステージテーブルにマージする必要があります。 マージするデータが既にターゲットテーブルに存在することが事前に分かっている場合は、更新文を使用してデータをマージします。
  4. しかし、ほとんどの場合、マージするデータのある部分は既にターゲットテーブルに存在し、一部が新しくなっており、ターゲットテーブルに一致するレコードが存在していません。
  5. この場合、一括削除と一括挿入の組み合わせが使用できます。 サンプルコードは次のとおりです。
  6. </p>
  7. <pre class="pre codeblock" id="codeblock-r4x-ktr-i33"><code>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;

  1. <section class="section" id="section-gfv-pzz-gfb">
  2. <h2 class="title sectiontitle" id="title-he1-fb7-epk">Values( ) を使用してリアルタイムでデータを更新する</h2>
  3. <p class="p" id="p-dc1-0zs-003">ステージテーブルを使用するには、そのライフサイクルを維持する必要があります。 一部のユーザーは、リアルタイムに一括で PostgreSQL に関して HybridDB
  4. にデータを更新したいと考えています、つまり、継続的にデータを同期したり PostgreSQL 用に HybridDB にデータをマージしたいと考えています。
  5. </p>
  6. <p class="p" id="p-cju-20g-d7j">前述の方法を使用する場合は、ステージテーブルを繰り返し作成および削除 (または切り捨て) する必要があります。 実際、テーブルを維持するために苦労することなく、Values
  7. 式を使用してステージテーブルと同様の効果を得ることができます。 まず、データをスプライスして Values 式に更新し、次の方法を使用して、更新コマンドまたは削除コマンドを実行します。
  8. </p>
  9. <pre class="pre codeblock" id="codeblock-e5k-hcs-rh1"><code>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

delete from target_table using (values(1,1),(2,2),(3,3),…(2000,2000)) as t(c1,c2) where target_table.c1 = t.c1

どちらも「オプティマイザ= on」「enable_nestloop = on」を設定し、 インデックスを使用するクエリプランを生成します。ただし、複数の索引フィールドやパーティションテーブルが含まれるような複雑な場合は、ORCA オプティマイザを使用して索引を一致させる必要があります。

  1. </section>
  2. </div>
  3. </article>
  4. </main>