Amazon has unveiled an innovative capability for Amazon Aurora PostgreSQL, enabling users to seamlessly query operational data alongside data stored in data lakes formatted in Apache Iceberg and Apache Parquet. This advancement allows developers to leverage their existing PostgreSQL applications and tools without the cumbersome process of extracting, transforming, and loading (ETL) structured data from data lakes into operational databases. The result is a significant reduction in operational complexity, streamlining application development.
With this new functionality, Aurora PostgreSQL can directly access data from Iceberg REST Catalog (IRC)-compatible catalogs, facilitating a unified approach to analytics across various systems without the need for data duplication or relocation. This capability is particularly beneficial for real-time dashboards, transaction enrichment with historical context, and the development of AI agents that require access to both live and archived data—all achievable through a single, familiar interface.
Enhanced Querying with DuckDB Integration
Previously, applications that required the integration of recent transactional data from Aurora with historical records stored in Amazon S3 often relied on reverse ETL pipelines. These pipelines not only duplicated data but also escalated infrastructure costs and necessitated ongoing engineering efforts for synchronization. This complexity is further amplified when incorporating AI agents, as it becomes impractical to predict and pre-replicate every dataset an agent might require.
The recent acquisition of DuckLabs, the team behind the DuckDB project, has paved the way for this capability. DuckDB is now embedded within Aurora PostgreSQL, allowing users to query live operational data—including uncommitted writes—alongside data from their data lakes in a single query. This integration ensures that query processing remains within Aurora, eliminating additional network hops and the need for ETL pipelines that duplicate data. Users can query Apache Iceberg tables managed through the AWS Glue Data Catalog, as well as Parquet and Iceberg data stored in Amazon S3, all while utilizing familiar PostgreSQL syntax.
By embedding DuckDB into Aurora PostgreSQL, Amazon is excited to offer enhanced speed and simplicity, enabling users and their agents to query and combine operational and Iceberg data through existing PostgreSQL applications and tools. This architecture not only facilitates immediate benefits but also positions Aurora to benefit from future enhancements to the open-source DuckDB engine, ensuring ongoing performance and functionality improvements across AWS services.
Getting Started with Direct Querying
This new capability is available on two major versions of Aurora PostgreSQL: 17 (starting with 17.11) and 18 (starting with 18.6). To utilize this feature, users must create an Aurora PostgreSQL cluster, attach an IAM role with the AuroraAnalytics feature, and enable the aurora_analytics extension. The IAM role grants Aurora access to data stored in Amazon S3 and the AWS Glue Data Catalog. Users can then create foreign tables that reference their Iceberg or Parquet data in the data lake, querying them using standard PostgreSQL syntax. This setup can be accomplished via the Amazon RDS console or any PostgreSQL client, such as psql, with comprehensive documentation available in the Aurora PostgreSQL documentation.
Users can also query data across external IRC-compatible catalogs through AWS Glue Data Catalog federation. By registering the external catalog once with Glue, users can create foreign tables for the tables they wish to query, similar to how they would for any Glue-native table. This allows for a single query to join data stored in Aurora with Iceberg tables registered across multiple catalogs, providing a unified view without the need to move data or replace existing catalog investments.
Aurora optimizes query performance through techniques such as predicate pushdown and column pruning, ensuring that only relevant data is read, which is crucial as the underlying data grows. Frequently accessed data is cached within the Aurora instance, resulting in faster subsequent queries against the same data. Users can monitor this behavior per query using aurora_analytics_stat_statements(), which provides metrics such as rows scanned, bytes read from Amazon S3, and cache hits.
To illustrate the direct querying process, I connected to my Aurora PostgreSQL database using psql and created the extension:
CREATE EXTENSION aurora_analytics;For demonstration purposes, I set up a straightforward financial scenario involving a recent_transactions table in Aurora containing the last seven days of customer transactions and a Parquet file in Amazon S3 with five years of historical transaction data. To integrate the historical data into Aurora, I created a foreign table pointing to the Parquet file in S3:
CREATE FOREIGN TABLE transaction_history ()
SERVER aurora_analytics_server
OPTIONS (
location 's3:///finance/transaction_history.parquet',
format 'parquet'
);Notably, the empty parentheses in the CREATE FOREIGN TABLE statement indicate that Aurora automatically reads the schema from the Parquet file metadata, eliminating the need for manual column definitions. For scenarios involving numerous tables, users can expedite the process by employing a single IMPORT FOREIGN SCHEMA statement to bulk-create foreign tables for all Iceberg or Parquet tables within an AWS Glue Data Catalog database, with schema inference handled automatically.
Once both tables were established, I executed a single query that combined the recent operational data in Aurora with the historical data in S3:
SELECT merchant, category, amount, transaction_date, 'recent' AS source
FROM recent_transactions
WHERE customer_id = 'C-1001'
UNION ALL
SELECT merchant, category, amount, transaction_date, 'historical' AS source
FROM transaction_history
WHERE customer_id = 'C-1001'
AND transaction_date >= CURRENT_DATE - INTERVAL '5 years'
ORDER BY transaction_date DESC
LIMIT 15;The resulting output displayed both recent and historical transactions within a single result set. The seven most recent rows originated from Aurora, while the remainder came directly from the Parquet file in S3. DuckDB efficiently managed the analytical scan of the Parquet data, while Aurora processed the operational data, a task that would have previously necessitated a pipeline to transfer the historical data into the database beforehand.
For queries requiring single-digit-millisecond latency, users can materialize data from the data lake into a native Aurora PostgreSQL table using familiar commands such as CREATE TABLE AS SELECT, INSERT INTO ... SELECT, or MERGE INTO. The materialized table resides in Aurora and can be queried like any other PostgreSQL table, providing a low-latency pathway for hot data without the need for a separate ingestion pipeline. Read queries can be executed on any Aurora PostgreSQL instance within the cluster, whether it be the writer or a read replica, allowing for the offloading of analytical scans from the operational workload. The materialization commands write data into Aurora, thus executing on the writer instance.
Direct querying of Apache Iceberg and Parquet data from Amazon Aurora PostgreSQL is now available across all commercial AWS Regions and AWS GovCloud (US) Regions, without incurring additional charges. Users will only be billed for the incremental Aurora compute consumed by the queries and Amazon S3 request costs associated with reading data lake files.
To explore this feature further, visit the Amazon Aurora features page, consult the Aurora PostgreSQL documentation, or experiment with it in the Amazon RDS console. Feedback is welcome through AWS re:Post or through your usual AWS Support contacts.
