ApsaraDB RDS:How to accelerate queries with DuckDB?

Step 1: Enable DuckDB acceleration

To offload SELECT queries to DuckDB, use one of the following methods.

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

   Add a hint to a SELECT statement to accelerate only that query. This method is ideal for temporary validation or accelerating a specific slow query:

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

2. Method 2: Set a session-level parameter

   Run the following command in the current session. All eligible queries within this session are then offloaded to DuckDB:

   SET rds_duckdb.execution = on;

Step 2: Verify offloading

Use the execution plan to verify if a SQL statement was offloaded to DuckDB.

1. Example 1: A single-table query is offloaded 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 plan reverts to the native PostgreSQL plan after offloading 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-level messages are sent to the client. This helps you identify why a query was not offloaded to DuckDB.

1. Enable WARNING output. This change takes effect immediately at the session level.

   SET rds_duckdb.enable_log_warning = on;

2. Observe the client output. When enable_log_warning = on, a WARNING is output in the following scenarios:

   • The query 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 processed by DuckDB: Statements don't need to be handed over to DuckDB, fallback to PG.

When enable_log_warning = off (default), this information is written to the log at the DEBUG1 level and does not appear on the client.

4.1 Automatic DDL synchronization

The rds_duckdb extension can automatically synchronize table structure changes (DDL) from ApsaraDB RDS for PostgreSQL to DuckDB.

Supported DDL types:

-- 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 of a column
ALTER TABLE tbl ALTER COLUMN extra_1 DROP DEFAULT;
-- Modify the data type of a column
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 with a savepoint
BEGIN;
SAVEPOINT s1;
ALTER TABLE tbl DROP COLUMN extra_1;
ROLLBACK TO SAVEPOINT s1;
COMMIT;

Examples of unsupported DDL operations that trigger a fallback:

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

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

After you delete the primary key, the status of the table in duckdb_sync_stat changes to not syncing, and queries are no longer routed through DuckDB. This state persists until you restore the primary key or REPLICA IDENTITY and manually refresh the table.

4.2 Partitioned table synchronization

The rds_duckdb extension can synchronize PostgreSQL partitioned tables, including single-level and multi-level partitions, to DuckDB.

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

-- Create a PostgreSQL partitioned table.
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 querying the root partition:

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

Expected plan (pruned to scan only the matching partitions):

 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, as shown in the following 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 all partition 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
}');

4.3 Automatic DuckDB table creation

In addition to manually calling create_duckdb_table(), rds_duckdb supports automatic creation. When this feature is enabled, the system automatically creates a corresponding DuckDB table and starts incremental synchronization if you execute CREATE TABLE in PostgreSQL and the synchronization conditions are met.

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

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

-- Directly query the DuckDB data.
/*+ set(rds_duckdb.execution on) */ SELECT * FROM auto_tbl;

Expected result (if 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 currently supported.

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

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

4.4 Autonomous fallback

The rds_duckdb.enable_fallback parameter controls the system's behavior when a SQL statement cannot be executed by DuckDB:

• on (default): Automatically falls back to PostgreSQL for execution. You can use enable_log_warning to view the reason for the fallback.

• off: Throws an error directly, which forces the issue to be addressed.

The extension also supports fallback based on synchronization latency. The rds_duckdb.wait_sync_timeout parameter controls how long a query waits for the incremental synchronization position before timing out. The unit is milliseconds.

Triggering scenario: In environments with frequent write operations and high synchronization latency, queries that require strong consistency may fall back due to a timeout.

Log example (enable_log_warning = on):

WARNING:  Fallback postgres due to waiting for incremental synchronization timeout

If enable_fallback = off, an error is reported:

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

Check latency:

-- Check the synchronization latency.
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%';

4.5 Execution cost threshold (plan_cost_threshold)

The rds_duckdb.plan_cost_threshold parameter ensures that only queries with a sufficiently high estimated cost are forwarded to DuckDB for execution, preventing simple queries from slowing down due to the overhead of forwarding and startup.

Typical use case:

• Your workload includes both complex analytical SQL queries (involving joins, aggregations, and large scans) and simple point queries or small-range scans. The simple queries often run faster in PostgreSQL because of the fixed overhead of offloading and connection initialization in DuckDB.

• Setting a reasonable threshold, such as 1000 or 10000, allows low-cost, simple queries to continue running in PostgreSQL, while only pushing down high-cost, large queries to DuckDB for acceleration.

Configuration example:

-- Set the cost threshold to 5000. Queries are offloaded to DuckDB only if their PostgreSQL plan cost exceeds 5000.
SET rds_duckdb.plan_cost_threshold = 5000;

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

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

Why isn't my query offloaded?

Use the rds_duckdb.enable_fallback parameter to determine why a query was not offloaded to DuckDB.

Troubleshooting steps:

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

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

Potential errors and their meanings:

Summary of common fallback scenarios:

You can query the rds_duckdb.duckdb_sync_stat view to check the synchronization status of each DuckDB table. This action requires a privileged account. 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 have not been incrementally synchronized, you can set the rds_duckdb.query_syncing_table = off parameter.