Tutorial: Query Glue Iceberg Tables from Aurora PostgreSQL

Aurora PostgreSQL can now query Glue Iceberg tables in S3 directly, using an embedded DuckDB engine and a plain Postgres foreign table. This walkthrough takes an existing cluster from zero to a working join between live Aurora rows and lake history, then materializes a result and checks what it cost in S3 reads. Budget about 45 minutes, most of it waiting on IAM and the AWS CLI.

Prerequisites

• An Aurora PostgreSQL cluster on 17.11+ or 18.6+ using a custom DB cluster parameter group (the default group can’t be edited). This guide calls it shop-prod with parameter group shop-prod-cpg.
• An AWS Glue Data Catalog database named sales_lake containing an Apache Iceberg table orders (order_id, customer_id, order_ts, total_amount, status). One created by Amazon Athena works fine.
• Some loose Parquet files under s3://acme-lake/raw/refunds/, same Region as the cluster.
• AWS CLI v2 with IAM and RDS permissions, plus psql and a login with rds_superuser.

How to query Glue Iceberg tables from Aurora, step by step

Quick mental model before the commands. The foreign table you create stores only a name, a location and a schema; no rows move into Aurora. At query time, Postgres plans the SQL and hands the lake part to DuckDB, which asks Glue for the current Iceberg snapshot, then reads just the byte ranges and columns it needs from S3 using the role you attach in step 4. Results come back as normal Postgres rows, so your driver, ORM and BI tool never know the difference. Everything below follows that path in order: permissions, the cluster switch, networking, then SQL.

1. Confirm the engine version and parameter group. If the version is below 17.11, upgrade first. If the parameter group says default.aurora-postgresql17, create and attach a custom one, which needs an instance reboot. Do this in a maintenance window, not at lunch.

   aws rds describe-db-clusters \
     --db-cluster-identifier shop-prod \
     --query "DBClusters[0].[EngineVersion,DBClusterParameterGroup]" \
     --output text

2. Create a least-privilege IAM policy. Iceberg via Glue needs glue:GetTable on the catalog, database and table ARNs, plus s3:GetObject. glue:GetTables is for IMPORT FOREIGN SCHEMA, and s3:ListBucket is for the raw Parquet prefix. Add kms:Decrypt if the bucket uses SSE-KMS.

   aws iam create-policy --policy-name AuroraAnalyticsLakeRead \
     --policy-document '{
     "Version": "2012-10-17",
     "Statement": [
       {"Effect": "Allow", "Action": ["glue:GetTable", "glue:GetTables"],
        "Resource": ["arn:aws:glue:us-east-1:123456789012:catalog",
                     "arn:aws:glue:us-east-1:123456789012:database/sales_lake",
                     "arn:aws:glue:us-east-1:123456789012:table/sales_lake/*"]},
       {"Effect": "Allow", "Action": "s3:GetObject",
        "Resource": "arn:aws:s3:::acme-lake/*"},
       {"Effect": "Allow", "Action": "s3:ListBucket",
        "Resource": "arn:aws:s3:::acme-lake"}
     ]}'

3. Create the role RDS will assume. The trust principal is rds.amazonaws.com, same as for S3 import and export roles.

   aws iam create-role --role-name AuroraAnalyticsRole \
     --assume-role-policy-document '{"Version": "2012-10-17",
      "Statement": [{"Effect": "Allow",
      "Principal": {"Service": "rds.amazonaws.com"},
      "Action": "sts:AssumeRole"}]}'
   
   aws iam attach-role-policy --role-name AuroraAnalyticsRole \
     --policy-arn arn:aws:iam::123456789012:policy/AuroraAnalyticsLakeRead

4. Attach the role with the AuroraAnalytics feature and flip the switch. The feature name matters; a role attached for s3Import will not be used. aurora_analytics.enabled defaults to false and is dynamic, so no reboot here. See add-role-to-db-cluster for the full flag list.

   aws rds add-role-to-db-cluster \
     --db-cluster-identifier shop-prod \
     --feature-name AuroraAnalytics \
     --role-arn arn:aws:iam::123456789012:role/AuroraAnalyticsRole
   
   aws rds modify-db-cluster-parameter-group \
     --db-cluster-parameter-group-name shop-prod-cpg \
     --parameters "ParameterName=aurora_analytics.enabled,ParameterValue=true,ApplyMethod=immediate"

5. Give private subnets a path to S3 and Glue. Skip this if you already route through a NAT gateway, though the gateway endpoint is free and keeps traffic off NAT data-processing charges. The Glue interface endpoint needs private DNS and a security group allowing 443 from the cluster.

   aws ec2 create-vpc-endpoint --vpc-id vpc-0abc123 \
     --service-name com.amazonaws.us-east-1.s3 \
     --route-table-ids rtb-0def456
   
   aws ec2 create-vpc-endpoint --vpc-id vpc-0abc123 \
     --vpc-endpoint-type Interface \
     --service-name com.amazonaws.us-east-1.glue \
     --subnet-ids subnet-0aaa111 subnet-0bbb222 \
     --security-group-ids sg-0ccc333 --private-dns-enabled

6. Install the extension. Run this in each database that needs lake access. It creates the foreign server aurora_analytics_server.

   psql "host=shop-prod.cluster-xxxx.us-east-1.rds.amazonaws.com dbname=shop user=postgres"
   
   CREATE EXTENSION aurora_analytics;
   CREATE SCHEMA lake;
   SELECT srvname FROM pg_foreign_server WHERE srvname = 'aurora_analytics_server';

   

7. Map a raw Parquet prefix. Empty parentheses mean the schema is inferred from Parquet metadata. Raw S3 paths need format; catalog ARNs don’t.

   CREATE FOREIGN TABLE lake.refunds ()
   SERVER aurora_analytics_server
   OPTIONS (location 's3://acme-lake/raw/refunds/', format 'parquet');
   
   \d lake.refunds
   SELECT count(*) FROM lake.refunds;

8. Import the Glue database in one shot. IMPORT FOREIGN SCHEMA creates a foreign table per Glue table inside a single transaction. If any one table fails, the whole import rolls back, so LIMIT TO keeps the first run clean.

   IMPORT FOREIGN SCHEMA sales_lake
     LIMIT TO (orders)
     FROM SERVER aurora_analytics_server
     INTO lake
     OPTIONS (location 'arn:aws:glue:us-east-1:123456789012:catalog');
   
   SELECT status, count(*) FROM lake.orders GROUP BY status;

9. Join lake history with live Aurora rows, then materialize. This creates a tiny local table standing in for your real customers table, joins it to two years of Iceberg orders, and saves a monthly rollup locally with CTAS. Run materialization on the writer; reads work on any instance.

   CREATE TABLE IF NOT EXISTS public.customers (
     id BIGINT PRIMARY KEY, email TEXT, tier TEXT);
   INSERT INTO public.customers VALUES
     (1001, 'ana@example.com', 'gold'), (1002, 'raj@example.com', 'silver')
   ON CONFLICT DO NOTHING;
   
   SELECT c.tier, count(*) AS orders, sum(o.total_amount) AS revenue
   FROM lake.orders o
   JOIN public.customers c ON c.id = o.customer_id
   WHERE o.order_ts >= now() - interval '2 years'
   GROUP BY c.tier;
   
   CREATE TABLE public.tier_revenue_monthly AS
   SELECT c.tier, date_trunc('month', o.order_ts) AS month,
          sum(o.total_amount) AS revenue
   FROM lake.orders o
   JOIN public.customers c ON c.id = o.customer_id
   GROUP BY 1, 2;

10. See what it cost. aurora_analytics_stat_statements() shows per-query bytes from S3 versus the local cache. Run the join twice; the second pass should shift bytes from S3 reads to cache hits. If s3_reads stays large on repeat runs, the working set is bigger than the local cache, which is your cue to look at instance size or data layout. Plain EXPLAIN ANALYZE on the same query also prints S3 GET and HEAD request counts at the bottom of the plan.

    SELECT calls,
           pg_size_pretty(analytics_remote_read_bytes) AS s3_reads,
           pg_size_pretty(analytics_cache_hit_bytes)   AS cache_hits,
           left(query, 60) AS query
    FROM aurora_analytics_stat_statements()
    ORDER BY analytics_remote_read_bytes DESC
    LIMIT 5;

What you built

You now have an Aurora cluster that reads Iceberg tables through the Glue catalog and loose Parquet straight from S3, joins both with live operational tables, and can persist results locally when an app needs fast, predictable reads. Nothing was copied, and the lake stays the single source of truth. For production, move these queries onto a dedicated reader, as the dedicated reader guidance describes, before your dashboards discover the writer.

Cleanup: DROP SCHEMA lake CASCADE; and drop public.tier_revenue_monthly and the demo public.customers if they aren’t real. Then remove the role with aws rds remove-role-from-db-cluster using the same feature name, detach and delete the IAM policy and role, set aurora_analytics.enabled back to false, and delete the Glue interface endpoint, which bills hourly, if you created it only for this.

At Enkompass (enkompass.net), this is our home turf. We’ve wired Aurora PostgreSQL directly to client lakehouses on Glue and S3 Tables and retired the reverse-ETL jobs that used to shuttle that data back into the database. If you’d rather have seasoned cloud people handle it instead of learning the hard way at 2 AM, let’s talk. Reach out through our contact page or call us directly at +1-828-436-5667 — we’ll help you turn this from a someday-project into a done-deal.