awsrdsPractitioner

Querying Data Lakes Directly with Amazon Aurora PostgreSQL

5 min read AWS BlogSep 30, 2026Reviewed for accuracy

Practitioner — Hands-on experience recommended

In today’s data-driven world, the ability to query data lakes directly from your operational database is a game changer. Amazon Aurora PostgreSQL now supports this capability, allowing you to access Apache Iceberg and Parquet data without the overhead of data movement. This integration means you can run complex queries that join live operational data with historical data stored in your data lake, all in a single query.

To get started, you need to create an Aurora PostgreSQL cluster and attach an IAM role with the AuroraAnalytics feature. Once that's done, enable the aurora_analytics extension. This extension allows you to create foreign tables that point to your Iceberg or Parquet data in Amazon S3. You can then use familiar PostgreSQL syntax to query these tables. For example, a single SQL query can join data from Aurora with Iceberg tables registered across multiple catalogs, providing a unified view of your data.

In production, make sure you’re using one of the supported Aurora PostgreSQL versions: 17 (starting with 17.11) or 18 (starting with 18.6). Pay attention to the IAM role configuration, as it’s crucial for accessing your data in S3 and the AWS Glue Data Catalog. This feature can significantly enhance your analytics capabilities, but you should also be aware of the potential complexities of managing foreign tables and the performance implications of querying large datasets directly from your data lake.

Key takeaways

→Create an Aurora PostgreSQL cluster and attach an IAM role with the AuroraAnalytics feature.
→Enable the `aurora_analytics` extension to query Iceberg and Parquet data.
→Use familiar PostgreSQL syntax to create foreign tables pointing to your data lake.
→Join live operational data with historical data in a single query for comprehensive insights.

Why it matters

This feature allows you to leverage your existing data lake infrastructure without duplicating data, reducing costs and complexity while enhancing analytics capabilities.

Code examples

sql

CREATE EXTENSION aurora_analytics;

sql

1CREATE FOREIGN TABLE transaction_history ()
2SERVER aurora_analytics_server
3OPTIONS (
4    location 's3://<my-bucket>/finance/transaction_history.parquet',
5    format 'parquet'
6);

sql

1SELECT merchant, category, amount, transaction_date, 'recent' AS source
2FROM recent_transactions
3WHERE customer_id = 'C-1001'
4UNION ALL
5SELECT merchant, category, amount, transaction_date, 'historical' AS source
6FROM transaction_history
7WHERE customer_id = 'C-1001'
8  AND transaction_date >= CURRENT_DATE - INTERVAL '5 years'
9ORDER BY transaction_date DESC
10LIMIT 15;