This topic describes how to try the AP acceleration engine (rds_duckdb) for RDS PostgreSQL for free in the ApsaraDB Solution Center to experience the effects of query acceleration in complex scenarios.
Background
With the built-in DuckDB, an embedded columnar acceleration engine, you can use SQL commands to accelerate complex queries and perform Extract, Transform, and Load (ETL) functions. This engine is ideal for scenarios that require lightweight data analysis of online data, such as for real-time reporting and accelerated ETL computations. The key advantages include:
High performance: DuckDB is a popular embedded analytical processing (AP) database. It delivers higher query efficiency and lower CPU utilization by using technologies such as columnar storage, just-in-time (JIT) compilation, vectorized execution, efficient memory management, and parallel processing.
Seamless integration: RDS PostgreSQL has DuckDB built in, enabling seamless internal data flow. It supports multiple data synchronization and refresh mechanisms to ensure real-time, transaction-level consistency between row storage and columnar storage. The PostgreSQL engine parses and processes all SQL commands in a unified way, which allows you to perform data analysis by using native PostgreSQL.
Low cost: You can perform data analysis directly on your RDS PostgreSQL instance without purchasing separate analytical nodes. In data analysis scenarios, columnar storage uses approximately one-sixth the storage space of row storage.
Impact on your environment
In this free trial, all resources are provided by Alibaba Cloud and are separate from your own. Therefore, this trial does not affect your business.
Billing
The resources for this trial are provided by Alibaba Cloud free of charge. The AP acceleration engine (rds_duckdb) for RDS PostgreSQL is also free to use in production environments.
Trial details
Trial environment
This free trial provides a pre-configured environment with the following specifications:
An RDS PostgreSQL instance with the following specifications:
Major version: RDS PostgreSQL 16
Kernel minor version: rds_postgres_1600_20241030
Product series: high-availability edition
Instance type: 4 cores, 16 GB (pg.n4m.4c.2m)
Storage type: ESSD
Test dataset: The instance is pre-loaded with the 10 GB TPC-H standard test dataset.
Metrics to observe
CPU utilization, in percent (%).
Query latency, in seconds (s).
Procedure
Go to the RDS PostgreSQL Columnar Index/Table - Accelerate Complex TP SQL Queries trial page.
At the bottom of the page, click Create Free Trial Task.
In the dialog box that appears, click OK.
After a moment, the trial task that you created appears.
Click View Details to go to the real-time query trial page. Follow the on-screen instructions to begin.
You can run the following tasks to compare query efficiency with and without AP acceleration.
Single-table Statistics: Normal Query
Single-table Statistics: Accelerated Query
Multi-table Join: Normal Query
Multi-table Join: Accelerated Query
Follow the on-screen prompts and click the buttons to run the related SQL statements. If you do not click a button before the countdown ends, the system automatically runs the corresponding operation. This allows you to compare the query efficiency with and without columnar acceleration.
While the SQL statements are running, you can monitor the CPU utilization of the RDS PostgreSQL instance in the trend chart at the bottom of the page.
NoteDue to potential delays in real-time monitoring data, the trend chart continues to display data for approximately 2 to 3 seconds after a query is complete. This ensures that the chart displays the complete change in CPU utilization.
(Optional) To view the results and details of completed trial tasks, go to the RDS PostgreSQL Columnar Index/Table - Accelerate Complex TP SQL Queries page, click Trial Records, and then click All Tasks or My Tasks in the task list.
Result analysis
As shown in the following figures, for both single-table statistical queries and multi-table JOIN queries:
Queries with columnar acceleration enabled have lower query latency and are more efficient than normal queries.
Queries with columnar acceleration enabled have lower CPU utilization, indicating that they consume fewer instance resources than normal queries.
Single-table statistical query
Multi-table JOIN query
This trial demonstrates the following advantages of the AP acceleration engine (rds_duckdb) for RDS PostgreSQL:
Easy to use: You only need to install an extension and enable it.
High compatibility: You can use standard PostgreSQL syntax to run accelerated queries.
High performance: For complex SQL queries, the AP acceleration engine (rds_duckdb) achieves a performance improvement of more than 30 times.
Related documentation
For an overview of the AP acceleration engine (rds_duckdb) and instructions on how to use it for free in an RDS PostgreSQL database, see How to accelerate queries by using DuckDB?.
Contact experts
Join the DingTalk group for RDS PostgreSQL extensions (Group ID: 103525002795) to ask questions, share feedback, and learn more about the extensions.