PolarDB:Parallel writes and queries of OSS foreign tables

Background

PolarDB for PostgreSQL (Compatible with Oracle) lets you create OSS foreign tables using the oss_fdw extension. Foreign tables are physically stored in OSS, with only metadata stored in the database. This lets you archive cold data or historical data from databases to OSS, reducing database storage costs while still reading and writing the data through standard SQL.

By default, PolarDB uses a single process to write data to or read data from OSS foreign tables. In large-scale archiving or analytics workloads, a single process underutilizes available network bandwidth, making write and query operations slow.

ePQ addresses this by generating multi-process execution plans:

• Parallel writes: The ePQ optimizer starts multiple processes on read/write nodes to write data to an OSS foreign table simultaneously.

• Parallel queries: The ePQ optimizer starts multiple processes on read/write nodes to scan an OSS foreign table simultaneously.

This makes full use of OSS network bandwidth and significantly improves throughput for cold data archiving and historical data queries.

ePQ parameters

The following parameters control parallel writes and queries of OSS foreign tables.

Prerequisites

Before you begin, ensure that you have:

• A PolarDB for PostgreSQL (Compatible with Oracle) cluster

• Access to OSS with a bucket configured

• The oss_fdw extension available in your cluster

Set up an OSS foreign table

This section covers one-time setup steps. If you already have an OSS foreign table, skip to Write data to an OSS foreign table in parallel.

1. Create a local table t_local and populate it with test data.

   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. Create the oss_fdw extension, define an OSS server, and create the OSS foreign table t_oss with the same schema as t_local.

   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/');

Write data to an OSS foreign table in parallel

The following steps compare single-process and parallel write performance using the t_local and t_oss tables created in Set up an OSS foreign table.

1. Disable ePQ and run the INSERT to establish a single-process baseline.

   SET polar_enable_px TO OFF;
   
   EXPLAIN (COSTS OFF) INSERT INTO t_oss SELECT * FROM t_local;

   The execution plan and timing:

           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)

   The executor uses a single process to scan t_local and write to t_oss. Total time: 8861.708 ms.

2. Enable ePQ, enable parallel INSERT, and set the degree of parallelism to 16 for both reads and writes.

   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;

3. Run the INSERT again.

   EXPLAIN (COSTS OFF) INSERT INTO t_oss SELECT * FROM t_local;

   The execution plan and timing:

                       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)

   Reading the execution plan:

   • segments: 32 — 32 processes execute slice1 in parallel.

   • Partial Seq Scan — the local table is scanned across multiple processes simultaneously.

   • PX Hash 32:16 — the Motion operator distributes data from 32 scan processes to 16 write processes.

   Total time: 1321.212 ms — a roughly 6x improvement over single-process mode.

Query data in an OSS foreign table in parallel

1. Disable ePQ and run the query to establish a single-process baseline.

   SET polar_enable_px TO OFF;
   
   EXPLAIN SELECT COUNT(*) FROM t_oss;

   The execution plan and timing:

                                     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)

   The executor uses a single process to scan all 17 OSS objects (297 MB). Total time: 36230.325 ms.

2. Enable ePQ and set the degree of parallelism to 8.

   SET polar_enable_px TO ON;
   SET polar_px_dop_per_node TO 8;

3. Run the query again.

   EXPLAIN SELECT COUNT(*) FROM t_oss;

   The execution plan and timing:

                                         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)

   Reading the execution plan:

   • segments: 16 — 16 processes execute slice1 in parallel.

   • Partial Foreign Scan — the 17 OSS objects are distributed across 16 processes for parallel scanning.

   • PX Coordinator 16:1 — the Motion operator aggregates results from all 16 processes and returns them to the query-initiating process.

   Total time: 18100.894 ms — roughly 2x improvement over single-process mode.