AP acceleration engine (rds_duckdb) - ApsaraDB RDS - Alibaba Cloud Documentation Center

The rds_duckdb extension for ApsaraDB RDS for PostgreSQL accelerates complex analytical queries. By providing column-oriented tables and vectorization, it significantly improves query performance without requiring changes to your original SQL statements.

For questions, discussions, or feedback about this extension, join the ApsaraDB RDS for PostgreSQL Extensions DingTalk group (ID: 103525002795).

Overview

The rds_duckdb extension integrates the efficient, resource-friendly DuckDB into ApsaraDB RDS for PostgreSQL to enhance its analytical query capabilities. This extension lets you export local tables into column-oriented tables and enable AP query acceleration.

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.
For more information about how to configure parameters, see Set instance parameters. For example, you can change 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

Run the following command to export a local table from your ApsaraDB RDS for PostgreSQL instance into a column-oriented table. Local tables can include user tables, materialized views, or foreign tables.

Note

If your instance runs PostgreSQL 15 with minor engine version 20250228 or later, automatic incremental synchronization for column-oriented tables is enabled by default. Before creating a column-oriented table, configure the target ApsaraDB RDS for PostgreSQL instance and local table for this feature. For more information, see Configure automatic incremental synchronization for column-oriented tables.

SELECT rds_duckdb.create_duckdb_table('local_table_name');

Refresh a column-oriented table

Run the following command to refresh the exported column-oriented table with the latest data from the source local table. This command updates both the table schema and its data.

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 AP query acceleration is enabled, any query that exclusively uses tables with corresponding DuckDB column-oriented tables is offloaded to DuckDB. If a query involves unsupported operations, such as DML or DDL, or references tables without a column-oriented counterpart, it automatically falls back to the standard ApsaraDB RDS for PostgreSQL engine.

For queries that fall back to the PostgreSQL engine, the system returns a warning in the following format: WARNING: Trying to execute an operation with non-duckdb tables(test), fallback to PG. The tables listed in parentheses are those that lack a corresponding DuckDB column-oriented table.

Non-read-only queries also trigger a warning: 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

Adjust parameters in your session to control the performance of AP query acceleration. For example:

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.

For optimal performance, set this value to the number of available CPU cores.
A higher value increases CPU load. Adjust this setting based on your workload.
A higher value for this parameter provides better performance but also increases the CPU load. Conversely, a lower value reduces performance but also decreases the CPU load.

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

For optimal performance, set this value as high as your workload allows.
A higher value increases memory consumption. Adjust this setting based on your workload.
The default value is conservative. Adjust it based on your instance's resources.
A low value can degrade performance during AP query acceleration and large table exports.

Note

For more information about DuckDB parameters, see the official DuckDB documentation.

Disable AP acceleration

SET rds_duckdb.execution = off;

Configure auto-sync for column-oriented tables

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

The synchronization status of the column-oriented table.

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 incremental synchronization.

no errors: No issues were detected.
dml replay conflict: An incremental replay conflict occurred due to a DML operation.
ddl replay conflict: An incremental replay conflict occurred due to a DDL operation.
no primary key or replica identity index: The source PostgreSQL table lacks a primary key or a REPLICA IDENTITY index.
unsupported relation type: The table type is not supported for synchronization. Examples include partitioned tables, views, and materialized views.
rds_duckdb.enable_sync not set: The global synchronization GUC (Grand Unified Configuration) parameter is not enabled.
Note
This is enabled by default for instances that run PostgreSQL 15 with minor engine version 20250228 or later.
removing duckdb table: The column-oriented table is being dropped.

View SQL execution plans

Use the EXPLAIN statement to compare the execution plans of a SQL query with and without AP query acceleration enabled. For example:

The following example shows the execution plan when AP query acceleration is 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)

The following example shows the execution plan when AP query acceleration is 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

To evaluate the performance improvements that rds_duckdb provides for complex queries, refer to our standard TPC-H benchmark tests conducted in a Linux environment. For detailed procedures and results, see Performance test for the rds_duckdb extension.