Stop Reverse-ETLing: Aurora PostgreSQL Queries S3 Data Lakes
Aurora PostgreSQL now queries S3 data lakes directly (DuckDB embedded, zero ETL): one query joins live transactions with lake history. Three tips inside.
For years the deal was simple: if your application needed transactional data and history together, you shipped the history into the database first. Reverse ETL, nightly COPY jobs, a Sinatra-sacrifice of engineering hours keeping it all in sync — pick your poison. I've maintained enough of those pipelines to have opinions, mostly unprintable ones.
AWS just removed most of that work. Aurora PostgreSQL can now query Apache Iceberg and Parquet data in S3 directly from your Postgres session — no ETL, no copies, no second endpoint. DuckDB is embedded right inside Aurora, so a single query joins your live
recent_transactions table with five years of Parquet in a bucket, using the SQL you already write. Announced in late September 2026, here's what it looks like and the tricks I'd actually use.The setup is three steps
- Right engine version — Aurora PostgreSQL 17 (17.11+) or 18 (18.6+).
- Give it access — attach an IAM role with the
AuroraAnalyticsfeature to the cluster (that role grants S3 and AWS Glue Data Catalog permissions), then enable the extension:
1
CREATE EXTENSION aurora_analytics;- Point a foreign table at your data — schema included, because Aurora reads it from the file metadata. The empty parentheses are not a typo:
1
2
3
4
5
6
CREATE FOREIGN TABLE transaction_history ()
SERVER aurora_analytics_server
OPTIONS (
location 's3://my-bucket/finance/transaction_history.parquet',
format 'parquet'
);Setting up many tables? Skip the one-at-a-time dance: a single
IMPORT FOREIGN SCHEMA bulk-creates foreign tables for every Iceberg or Parquet table in a Glue database, inferring schemas automatically. That one line replaces an afternoon of DDL archaeology.One query, warm and cold data
The classic demo, minus the pipeline:
1
2
3
4
5
6
7
8
9
10
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 recent rows come from Aurora — including uncommitted writes, which still surprises me — and the historical rows stream straight from S3 under a DuckDB analytical scan. Query processing stays inside Aurora: no extra network hops, no duplicated data for two teams to eventually disagree about.
Performance is handled the boring-but-correct way: predicate pushdown and column pruning keep the S3 read small, and frequently accessed data is cached in the Aurora instance. You can see it working —
aurora_analytics_stat_statements() reports rows scanned, bytes read from S3, and cache hits per query. That last one is the difference between "it's fast" and knowing why it's fast, which is the kind of number you want in your runbook before the 2 a.m. page, not after.Three tips worth more than the setup guide
Materialize only the hot path. If a query needs single-digit-millisecond latency, land the data lake slice into a native Aurora table with
CREATE TABLE AS SELECT, INSERT INTO ... SELECT, or MERGE INTO. You get a low-latency table without operating a pipeline — and MERGE INTO keeps it fresh on your schedule instead of a cron job's.Offload analytics to readers. Lake-facing read queries run on any instance in the cluster — writer or read replica — so point your analytical dashboards at a replica and keep the writer for traffic that matters. (Materialization writes still go to the writer. Physics.)
Catalog federation for the messy middle. If your Iceberg tables live in external IRC-compatible catalogs, register them once with Glue and query them like any Glue-native table. One query can join across multiple catalogs. I like that AWS didn't try to evict my existing catalog — that attitude is rarer than it should be.
What it costs (and what to watch)
The capability itself carries no additional charge: you pay the incremental Aurora compute the queries consume and S3 request costs for reading lake files. Two things I'd watch in a security review before unleashing it: the IAM role's blast radius (scope it to the specific buckets and catalog resources, not
s3:*), and who on your team can CREATE FOREIGN TABLE — now that Postgres roles can reach into S3, your least-privilege policy needs one more column.If you're already running Aurora PostgreSQL 17.11+ or 18.6+, enable the extension and point it at your oldest Parquet archive. The pipeline you won't build is the best one.
Enjoyed reading this content? Let the author know!
Your likes, comments, shares, and saves help creators reach more builders.
Loading recommendations
Loading article