Aurora PostgreSQL Can Now Query Apache Iceberg Directly - I Put It to the Test
AWS just shipped something that quietly removes a whole category of ETL pipelines: Aurora PostgreSQL (17.11+ and 18.6+) can now query Apache Iceberg and Parquet files sitting in S3 directly, in the same SQL statement as your live operational tables. No export job, no Athena hop, no application-side join.
I didn't want a toy demo, so I built the whole thing: a real S3 + Glue Iceberg table holding five years of synthetic transaction history, and a real Aurora PostgreSQL 17.11 Serverless v2 cluster, wired together with the new
aurora_analytics extension. Here's what that actually involved - every command, every error, and the query that was the point of all of it.Stack: Aurora PostgreSQL 17.11 (Serverless v2, 0.5–2 ACU) ·
aurora_analytics extension · Apache Iceberg on S3 + Glue Data Catalog · us-east-1Setting up the lake
An encrypted S3 bucket, a Glue database, and an Iceberg table created and loaded through Athena - the same path most teams already use for lake storage.
1
2
3
4
5
6
7
8
9
aws s3api create-bucket --bucket iceberg-demo-<account>-use1 --region us-east-1
aws s3api put-bucket-encryption --bucket iceberg-demo-<account>-use1 \
--server-side-encryption-configuration '{"Rules":[{"ApplyServerSideEncryptionByDefault":{"SSEAlgorithm":"AES256"}}]}'
aws s3api put-public-access-block --bucket iceberg-demo-<account>-use1 \
--public-access-block-configuration BlockPublicAcls=true,IgnorePublicAcls=true,BlockPublicPolicy=true,RestrictPublicBuckets=true
aws glue create-database --database-input '{"Name":"iceberg_demo"}'
aws athena create-work-group --name iceberg-demo-wg \
--configuration "ResultConfiguration={OutputLocation=s3://iceberg-demo-<account>-use1/athena-results/},EnforceWorkGroupConfiguration=true"
The table itself, created through Athena:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE TABLE transaction_history (
transaction_id string,
customer_id string,
merchant string,
category string,
amount decimal(10,2),
transaction_date date
)
LOCATION 's3://iceberg-demo-<account>-use1/iceberg/transaction_history/'
TBLPROPERTIES (
'table_type' = 'ICEBERG',
'format' = 'parquet',
'write_compression' = 'snappy'
)
Twenty-five rows went in across 2021–2025 via a plain
INSERT INTO … VALUES. Athena handled the Iceberg snapshot, manifest list, and manifest file bookkeeping automatically - no Spark job required. Worth a quick look at what that actually wrote to S3, since it's what the extension reads later:1
2
3
4
5
iceberg/transaction_history/data/.../....parquet 1.6 KiB # the actual data
iceberg/transaction_history/metadata/00000-....metadata.json 1.6 KiB # table created
iceberg/transaction_history/metadata/00001-....metadata.json 2.8 KiB # after INSERT - current snapshot
iceberg/transaction_history/metadata/....m0.avro 6.9 KiB # manifest file
iceberg/transaction_history/metadata/snap-....avro 4.2 KiB # manifest list
A versioned chain of
metadata.json files, each pointing at a manifest list, which points at manifest files, which point at the Parquet data. Glue's table record stores a pointer to whichever metadata.json is current - that pointer matters a lot in Gotcha #2 below.
Provisioning Aurora, and the IAM role that's new here
This is the part that's genuinely new: a cluster-level IAM role attached under a specific feature name,
AuroraAnalytics - not the familiar s3Import/s3Export pattern. The role trusts rds.amazonaws.com and needs read access to both the S3 bucket and the Glue Data Catalog.1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
aws rds describe-db-engine-versions --engine aurora-postgresql \
--query "DBEngineVersions[?EngineVersion>='17.11'].EngineVersion"
# 17.4 17.5 17.6 17.7 17.9 17.11 18.3 18.4 18.6
aws rds create-db-cluster \
--db-cluster-identifier iceberg-demo-cluster \
--engine aurora-postgresql --engine-version 17.11 \
--master-username pgadmin --master-user-password '<pw>' \
--db-subnet-group-name iceberg-demo-subnet-group \
--vpc-security-group-ids <sg> \
--serverless-v2-scaling-configuration MinCapacity=0.5,MaxCapacity=2 \
--enable-http-endpoint \
--storage-encrypted \
--database-name icebergdemo
aws rds create-db-instance \
--db-instance-identifier iceberg-demo-instance-1 \
--db-cluster-identifier iceberg-demo-cluster \
--engine aurora-postgresql --db-instance-class db.serverless \
--publicly-accessible
The IAM role itself - trust policy plus a scoped S3 + Glue read policy:
1
2
3
4
5
6
7
8
{
"Version": "2012-10-17",
"Statement": [{
"Effect": "Allow",
"Principal": { "Service": "rds.amazonaws.com" },
"Action": "sts:AssumeRole"
}]
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
{
"Version": "2012-10-17",
"Statement": [
{
"Sid": "S3DataLakeAccess",
"Effect": "Allow",
"Action": ["s3:GetObject", "s3:ListBucket"],
"Resource": [
"arn:aws:s3:::iceberg-demo-<account>-use1",
"arn:aws:s3:::iceberg-demo-<account>-use1/*"
]
},
{
"Sid": "GlueCatalogAccess",
"Effect": "Allow",
"Action": ["glue:GetDatabase", "glue:GetDatabases", "glue:GetTable", "glue:GetTables", "glue:GetPartition", "glue:GetPartitions"],
"Resource": [
"arn:aws:glue:us-east-1:<account>:catalog",
"arn:aws:glue:us-east-1:<account>:database/iceberg_demo",
"arn:aws:glue:us-east-1:<account>:table/iceberg_demo/*"
]
}
]
}
1
2
3
4
5
6
7
8
9
aws iam create-role --role-name aurora-analytics-iceberg-demo-role \
--assume-role-policy-document file://trust-policy.json
aws iam put-role-policy --role-name aurora-analytics-iceberg-demo-role \
--policy-name AuroraAnalyticsS3GlueAccess --policy-document file://analytics-policy.json
aws rds add-role-to-db-cluster \
--db-cluster-identifier iceberg-demo-cluster \
--role-arn arn:aws:iam::<account>:role/aurora-analytics-iceberg-demo-role \
--feature-name AuroraAnalytics
One timing detail: the role attachment only succeeds once the cluster status is
available - doing it during creating throws InvalidDBClusterStateFault. After a successful attach, describe-db-clusters shows the role's status as PENDING for roughly a minute before flipping to ACTIVE; nothing downstream works until it does.
Gotcha #1: CREATE EXTENSION isn't the same as "on"
CREATE EXTENSION aurora_analytics succeeds immediately and registers the foreign server and FDW. Try to use it, though, and you get:1
2
ERROR: aurora_analytics is not enabled
Hint: Set aurora_analytics.enabled to true in the parameter group and retry the query.
The parameter lives in a cluster parameter group, defaults off, and its apply method is
pending-reboot - meaning a parameter group swap alone doesn't take effect.1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
aws rds create-db-cluster-parameter-group \
--db-cluster-parameter-group-name iceberg-demo-pg17 \
--db-parameter-group-family aurora-postgresql17
aws rds modify-db-cluster-parameter-group \
--db-cluster-parameter-group-name iceberg-demo-pg17 \
--parameters "ParameterName=aurora_analytics.enabled,ParameterValue=1,ApplyMethod=pending-reboot"
aws rds modify-db-cluster \
--db-cluster-identifier iceberg-demo-cluster \
--db-cluster-parameter-group-name iceberg-demo-pg17 \
--apply-immediately
aws rds reboot-db-instance --db-instance-identifier iceberg-demo-instance-1
aws rds wait db-instance-available --db-instance-identifier iceberg-demo-instance-1
The full parameter family, for reference -
describe-db-cluster-parameters on the new group surfaces seven aurora_analytics.* knobs, every one of them pending-reboot:| Parameter | Controls |
|---|---|
| aurora_analytics.enabled | master on/off switch |
| aurora_analytics.enable_logging | whether the extension writes its own logs |
| aurora_analytics.logging_level | DEBUG5 … ERROR |
| aurora_analytics.query_mem | per-query memory ceiling (KB), scales with instance class |
| aurora_analytics.track_detailed_metrics | feeds the per-query stats in aurora_analytics_stat_statements() |
| aurora_analytics.lowercase_unquoted_column_name | case handling on inferred foreign-table columns |
| aurora_analytics.skip_unsupported_columns | skip vs. error on unmappable columns |
Budget a couple of minutes for the reboot before anything downstream works - on Serverless v2 this one took under two minutes.
Gotcha #2: Athena's Iceberg tables don't write what the reader expects
The bulk path the docs lead with -
IMPORT FOREIGN SCHEMA straight from the Glue database - threw a generic, unhelpful error for me:1
2
IMPORT FOREIGN SCHEMA iceberg_demo FROM SERVER aurora_analytics_server INTO datalake;
-- ERROR: Database returned SQL Exception
A direct
CREATE FOREIGN TABLE against the same table failed too, but with a far more useful error:1
2
3
4
5
6
7
8
9
CREATE FOREIGN TABLE datalake.transaction_history ()
SERVER aurora_analytics_server
OPTIONS (location 's3://.../iceberg/transaction_history/', format 'iceberg');
-- ERROR: [Aurora Analytics] failed to fetch column information due to error:
-- 'Invalid Error: Invalid Configuration Error: The specified Iceberg table
-- could not be read because the associated metadata file could not be found.
-- Version-hint.text may be missing. Please provide direct metadata path in
-- location option: s3://bucket/table/metadata/<version>.metadata.json'
The embedded Iceberg reader (DuckDB-based) expects a Hive/Spark-style
version-hint.text pointer at the table root. Athena-managed Iceberg tables don't write one - Athena tracks the current snapshot entirely inside Glue's own metadata_location table property instead:1
2
3
4
5
aws glue get-table --database-name iceberg_demo --name transaction_history \
--query 'Table.Parameters'
# "metadata_location": "s3://.../metadata/00001-....metadata.json",
# "previous_metadata_location": "s3://.../metadata/00000-....metadata.json",
# "table_type": "ICEBERG"
Pointing the foreign table straight at that exact file fixed it immediately, with every column correctly inferred - no manual column list needed:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE FOREIGN TABLE datalake.transaction_history ()
SERVER aurora_analytics_server
OPTIONS (
location 's3://.../iceberg/transaction_history/metadata/00001-....metadata.json',
format 'iceberg'
);
-- CREATE FOREIGN TABLE · all six columns inferred automatically
SELECT * FROM datalake.transaction_history LIMIT 5;
-- T-00001 C-1001 Whole Foods Groceries 84.23 2021-03-14
-- T-00002 C-1001 Shell Gas Fuel 52.10 2021-07-02
-- T-00003 C-1001 Amazon Shopping 129.99 2021-11-19
-- T-00004 C-1001 Delta Airlines Travel 412.50 2022-01-08
-- T-00005 C-1001 Starbucks Dining 6.75 2022-02-23
For a bulk, Glue-driven import across many tables, the fix scales: pull each table's live
metadata_location with glue:GetTable and generate the CREATE FOREIGN TABLE statements from that - a few lines of script, not a blocker.The query that was the point of all this
A customer's last 30 days, still warm in a native Aurora table, unioned with everything the lake has on them going back five years. One plan, one round trip, no application-side merge:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
CREATE TABLE recent_transactions (
transaction_id text PRIMARY KEY,
customer_id text,
merchant text,
category text,
amount numeric(10,2),
transaction_date date
);
-- 7 rows inserted, dated within the last 30 days
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 datalake.transaction_history
WHERE customer_id = 'C-1001'
AND transaction_date >= CURRENT_DATE - INTERVAL '5 years'
ORDER BY transaction_date DESC
LIMIT 15;
Full result, unedited:
| Merchant | Category | Amount | Date | Source |
|---|---|---|---|---|
| Whole Foods | Groceries | $92.10 | 2026-09-30 | recent |
| Shell Gas | Fuel | $61.20 | 2026-09-27 | recent |
| Apple Store | Electronics | $1,099.00 | 2026-09-23 | recent |
| Chipotle | Dining | $16.75 | 2026-09-20 | recent |
| Delta Airlines | Travel | $528.40 | 2026-09-14 | recent |
| Best Buy | Electronics | $249.99 | 2024-09-12 | historical |
| Delta Airlines | Travel | $455.20 | 2024-07-30 | historical |
| Whole Foods | Groceries | $88.60 | 2024-05-27 | historical |
| Netflix | Entertainment | $15.49 | 2024-03-15 | historical |
| Shell Gas | Fuel | $58.90 | 2024-02-02 | historical |
| Costco | Groceries | $210.55 | 2023-12-18 | historical |
| Chipotle | Dining | $14.30 | 2023-10-09 | historical |
| Apple Store | Electronics | $999.00 | 2023-08-21 | historical |
| Whole Foods | Groceries | $76.88 | 2023-06-02 | historical |
| Marriott | Travel | $389.00 | 2023-04-19 | historical |
Rows written to Aurora within the last two weeks, interleaved by date with rows that have lived in S3 as Parquet since 2023 - sorted together, filtered together, in one
ORDER BY. I ran this through the AWS CLI / RDS Data API, and separately confirmed it over a direct psql connection too - both returned the identical fifteen rows.A couple of extras worth knowing
Materializing a hot slice. For data queried often enough that even the pruned S3 scan is overhead,
CREATE TABLE … AS SELECT against the foreign table pulls it into native Aurora storage:1
2
3
4
5
6
7
8
CREATE TABLE transaction_history_2024 AS
SELECT * FROM datalake.transaction_history
WHERE transaction_date >= DATE '2024-01-01';
-- SELECT 8
SELECT count(*), min(transaction_date), max(transaction_date)
FROM transaction_history_2024;
-- 8 | 2024-02-02 | 2025-01-19
Watching what it actually scans.
aurora_analytics_stat_statements() extends the familiar pg_stat_statements pattern with per-query timing, keyed to the same normalized query text:1
2
3
4
SELECT query, calls, total_exec_time, rows
FROM aurora_analytics_stat_statements()
WHERE query ILIKE '%transaction_history%'
ORDER BY calls DESC;
| Query | Calls | Total exec time | Rows |
|---|---|---|---|
| CREATE FOREIGN TABLE transaction_history (…) | 1 | 78.0 ms | 0 |
| SELECT * FROM datalake.transaction_history LIMIT $1 | 1 | 137.8 ms | 5 |
| SELECT … UNION ALL … (the headline query) | 1 | 17.4 ms | 15 |
The union query was faster than the raw table scan it was built from - by the time it ran, the Iceberg manifest read from the prior query was already cached. That's the "query caching" line in the release notes, made concrete: the second touch of the same files didn't pay the S3 round trip again.
What it costs
Nothing here is free-tier:
- Aurora Serverless v2, 0.5 ACU floor - this workload never pushed past it
- S3 - storage plus GET requests per scan; Iceberg's pruning keeps per-query cost low (0.83 KB scanned for the query above, confirmed via Athena on the same table)
- Secrets Manager - one secret, used so the RDS Data API could run SQL over HTTPS
- No separate charge for
aurora_analyticsitself - it bills as ordinary Aurora compute plus standard S3 request pricing, exactly as documented
The headline here isn't really the SQL -
UNION ALL across a foreign table is not new syntax. It's that Aurora now treats your data lake as just another table, with real predicate pushdown and caching, instead of a separate system you query separately and reconcile in application code. For teams sitting on years of "cold" operational history in S3, that's a meaningfully smaller architecture.Built end-to-end with the AWS CLI, the RDS Data API, and the Athena and Glue consoles - Aurora PostgreSQL 17.11, us-east-1.
Enjoyed reading this content? Let the author know!
Your likes, comments, shares, and saves help creators reach more builders.
Loading recommendations
Loading article