PostgreSQL 18 on Amazon Aurora and Amazon RDS: Performance enhancements

This article explores PostgreSQL 18 performance enhancements available on Amazon Aurora and RDS for PostgreSQL, focusing on query optimization and maintenance improvements.

• Skip scan optimization enables multicolumn indexes to efficiently handle queries filtering non-leading columns, delivering 21x performance improvements without additional indexes
• Enhanced EXPLAIN now includes buffer statistics by default and reports memory vs. disk storage for materialized CTEs with peak usage metrics
• Index Searches metric in EXPLAIN ANALYZE shows how many times the database traversed the index tree during execution
• Self-join elimination automatically removes redundant inner joins on primary keys, eliminating unnecessary table scans
• autovacuum_vacuum_max_threshold caps dead tuple accumulation before autovacuum triggers, preventing excessive bloat on large tables
• vacuum_truncate becomes a server-wide GUC parameter to prevent lock escalation and query cancellations on read replicas
• autovacuum_max_workers is now dynamic, allowing runtime adjustments without server restart
• New pg_stat_all_tables columns track cumulative vacuum and analyze time per table for maintenance overhead analysis

These features help PostgreSQL users optimize query performance, reduce maintenance overhead, and simplify database tuning without manual per-table configuration.