ePQ支援並行寫入及查詢OSS外部表格 - PolarDB

PolarDB PostgreSQL版的彈性跨機並行查詢（ePQ）支援並行寫入及查詢OSS外部表格。

前提條件

支援的PolarDB PostgreSQL版的版本如下：

PostgreSQL 14（核心小版本14.9.13.0及以上）
PostgreSQL 11（核心小版本1.1.35及以上）

說明

您可通過如下語句查看PolarDB PostgreSQL版的核心小版本的版本號碼：

PostgreSQL 14
```
select version();
```
PostgreSQL 11
```
show polar_version;
```

背景資訊

PolarDB PostgreSQL版支援通過oss_fdw外掛程式建立OSS外部表格，其實體儲存體位於阿里雲Object Storage Service上，在資料庫內僅儲存表的元資訊。資料庫中不常使用的歷史資料、冷資料可以以外部表格的形式歸檔到OSS上，從而節省資料庫的儲存成本。PolarDB PostgreSQL版的FDW介面標準保證了OSS外部表格依舊支援通過標準SQL進行受限的讀寫訪問。

將本地表的資料通過寫入OSS外部表格建立歸檔時，PolarDB PostgreSQL版預設將啟動一個進程寫入OSS外部表格，其本質上是單進程上傳的網路訪問模式。在歸檔資料量非常大的情境中，單進程寫入OSS外部表格無法有效利用OSS的網路高頻寬，導致非常低效。

在查詢OSS外部表格中的歷史歸檔資料時，PolarDB PostgreSQL版預設將啟動一個進程查詢OSS外表對應的全量資料，其本質上也是單進程下載的網路訪問模式。當歸檔資料量非常大時，單進程查詢OSS外部表格無法有效利用OSS的網路高頻寬，也相對低效。

PolarDB PostgreSQL版的彈性跨機並行查詢（ePQ）支援並行寫入及查詢OSS外部表格：

ePQ最佳化器能夠產生多進程並行寫入OSS外部表格的執行計畫，ePQ執行器將在讀寫節點上啟動多個進程並行寫入OSS外部表格。
ePQ最佳化器能夠產生多進程並行查詢OSS外部表格的執行計畫，ePQ執行器將在多個計算節點上啟動多個進程並行查詢OSS外部表格。

ePQ對OSS外部表格的並行化讀寫訪問能力將資料庫對OSS的網路訪問模式從單進程上傳/下載模式最佳化為多進程上傳/下載模式，從而能夠充分利用OSS的網路頻寬資源，提升冷資料歸檔和冷資料查詢的效能。

使用指南

準備資料

建立一張本地表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);

建立一張與本地表t_local結構相同的OSS外部表格t_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外部表格

執行以下命令，禁用ePQ。
```
SET polar_enable_px TO OFF;
```
執行以下命令，將本地表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)
```
根據上述執行計畫顯示，執行器將啟動一個進程來掃描本地表並同時寫入OSS外部表格。總計用時為8861.708ms。
執行以下命令，啟用ePQ以及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;
```

再次執行以下命令，執行計畫與時間如下。

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.212ms，較單進程寫入有了明顯的提升。

並行查詢OSS外部表格

執行以下命令，禁用ePQ。
```
SET polar_enable_px TO OFF;
```

執行以下命令，查詢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)

根據上述執行計畫顯示，執行器將啟動一個進程來掃描OSS外部表格對應的17個OSS檔案，總計297 MB，用時為36230.325ms。

執行以下命令，啟用ePQ並設定查詢並行度為8。
```
SET polar_enable_px TO ON;
SET polar_px_dop_per_node TO 8;
```

再次執行以下命令，執行計畫與結果如下。

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執行架構將在兩個計算節點上總共啟動16個進程（segments: 16）執行計畫分區slice1，並行掃描OSS外部表格（Partial Foreign Scan）。這16個進程將會以OSS上的檔案為粒度，各自對查詢任務進行分工。所有進程的查詢結果將會通過Motion運算元（PX Coordinator 16:1）匯聚到查詢發起進程並返回。總計用時為18100.894ms，較單進程查詢有了明顯的提升。