ApsaraDB RDS:AP acceleration engine (rds_duckdb)

Step 1: Enable DuckDB acceleration

Use one of the following methods to forward SELECT queries to DuckDB:

1. Method 1: Use a hint (statement level)

   Add a hint before the SELECT statement. The hint takes effect only on the statement, which is suitable for temporary verification or accelerating a single slow query:

   /*+ set(rds_duckdb.execution on) */ SELECT * FROM my_table WHERE id = 1;

2. Method 2: Configure a session-level parameter

   Run the following command in the current session. All eligible queries in the session are pushed down to DuckDB:

   SET rds_duckdb.execution = on;

Step 2: Verify query forwarding (EXPLAIN / EXPLAIN ANALYZE)

Check the execution plan to determine whether a SQL statement is forwarded to DuckDB.

1. Example 1: A single-table query is forwarded to DuckDB

   /*+ set(rds_duckdb.execution on) */ EXPLAIN SELECT * FROM test_hint;

   The expected plan contains Custom Scan (DuckDBScan) and DuckDB Execution Plan:

                            QUERY PLAN
   ------------------------------------------------------------
    Custom Scan (DuckDBScan)  (cost=0.00..0.00 rows=0 width=0)
      DuckDB Execution Plan:
   
    ┌───────────────────────────┐
    │         SEQ_SCAN          │
    │    ────────────────────   │
    │      Table: test_hint     │
    │   Type: Sequential Scan   │
    │       Projections: a      │
    │                           │
    │          ~0 Rows          │
    └───────────────────────────┘

2. Example 2: The native PostgreSQL plan is restored after forwarding is disabled

   /*+ set(rds_duckdb.execution off) */ EXPLAIN SELECT * FROM test_hint;

   Expected plan:

         QUERY PLAN
   -----------------------
    Seq Scan on test_hint
   (1 row)

Step 3: View DuckDB logs (optional)

The rds_duckdb.enable_log_warning parameter controls whether WARNING messages are returned to the client. This helps you identify why a query is not executed by DuckDB.

1. Enable WARNING output (takes effect immediately at the session level):

   SET rds_duckdb.enable_log_warning = on;

2. Check the client output. When enable_log_warning = on, WARNING messages are returned in the following scenarios:

   • The SQL statement falls back to PostgreSQL: Fallback postgres due to ...

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

   • The statement does not need to be executed by DuckDB: Statements don't need to be handed over to DuckDB, fallback to PG.

When enable_log_warning = off (default), the preceding messages are written to logs at the DEBUG1 level and are not returned to the client.

Automatic DDL synchronization

Schema changes (DDL) in ApsaraDB RDS for PostgreSQL can be automatically synchronized to DuckDB. The following parameters control the synchronization:

Verified supported DDL operations:

-- Add a column
ALTER TABLE tbl ADD COLUMN extra_1 int;
-- Drop a column
ALTER TABLE tbl DROP COLUMN extra_1;
-- Add a column with a default value
ALTER TABLE tbl ADD COLUMN extra_1 int DEFAULT 0;
-- Drop the default value
ALTER TABLE tbl ALTER COLUMN extra_1 DROP DEFAULT;
-- Change the column type
ALTER TABLE tbl ALTER COLUMN extra_1 TYPE varchar;
-- Rename a column
ALTER TABLE tbl RENAME COLUMN extra_1 TO extra_2;
-- Rename a table
ALTER TABLE tbl RENAME TO tbl_new;
-- DDL within a transaction
BEGIN;
ALTER TABLE tbl ADD COLUMN extra_3 int;
INSERT INTO tbl VALUES (..., 1);
COMMIT;
-- Roll back DDL by using a savepoint
BEGIN;
SAVEPOINT s1;
ALTER TABLE tbl DROP COLUMN extra_1;
ROLLBACK TO SAVEPOINT s1;
COMMIT;

Examples of unsupported DDL operations that trigger fallback:

-- Change the schema of a table (triggers an automatic full refresh or a conflict based on the fail_action setting)
ALTER TABLE tbl SET SCHEMA nsp1;

-- Drop the primary key (interrupts synchronization, and queries fall back to PostgreSQL)
ALTER TABLE tbl DROP CONSTRAINT tbl_pkey;

After the primary key is dropped, the table enters the not syncing state in duckdb_sync_stat and queries are no longer executed by DuckDB until the primary key or REPLICA IDENTITY is restored and the table is manually refreshed.

Partitioned table synchronization

rds_duckdb can synchronize PostgreSQL partitioned tables (single-level and multi-level) to DuckDB.

To synchronize a PostgreSQL partitioned table, you must create DuckDB tables for the root partition and all leaf partitions. The following example shows a single-level partitioned table:

-- Create a partitioned table in PostgreSQL
CREATE TABLE test_partition (
    id int,
    age int,
    primary key (id, age)
) PARTITION BY RANGE (age);

CREATE TABLE test_partition_a PARTITION OF test_partition FOR VALUES FROM (0) TO (18);
CREATE TABLE test_partition_b PARTITION OF test_partition FOR VALUES FROM (18) TO (30);
CREATE TABLE test_partition_c PARTITION OF test_partition FOR VALUES FROM (30) TO (60);

INSERT INTO test_partition SELECT i, i FROM generate_series(0, 59) i;

-- Synchronize the root partition and all leaf partitions to DuckDB
SELECT rds_duckdb.create_duckdb_tables('{test_partition, test_partition_a, test_partition_b, test_partition_c}');

Example of a query on the root partition:

/*+ set(rds_duckdb.execution on) */ EXPLAIN SELECT * FROM test_partition WHERE age >= 10 AND age < 20;

Expected plan (only the hit partitions are scanned after pruning):

 Custom Scan (DuckDBScan)  (cost=0.00..0.00 rows=0 width=0)
   DuckDB Execution Plan:

 ┌───────────────────────────┐
 │           UNION           ├──────────────┐
 └─────────────┬─────────────┘              │
 ┌─────────────┴─────────────┐┌─────────────┴─────────────┐
 │         SEQ_SCAN          ││         SEQ_SCAN          │
 │           Table:          ││           Table:          │
 │      test_partition_a     ││      test_partition_b     │
 │   Type: Sequential Scan   ││   Type: Sequential Scan   │
 │       Projections:        ││       Projections:        │
 │          Filters:         ││          Filters:         │
 │     age>=10 AND age<20││     age>=10 AND age<20│
 └───────────────────────────┘└───────────────────────────┘

Multi-level partitioned tables are also supported. Example:

CREATE TABLE test_multi_partition (
    id serial,
    sale_id int NOT NULL,
    sale_date date NOT NULL,
    amount numeric(15,2) NOT NULL,
    primary key(sale_id, sale_date)
) PARTITION BY RANGE (sale_date);

CREATE TABLE test_multi_partition_a_l1 PARTITION OF test_multi_partition
    FOR VALUES FROM ('2024-1-1') TO ('2025-1-1') PARTITION BY RANGE (sale_date);
CREATE TABLE test_multi_partition_b_l1 PARTITION OF test_multi_partition
    FOR VALUES FROM ('2025-1-1') TO ('2026-1-1') PARTITION BY RANGE (sale_date);

CREATE TABLE test_multi_partition_a_l2_1 PARTITION OF test_multi_partition_a_l1
    FOR VALUES FROM ('2024-1-1') TO ('2024-7-1');
CREATE TABLE test_multi_partition_a_l2_2 PARTITION OF test_multi_partition_a_l1
    FOR VALUES FROM ('2024-7-1') TO ('2025-1-1');
CREATE TABLE test_multi_partition_b_l2_1 PARTITION OF test_multi_partition_b_l1
    FOR VALUES FROM ('2025-1-1') TO ('2025-7-1');
CREATE TABLE test_multi_partition_b_l2_2 PARTITION OF test_multi_partition_b_l1
    FOR VALUES FROM ('2025-7-1') TO ('2026-1-1');

INSERT INTO test_multi_partition (sale_id, sale_date, amount)
SELECT (random() * 100)::int, '2024-01-1'::date + i, (random() * 1000)::numeric(15,2)
FROM generate_series(1, 730) i;

-- Synchronize the partitions at all levels
SELECT rds_duckdb.create_duckdb_tables('{
    test_multi_partition,
    test_multi_partition_a_l1, test_multi_partition_b_l1,
    test_multi_partition_a_l2_1, test_multi_partition_a_l2_2,
    test_multi_partition_b_l2_1, test_multi_partition_b_l2_2
}');

Automatic DuckDB table creation

In addition to manually calling create_duckdb_table(), you can use rds_duckdb to automatically create DuckDB tables. After automatic creation is enabled, when you run CREATE TABLE in PostgreSQL and the synchronization conditions are met, the system automatically creates the corresponding DuckDB table and starts incremental synchronization.

-- Enable automatic creation (USERSET level; can be controlled at the session level)
SET rds_duckdb.auto_create_duckdb_table = on;

-- Create a table in PostgreSQL, and the DuckDB table is automatically created
CREATE TABLE auto_tbl(id int primary key, val text);
INSERT INTO auto_tbl VALUES (1, 'hello');

-- Query DuckDB data directly
/*+ set(rds_duckdb.execution on) */ SELECT * FROM auto_tbl;

Expected result (when synchronization is normal):

  sync_table     | sync_status_description | sync_error_description
-----------------+-------------------------+------------------------
 public.auto_tbl | data syncing            | no errors

Behavior notes:

• This feature applies only to regular tables. Automatic creation for partitioned tables is not supported in the current version.

• The table must have a primary key or REPLICA IDENTITY. Otherwise, the table cannot enter the data syncing state.

• Automatic creation is not triggered if no DuckDB table has been created in the current database.

Fallback policy

When a SQL statement does not meet the conditions for execution by DuckDB, the rds_duckdb.enable_fallback parameter determines the behavior:

• on (default): The query automatically falls back to PostgreSQL. You can configure enable_log_warning to view the fallback reason.

• off: An error is directly returned to expose the issue.

In addition, fallback is supported for excessive incremental synchronization delays. The rds_duckdb.wait_sync_timeout parameter specifies the maximum period of time that a query waits for the incremental synchronization position. Unit: milliseconds:

Trigger scenario: If writes are frequent and synchronization lag is high, queries that require strong consistency may fall back due to a wait timeout.

Log example (enable_log_warning = on):

WARNING:  Fallback postgres due to waiting for incremental synchronization timeout

If enable_fallback = off, an error is returned:

ERROR:  RDS DuckDB: canceling statement due to waiting for incremental synchronization timeout

Verify synchronization lag:

-- View synchronization lag
SELECT slot_name, pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) AS lag_bytes
FROM pg_replication_slots
WHERE slot_name LIKE 'rds_duckdb_slot%';

Execution cost threshold (plan_cost_threshold)

The rds_duckdb.plan_cost_threshold parameter ensures that only queries whose estimated costs are sufficiently high are forwarded to DuckDB. This prevents simple queries from running slower due to the forwarding and startup overhead of DuckDB.

Typical scenarios:

• An online workload contains both complex analytical SQL statements (JOINs, aggregations, and large scans) and simple point queries or small-range scans. The latter run faster in PostgreSQL because DuckDB incurs fixed overhead for forwarding and connection initialization.

• If you set an appropriate threshold, such as 1000 or 10000, low-cost simple queries continue to run in PostgreSQL, and only high-cost heavy queries are pushed down to DuckDB for acceleration.

Configuration example:

-- Set the cost threshold to 5000. Only queries whose PostgreSQL plan costs exceed 5000 are forwarded to DuckDB
SET rds_duckdb.plan_cost_threshold = 5000;

-- Example 1: A simple point query whose PostgreSQL plan cost is low remains in PostgreSQL
/*+ set(rds_duckdb.execution on) */ EXPLAIN SELECT * FROM my_table WHERE id = 1;

-- Example 2: A complex aggregation query whose PostgreSQL plan cost is high is forwarded to DuckDB
/*+ set(rds_duckdb.execution on) */ EXPLAIN SELECT count(*), avg(amount) FROM my_table GROUP BY region;

Why is a query not executed by DuckDB?

The rds_duckdb.enable_fallback parameter helps you identify the specific reason why a query is not executed by DuckDB.

Troubleshooting steps:

-- Step 1: Disable fallback to expose the actual cause
SET rds_duckdb.enable_fallback = off;

-- Step 2: Run the target SQL statement
/*+ set(rds_duckdb.execution on) */ EXPLAIN SELECT * FROM my_table;

Possible errors and meanings:

Common fallback scenarios:

Query the synchronization status of each DuckDB table by using the view rds_duckdb.duckdb_sync_stat. A privileged account is required. Example:

SELECT sync_table,
       sync_status_description,
       sync_error_description
FROM rds_duckdb.duckdb_sync_stat;

 sync_table        | sync_status_description | sync_error_description
-------------------+-------------------------+------------------------------------------
 test_schema.test1 | not syncing             | no primary key or replica identity index

If you want to query data from tables that are not incrementally synchronized, set the rds_duckdb.query_syncing_table = off parameter.