Getting Aurora to read Iceberg is the easy part. Aurora analytics performance is where the work is: the same query can scan two files or two thousand, hit the local cache or hammer S3, and run on an idle reader or on the writer taking your checkout traffic. This piece is the tuning playbook for aurora_analytics, built around what the engine actually reports instead of vibes.
Measure Aurora analytics performance before touching knobs
Every lake query has a scan cost (bytes from S3 versus bytes from cache) and an execution cost (CPU, memory, spill). aurora_analytics_stat_statements() gives you both, cumulatively per normalized query, in the spirit of pg_stat_statements. Sort by remote reads to find the queries eating your S3 request bill, and by spill to find the ones starving for memory.
SELECT calls,
pg_size_pretty(analytics_remote_read_bytes) AS s3_read,
pg_size_pretty(analytics_cache_hit_bytes) AS cache_hit,
pg_size_pretty(analytics_total_disk_spill) AS spilled,
round(analytics_total_cpu_time::numeric / calls, 1) AS cpu_ms_per_call,
left(query, 70) AS query
FROM aurora_analytics_stat_statements()
ORDER BY analytics_total_disk_spill DESC, analytics_remote_read_bytes DESC
LIMIT 10;Remote reads that dominate cache hits on a repeated query mean the working set does not fit or the cache is cold. Any non-zero spill means the query blew past aurora_analytics.query_mem. For a single query, EXPLAIN ANALYZE prints the same story at the bottom of the plan, plus S3 GET and HEAD counts and, on READ_PARQUET, Total Files Read and Rows Removed by Filter. Turn on aurora_analytics.track_detailed_metrics in a session when you want per-node CPU time too. By default you only see your own role’s statements; rds_superuser sees everyone’s.
Win the pushdown fight
The fast path is full query pushdown, where the whole plan runs inside DuckDB. The fallback still pushes filters and projections to the scan, but joins and aggregates then run in Postgres row by row, which is like hiring a sprinter and making him carry a couch. The most common avoidable cause is collation: text comparisons under a libc collation such as en_US are not pushdown-safe.
-- Falls back: non-C collation on the comparison
SELECT region, sum(amount) FROM lake.payments
WHERE merchant_name LIKE 'Acme%' GROUP BY region;
-- Pushes down
SELECT region, sum(amount) FROM lake.payments
WHERE merchant_name COLLATE "C" LIKE 'Acme%' GROUP BY region;
-- Is a function pushdown-safe? (missing row = not pushed down)
SELECT p.proname, f.description
FROM aurora_analytics_available_features() f
JOIN pg_proc p ON p.oid = f.oid
WHERE f.feature_type = 'function' AND p.proname = 'date_trunc';Other repeat offenders: Postgres-only functions and UDFs in the select list, regex operators, array_agg() and recursive CTEs. If an app query needs one, compute it in a CTE over pushdown-safe columns and apply the exotic bit to the small result. Re-check with EXPLAIN VERBOSE until the “Unsupported Pushdown Expressions” section disappears.
Layout beats tuning knobs
Pushdown only skips what the file layout lets it skip. The engine prunes on Iceberg partition values and Parquet row-group min/max statistics, so a filter on order_ts is nearly free if data is partitioned and sorted by time, and a full scan if it is not. Three rules from the data layout guidance: partition on the columns your app filters by, avoid piles of small files, and keep row groups sensibly sized. Iceberg’s hidden partitioning lets you evolve the spec without rewriting history, and compaction cleans up streaming-ingest confetti.
-- Spark SQL (Iceberg extensions): partition new data by day
ALTER TABLE glue_catalog.sales_lake.payments ADD PARTITION FIELD days(paid_at);
-- Athena: compact small files in the hot window
OPTIMIZE sales_lake.payments REWRITE DATA USING BIN_PACK
WHERE paid_at >= TIMESTAMP '2026-09-01 00:00:00';If you’d rather not run that yourself, S3 Tables handles compaction automatically. Also keep the bucket in the same Region as the cluster; cross-Region reads add latency and transfer charges for no benefit.
Move the heavy lifting to a reader
Lake queries do not use shared_buffers, they want query_mem and local disk. That makes a dedicated analytics reader the right home: give it its own DB parameter group with a smaller shared_buffers and a larger query_mem, set promotion tier 15 so failover picks a transaction-tuned reader first, and pin analytics clients to it through a custom endpoint instead of the round-robin cluster reader endpoint.
aws rds modify-db-instance --db-instance-identifier shop-analytics-1 \
--promotion-tier 15 --apply-immediately
aws rds create-db-cluster-endpoint \
--db-cluster-identifier shop-prod \
--db-cluster-endpoint-identifier lake-analytics \
--endpoint-type READER \
--static-members shop-analytics-1Prefer a db.r8gd class: with local NVMe (and Aurora I/O-Optimized), both the read cache and spill files land on NVMe instead of EBS. One catch: long queries on a reader hold back vacuum on the writer, so watch oldest_reader_feedback_xid_age and bound runtimes per role.
ALTER ROLE bi_dashboards SET aurora_analytics.query_mem = '8GB';
ALTER ROLE bi_dashboards SET statement_timeout = '120s';Keep the cache warm
The read cache is shared by every session on the instance and sized from the instance’s memory and storage, so bigger instances cache more. It is also wiped on restart or failover, which means the first dashboard load after maintenance pays full S3 latency. For latency-sensitive paths, run a warm-up script of your top queries (straight from stat_statements) right after the instance comes back, before you shift traffic. Freshness is not a cache problem: new Parquet files are picked up on the next query, and Iceberg snapshot resolution ignores the cache. Overwriting a Parquet file in place can briefly serve stale data to an open connection, which is one more reason to stop overwriting files.
Incremental materialization with MERGE
When an endpoint needs single-digit-millisecond reads, query a local table, not the lake. Full CTAS rebuilds are wasteful once the history is large; recompute a sliding window and MERGE it in. The window also absorbs late-arriving events.
MERGE INTO public.payments_daily d
USING (
SELECT paid_at::date AS day, region,
count(*) AS txns, sum(amount) AS gross
FROM lake.payments
WHERE paid_at >= current_date - 3
GROUP BY 1, 2
) s
ON d.day = s.day AND d.region = s.region
WHEN MATCHED THEN UPDATE SET txns = s.txns, gross = s.gross
WHEN NOT MATCHED THEN INSERT (day, region, txns, gross)
VALUES (s.day, s.region, s.txns, s.gross);Writes only happen on the writer, so this is the one lake scan you cannot exile to a reader. Keep the window tight thanks to the partition layout above, run it from a role with a modest query_mem, and schedule it off-peak.
Guardrails against hammering prod
- Concurrency budget. With default
shared_buffers, keep combinedquery_memof concurrent lake queries within roughly 30 percent of instance memory. Queue in the app rather than letting twenty dashboards stampede. - Don’t run dashboards as superuser. Built-in memory protection cancels runaway lake queries under pressure, but
rds_superusersessions are exempt. - Alarms.
AuroraAnalyticsMemoryUsage,AuroraAnalyticsDiskSpillSize,AuroraAnalyticsCacheHitRatioandFreeLocalStoragein CloudWatch alarms, plus theExtension:AuroraAnalyticsExecutewait event in Database Insights. - Live triage.
aurora_analytics_stat_resource_usage()joined topg_stat_activityonpidshows who is eating memory right now.
Measure, push down, fix layout, isolate, warm, materialize. In that order, most lake queries stop being a performance story at all.
At Enkompass (enkompass.net), this is our home turf. We’ve tuned Aurora analytics readers, Iceberg layouts and incremental materializations for clients replacing reverse-ETL pipelines, so the lakehouse queries stay fast and the writer stays boring. 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.