When you need fast results from complex analytical queries on RDS PostgreSQL, use the rds_duckdb acceleration engine. Built on DuckDB, it provides column-oriented storage and vectorized execution without requiring changes to your SQL.
Overview
RDS PostgreSQL uses row-based storage by default, which suits high-frequency point lookups and updates in online transactional processing (OLTP) scenarios but is limited when Online Analytical Processing (OLAP) queries need to scan large volumes of data. The rds_duckdb extension integrates DuckDB into RDS PostgreSQL to accelerate complex queries with the following capabilities:
-
Column-oriented storage: Data is organized and stored by column. For queries that aggregate data from a subset of columns, this method reads only the relevant columns, which significantly reduces I/O overhead.
-
Vectorized execution: Data is processed in batches, making full use of CPU SIMD instructions and cache locality to deliver several to tens of times better performance than traditional tuple-at-a-time execution (Volcano model).
-
Non-intrusive usage: Your original SQL statements require no modification, allowing you to smoothly migrate your application to the accelerated path.
Use cases
-
Complex reporting and ad-hoc queries that involve multi-table JOINs, GROUP BY clauses, and aggregate computation.
-
Data analysis and statistical scenarios where a single table is large and queries involve only a subset of its columns.
-
Hybrid Transactional/Analytical Processing (HTAP) workload scenarios where you need to support both online transactions and analytical queries on the same dataset.
Enablement options
Before you use analysis acceleration, configure your instance by choosing one of the following options based on your workload:
|
Enablement method |
Resource mode |
Recommended scenarios |
Documentation |
|
Enable analysis acceleration on the primary instance |
PostgreSQL and DuckDB share the resources of the primary instance. This option is simple to deploy and cost-effective. |
For businesses with a light TP workload or those that can allow AP queries to consume some resources of the primary instance. This is also ideal for quickly validating the acceleration performance. |
|
|
DuckDB-based analysis instance |
A dedicated DuckDB-based analysis instance is deployed separately and performs data synchronization with the primary instance. This provides complete physical resource isolation between AP and TP workloads. |
For core businesses that are sensitive to online service latency and require guaranteed TP stability while handling large-scale AP queries. |
If you are unsure which option to choose, we recommend starting with the DuckDB-based analysis instance. This approach isolates the analytical workload from online transactions, which prevents AP queries from affecting the performance and stability of your core business.
How to use
After you enable analysis acceleration, use the rds_duckdb extension to create column-oriented tables, import data, and run queries. For detailed syntax and operations, see AP acceleration engine (rds_duckdb).
Performance test
You can evaluate rds_duckdb acceleration by using the standard TPC-H benchmark, which covers test data preparation, query execution, and result comparison. For more information, see AP acceleration engine (rds_duckdb) performance test.
Free trial
Try rds_duckdb at no cost with the free trial, which lets you validate AP query acceleration without purchasing additional resources. For more information, see Free trial of RDS PostgreSQL AP acceleration engine (rds_duckdb).