PolarDB:ePQ による OSS 外部テーブルへの並列書き込みとクエリのサポート

適用範囲

背景情報

PolarDB for PostgreSQL では、oss_fdw 拡張機能を使用して Object Storage Service (OSS) 外部テーブルを作成できます。これらの外部テーブルは物理的に Alibaba Cloud OSS に保存され、データベースにはメタデータのみが保存されます。使用頻度の低い既存データやコールドデータを外部テーブルとして OSS にアーカイブすることで、データベースのストレージコストを削減できます。PolarDB for PostgreSQL の外部データラッパー (FDW) 標準により、標準 SQL を使用して OSS 外部テーブルに対して限定的な読み取りと書き込みを引き続き実行できます。

ローカルテーブルから OSS 外部テーブルにデータを書き込んでアーカイブする場合、PolarDB for PostgreSQL はデフォルトで単一プロセスを開始します。これは実質的に単一プロセスのアップロードアクセスモードです。大量のデータをアーカイブする場合、この単一プロセスの書き込みモードは OSS の高いネットワーク帯域幅を十分に活用できないため、非効率です。

同様に、OSS 外部テーブル内のアーカイブされた既存データをクエリする場合、PolarDB for PostgreSQL はデフォルトで単一プロセスを開始して完全なデータをクエリします。これは単一プロセスのダウンロードアクセスモードです。大量のアーカイブデータをクエリする場合、この単一プロセスのクエリモードも OSS の高いネットワーク帯域幅を十分に活用できないため、非効率です。

PolarDB for PostgreSQL の弾性パラレルクエリ (ePQ) 機能は、OSS 外部テーブルへの並列書き込みとクエリをサポートします。

• ePQ オプティマイザーは、OSS 外部テーブルへの並列書き込みのための実行計画を生成します。その後、ePQ エグゼキュータは読み取り/書き込みノードで複数のプロセスを開始し、データを並列で書き込みます。

• ePQ オプティマイザーは、OSS 外部テーブルへの並列クエリのための実行計画を生成します。その後、ePQ エグゼキュータは複数のコンピューティングノードで複数のプロセスを開始し、データを並列でクエリします。

ePQ が提供する OSS 外部テーブルへの並列読み取りおよび書き込みアクセスは、データベースのネットワークアクセスモードを単一プロセスのアップロード/ダウンロードから複数プロセスのアップロード/ダウンロードに最適化します。この変更により、OSS のネットワーク帯域幅リソースが最大限に活用され、コールドデータのアーカイブとクエリのパフォーマンスが向上します。

利用ガイド

データの準備

1. t_local という名前のローカルテーブルを作成し、大量のデータを挿入します。

   CREATE TABLE t_local (id INT, age INT, msg TEXT, detail TEXT);
   
   INSERT INTO t_local
   SELECT
       random() * 1000000,
       random() * 10000,
       md5(random()::TEXT),
       md5(random()::TEXT)
   FROM generate_series(1, 2000000);

2. ローカルテーブル t_local と同じ構造を持つ t_oss という名前の OSS 外部テーブルを作成します。

   CREATE EXTENSION oss_fdw;
   
   CREATE SERVER ossserver FOREIGN DATA WRAPPER oss_fdw
   OPTIONS (host 'oss-cn-xxx.aliyuncs.com', bucket 'mybucket', id 'xxx', key 'xxx');
   
   CREATE FOREIGN TABLE t_oss (id INT, age INT, msg TEXT, detail TEXT)
   SERVER ossserver OPTIONS (dir 'archive/');

OSS 外部テーブルへの並列書き込み

1. 次のコマンドを実行して ePQ を無効にします。

   SET polar_enable_px TO OFF;

2. 次のコマンドを実行して、ローカルテーブル t_local から OSS 外部テーブル t_oss にデータをインポートします。実行計画と所要時間は次のとおりです。

   EXPLAIN (COSTS OFF) INSERT INTO t_oss SELECT * FROM t_local;
           QUERY PLAN
   ---------------------------
    Insert on t_oss
      ->  Seq Scan on t_local
   (2 rows)
   
   INSERT INTO t_oss SELECT * FROM t_local;
   INSERT 0 2000000
   Time: 8861.708 ms (00:08.862)

   実行計画は、エグゼキュータが 1 つのプロセスを開始してローカルテーブルをスキャンし、OSS 外部テーブルに書き込むことを示しています。合計実行時間は 8861.708 ms です。

3. 次のコマンドを実行して、ePQ と並列 INSERT 機能を有効にします。ローカルテーブルをスキャンするための並列度を 16 に、OSS 外部テーブルに書き込むための並列度を 16 に設定します。

   SET polar_enable_px TO ON;
   SET polar_px_enable_insert_select TO ON;
   SET polar_px_dop_per_node TO 16;
   SET polar_px_insert_dop_num TO 16;

4. コマンドを再度実行します。実行計画と所要時間は次のとおりです。

   EXPLAIN (COSTS OFF) INSERT INTO t_oss SELECT * FROM t_local;
                       QUERY PLAN
   ---------------------------------------------------
    Insert on t_oss
      ->  Result
            ->  PX Hash 32:16  (slice1; segments: 32)
                  ->  Partial Seq Scan on t_local
    Optimizer: PolarDB PX Optimizer
   (5 rows)
   
   INSERT INTO t_oss SELECT * FROM t_local;
   INSERT 0 2000000
   Time: 1321.212 ms (00:01.321)

   実行計画は、ePQ 実行フレームワークが 32 のプロセス (segments: 32) を開始してプランのスライス slice1 を実行し、ローカルテーブルを並列でスキャン (Partial Seq Scan) することを示しています。その後、Motion オペレーターがデータ (PX Hash 32:16) を再配布し、OSS 外部テーブルに並列で書き込む 16 のプロセスに渡します。合計実行時間は 1321.212 ms であり、単一プロセスの書き込みと比較して大幅に改善されています。

OSS 外部テーブルへの並列クエリ

1. 次のコマンドを実行して ePQ を無効にします。

   SET polar_enable_px TO OFF;

2. 次のコマンドを実行して、OSS 外部テーブル t_oss の総行数をクエリします。実行計画と結果は次のとおりです。

   EXPLAIN SELECT COUNT(*) FROM t_oss;
                                     QUERY PLAN
   -------------------------------------------------------------------------------
    Aggregate  (cost=1366687.96..1366687.97 rows=1 width=8)
      ->  Foreign Scan on t_oss  (cost=0.00..1334280.40 rows=12963024 width=0)
            Directory on OSS: archive/
            Number Of OSS file: 17
            Total size of OSS file: 297 MB
   (5 rows)
   
   SELECT COUNT(*) FROM t_oss;
     count
   ---------
    4000000
   (1 row)
   
   Time: 36230.325 ms (00:36.230)

   実行計画は、エグゼキュータが 1 つのプロセスを開始して外部テーブルの 17 個の OSS ファイルをスキャンし、合計 297 MB を 36,230.325 ms で処理することを示しています。

3. 次のコマンドを実行して ePQ を有効にし、クエリの並列度を 8 に設定します。

   SET polar_enable_px TO ON;
   SET polar_px_dop_per_node TO 8;

4. コマンドを再度実行します。実行計画と結果は次のとおりです。

   EXPLAIN SELECT COUNT(*) FROM t_oss;
                                         QUERY PLAN
   ---------------------------------------------------------------------------------------
    Aggregate  (cost=0.00..431.00 rows=1 width=8)
      ->  PX Coordinator 16:1  (slice1; segments: 16)  (cost=0.00..431.00 rows=1 width=1)
            ->  Partial Foreign Scan on t_oss  (cost=0.00..431.00 rows=1 width=1)
                  Directory on OSS: archive/
                  Number Of OSS file: 17
                  Total size of OSS file: 297 MB
    Optimizer: PolarDB PX Optimizer
   (7 rows)
   
   SELECT COUNT(*) FROM t_oss;
     count
   ---------
    4000000
   (1 row)
   
   Time: 18100.894 ms (00:18.101)

   実行計画は、ePQ 実行フレームワークが 2 つのコンピューティングノードで合計 16 のプロセス (segments: 16) を開始してプランのスライス slice1 を実行し、OSS 外部テーブルを並列でスキャン (Partial Foreign Scan) することを示しています。これらの 16 のプロセスは、OSS 上でファイル粒度でクエリタスクを分担します。すべてのプロセスからのクエリ結果は、Motion オペレーター (PX Coordinator 16:1) によって収集され、クエリを開始したプロセスに送信された後、返されます。合計実行時間は 18100.894 ms であり、単一プロセスのクエリと比較して大幅に改善されています。