rds_duckdb AP query acceleration PostgreSQL DuckDB - - Alibaba Cloud Documentation Center

Overview

The rds_duckdb extension embeds DuckDB into ApsaraDB RDS for PostgreSQL. You export local tables as column-oriented tables, and qualifying analytical queries are automatically routed to DuckDB for faster execution.

Prerequisites

Your ApsaraDB RDS for PostgreSQL instance runs major version PostgreSQL 13 or later.
Your instance runs minor engine version 20260130 or later.
You have added rds_duckdb to the running value of the shared_preload_libraries parameter.

Configure this in Set instance parameters. For example, set the running value to 'pg_stat_statements,auto_explain,rds_duckdb'.

Note

For minor engine version 20250228 and later, automatic incremental data synchronization for column-oriented tables is enabled by default.

Create and drop the extension

Use a privileged account to create or drop the extension.

Create the extension

CREATE EXTENSION rds_duckdb;

Check the DuckDB version

SELECT rds_duckdb.duckdb_version();

Drop the extension

DROP EXTENSION rds_duckdb;

Manage column-oriented tables

Create a column-oriented table

Export a local table (user table, materialized view, or foreign table) into a column-oriented table:

Note

On PostgreSQL 15 with minor engine version 20250228 or later, automatic incremental synchronization is enabled by default. Complete the setup in Configure automatic incremental synchronization for column-oriented tables before creating a column-oriented table.

SELECT rds_duckdb.create_duckdb_table('local_table_name');

Refresh a column-oriented table

Refresh a column-oriented table with the latest schema and data from its source table:

SELECT rds_duckdb.refresh_duckdb_table('local_table_name');

Check the table size

SELECT rds_duckdb.duckdb_table_size('local_table_name');

Check the total size of exported tables

SELECT rds_duckdb.duckdb_database_size();

Drop a column-oriented table

SELECT rds_duckdb.drop_duckdb_table('local_table_name');

Manage AP acceleration

The rds_duckdb extension currently accelerates read-only queries. When enabled, queries that exclusively reference tables with DuckDB column-oriented counterparts are offloaded to DuckDB. Queries involving DML, DDL, or tables without a column-oriented counterpart fall back to the PostgreSQL engine automatically.

Fallback returns a warning such as: WARNING: Trying to execute an operation with non-duckdb tables(test), fallback to PG. Tables in parentheses lack a column-oriented counterpart.

Write operations trigger: WARNING: Modification operations on DuckDB tables are currently not supported, fallback to PG.

Enable AP acceleration

SET rds_duckdb.execution = on;

Configure AP acceleration parameters

Tune these session-level parameters to control AP query acceleration:

SET rds_duckdb.worker_threads = 32;
SET rds_duckdb.memory_limit = 16384;

Parameter

Description

Suggestion

rds_duckdb.worker_threads

The number of worker threads for AP query acceleration.

Valid values: 1 to 255.

Default value: 1.

Set this to the number of available CPU cores for optimal performance.
Higher values improve throughput but increase CPU load.
Adjust based on your workload requirements.

rds_duckdb.memory_limit

The memory limit for AP query acceleration.

Unit: MB. Do not include the unit when you set the parameter value.

Valid values: 1 to INT32_MAX.

Default value: 100 (which indicates 100 MB).

Set as high as your workload allows for optimal performance.
Higher values increase memory consumption. Adjust based on your workload.
The default (100 MB) is conservative. Increase it based on your instance's resources.
A low value degrades performance during query acceleration and large table exports.

Note

For more details on DuckDB parameters, see the DuckDB documentation.

Disable AP acceleration

SET rds_duckdb.execution = off;

Configure auto-sync for column-oriented tables

On PostgreSQL 15 with minor engine version 20250228 or later, automatic incremental synchronization is enabled by default. Complete these steps before creating a column-oriented table:

In extension management, check the rds_duckdb version and upgrade it to 1.3 or later.
Set instance parameters to change the running value of the wal_level parameter to logical.
(Optional) If the target local table does not have a primary key, run the following command to set a REPLICA IDENTITY index as the replication key.
```
ALTER TABLE <local_table_name> REPLICA IDENTITY USING INDEX <index_name>;
```
Use an account with replication permission or a privileged account to create the column-oriented table.

Check the synchronization status and progress of column-oriented tables

SELECT * FROM rds_duckdb.duckdb_sync_stat;

Example output and parameter descriptions

Example output:

 sync_table | sync_status_description |          sync_error_description          | confirmed_lsn 
------------+-------------------------+------------------------------------------+---------------
 test       | not syncing             | no primary key or replica identity index | 
 test2      | not syncing             | no primary key or replica identity index | 
 test3      | data syncing            | no errors                                | 0/250D1E8
 test4      | not syncing             | no primary key or replica identity index | 
 test5      | data syncing            | no errors                                | 0/250D1E8
 test6      | data syncing            | no errors                                | 0/250D1E8
 test7      | data syncing            | no errors                                | 0/250D1E8
 test8      | data syncing            | no errors                                | 0/250D1E8

Parameter descriptions:

Parameter

Description

sync_status_description

Synchronization status:

not syncing: Incremental data synchronization is not active.
data copying: The initial full data copy is in progress.
data catchup: Applying incremental data generated during the full data copy.
data syncing: Incremental synchronization is in progress.

sync_error_description

Reason for inactive synchronization:

no errors: No issues were detected.
dml replay conflict: Incremental replay conflict caused by a DML operation.
ddl replay conflict: Incremental replay conflict caused by a DDL operation.
no primary key or replica identity index: The source table lacks a primary key or REPLICA IDENTITY index.
unsupported relation type: The table type is unsupported (e.g., partitioned tables, views, materialized views).
rds_duckdb.enable_sync not set: The global synchronization GUC (Grand Unified Configuration) parameter is not enabled.

Note
Enabled by default on PostgreSQL 15 with minor engine version 20250228 or later.
removing duckdb table: The column-oriented table is being dropped.

View SQL execution plans

Use EXPLAIN to compare execution plans with and without AP acceleration:

Execution plan with AP acceleration enabled:

AP query acceleration enabled

tpch_10x=# SET rds_duckdb.execution = on;
SET
tpch_10x=# EXPLAIN SELECT
tpch_10x-#     100.00 * sum(
tpch_10x(#         CASE WHEN p_type LIKE 'PROMO%' THEN
tpch_10x(#             l_extendedprice * (1 - l_discount)
tpch_10x(#         ELSE
tpch_10x(#             0
tpch_10x(#         END) / sum(l_extendedprice * (1 - l_discount)) AS promo_revenue
tpch_10x-# FROM
tpch_10x-#     lineitem,
tpch_10x-#     part
tpch_10x-# WHERE
tpch_10x-#     l_partkey = p_partkey
tpch_10x-#     AND l_shipdate >= date '1995-09-01'
tpch_10x-#     AND l_shipdate < CAST('1995-10-01' AS date);
                         QUERY PLAN
------------------------------------------------------------
 Custom Scan (DuckDBNode)  (cost=0.00..0.00 rows=0 width=0)
   DuckDB Plan:

 ┌───────────────────────────┐
 │         PROJECTION        │
 │   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
 │        Projections:       │
 │       promo_revenue       │
 │   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
 │   Estimated Cardinality:  │
 │             1             │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │    UNGROUPED_AGGREGATE    │
 │   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
 │        Aggregates:        │
 │          sum(#0)          │
 │          sum(#1)          │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │         PROJECTION        │
 │   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
 │        Projections:       │
 │ CASE  WHEN (prefix(p_type,│
 │    'PROMO')) THEN (CAST(  │
 │ (l_extendedprice * (1.000 │
 │    - CAST(l_discount AS   │
 │     DECIMAL(18,3)))) AS   │
 │   DECIMAL(20,5))) ELSE 0  │
 │         .00000 END        │
 │ (l_extendedprice * (1.000 │
 │    - CAST(l_discount AS   │
 │      DECIMAL(18,3))))     │
 │   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
 │   Estimated Cardinality:  │
 │          6600339          │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │         HASH_JOIN         │
 │   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
 │         Join Type:        │
 │           INNER           │
 │   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
 │        Conditions:        ├──────────────┐
 │   l_partkey = p_partkey   │              │
 │   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │              │
 │   Estimated Cardinality:  │              │
 │          6600339          │              │
 └─────────────┬─────────────┘              │
 ┌─────────────┴─────────────┐┌─────────────┴─────────────┐
 │         SEQ_SCAN          ││         SEQ_SCAN          │
 │   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   ││   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
 │        Stringified:       ││        Stringified:       │
 │          lineitem         ││            part           │
 │   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   ││   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
 │        Projections:       ││        Projections:       │
 │         l_partkey         ││         p_partkey         │
 │      l_extendedprice      ││           p_type          │
 │         l_discount        ││   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
 │   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   ││   Estimated Cardinality:  │
 │          Filters:         ││          2000000          │
 │ l_shipdate>='1995-09-01': ││                           │
 │ :DATE AND l_shipdate<'1995││                           │
 │     -10-01'::DATE AND     ││                           │
 │   l_shipdate IS NOT NULL  ││                           │
 │   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   ││                           │
 │   Estimated Cardinality:  ││                           │
 │          11997210         ││                           │
 └───────────────────────────┘└───────────────────────────┘


(71 rows)

Execution plan with AP acceleration disabled:

AP query acceleration disabled

tpch_10x=# SET rds_duckdb.execution = off;
SET
tpch_10x=# EXPLAIN SELECT
    100.00 * sum(
        CASE WHEN p_type LIKE 'PROMO%' THEN
            l_extendedprice * (1 - l_discount)
        ELSE
            0
        END) / sum(l_extendedprice * (1 - l_discount)) AS promo_revenue
FROM
    lineitem,
    part
WHERE
    l_partkey = p_partkey
    AND l_shipdate >= date '1995-09-01'
    AND l_shipdate < CAST('1995-10-01' AS date);
                                                     QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=1286740.42..1286740.43 rows=1 width=32)
   ->  Gather  (cost=1286739.96..1286740.37 rows=4 width=64)
         Workers Planned: 4
         ->  Partial Aggregate  (cost=1285739.96..1285739.97 rows=1 width=64)
               ->  Parallel Hash Join  (cost=1235166.04..1282419.39 rows=189747 width=33)
                     Hash Cond: (part.p_partkey = lineitem.l_partkey)
                     ->  Parallel Seq Scan on part  (cost=0.00..43232.15 rows=500016 width=29)
                     ->  Parallel Hash  (cost=1233776.40..1233776.40 rows=111171 width=20)
                           ->  Parallel Seq Scan on lineitem  (cost=0.00..1233776.40 rows=111171 width=20)
                                 Filter: ((l_shipdate >= '1995-09-01'::date) AND (l_shipdate < '1995-10-01'::date))
 JIT:
   Functions: 17
   Options: Inlining true, Optimization true, Expressions true, Deforming true
(13 rows)

Performance testing

TPC-H benchmark results in a Linux environment are available in Performance test for the rds_duckdb extension.