PostgreSQL Tuning

Overview

Optimizes PostgreSQL 17/18+ performance across I/O, query execution, indexing, and maintenance. Covers the native AIO subsystem introduced in PostgreSQL 18 for throughput gains on modern storage, forensic query plan analysis with EXPLAIN BUFFERS (auto-included in PG18), B-tree skip scans for composite indexes, native UUIDv7 generation, and autovacuum tuning for high-churn tables.

When to use: Diagnosing slow queries, configuring async I/O, tuning shared_buffers and work_mem, optimizing indexes for write-heavy workloads, managing table bloat, pgvector HNSW tuning.

When NOT to use: Schema design (use a data modeling tool), application-level caching strategy, database selection decisions, ORM query generation.

Key monitoring views:

• pg_stat_statements — identifies slow query patterns by cumulative execution time
• pg_stat_io — granular I/O analysis by backend type, object, and context (PG16+)
• pg_stat_checkpointer — checkpoint frequency and timing (PG17+; previously in pg_stat_bgwriter)
• pg_stat_user_tables — dead tuple counts for bloat detection and autovacuum monitoring
• pg_statio_user_tables — buffer cache hit ratios per table
• pg_aios — in-progress AIO operations (PG18+)