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 timepg_stat_io— granular I/O analysis by backend type, object, and context (PG16+)pg_stat_checkpointer— checkpoint frequency and timing (PG17+; previously inpg_stat_bgwriter)pg_stat_user_tables— dead tuple counts for bloat detection and autovacuum monitoringpg_statio_user_tables— buffer cache hit ratios per tablepg_aios— in-progress AIO operations (PG18+)
Quick Reference
| Pattern | Configuration / Query | Key Points |
|---|---|---|
| Async I/O | io_method = worker or io_uring | PG18 default is worker; io_uring Linux-only (kernel 5.1+, requires liburing build flag) |
| I/O concurrency | io_max_concurrency and io_workers | io_workers defaults to 3; io_max_concurrency defaults to -1 (auto-calculated) |
| Forensic EXPLAIN | EXPLAIN (ANALYZE, BUFFERS, SETTINGS) | PG18 auto-includes BUFFERS with ANALYZE; target Shared Hit > 95% |
| UUIDv7 primary keys | DEFAULT uuidv7() | PG18 built-in; time-ordered, monotonic within a session; RFC 9562 compliant |
| B-tree skip scan | Composite index on (a, b) | PG18 skips leading column; works best with low-cardinality prefix and equality on trailing columns |
| Aggressive autovacuum | autovacuum_vacuum_scale_factor = 0.01 | Triggers at 1% row change instead of default 20% |
| Shared buffers | Start at 25% of RAM | Do not exceed 40% without benchmarking |
| work_mem tuning | SET work_mem = '64MB' per session | Prevents sort spills to disk; allocated per operator, not per query |
| BRIN index | CREATE INDEX USING brin(...) | 100x smaller than B-tree for physically ordered time-series data |
| HNSW vector index | USING hnsw (col vector_cosine_ops) | Tune m (default 16) and ef_construction (default 64) for recall vs speed |
| GIN index | CREATE INDEX USING gin(...) | JSONB containment, full-text search, array operators; slower writes |
| Checkpoint tuning | checkpoint_timeout = 30min | Spread writes over 90% of timeout window to avoid I/O storms |
| WAL compression | wal_compression = zstd | Available since PG15; reduces WAL I/O 50-70% for write-heavy workloads |
| Bloat detection | pg_stat_user_tables.n_dead_tup | Reindex concurrently if bloat > 30% |
| I/O monitoring | SELECT * FROM pg_stat_io | Watch evictions (cache too small) and extends (fast growth) |
| Checkpoint monitoring | pg_stat_checkpointer | PG17+ moved checkpoint stats out of pg_stat_bgwriter |
Key Version Changes
PostgreSQL 18:
- Native async I/O via
io_methodparameter (reads only; writes remain synchronous) - Built-in
uuidv7()function with monotonic ordering within a session (RFC 9562) uuidv4()alias forgen_random_uuid()anduuid_extract_timestamp()for UUIDv7- B-tree skip scan for composite indexes (equality on trailing columns, low-cardinality prefix)
- EXPLAIN ANALYZE auto-includes buffer statistics without specifying BUFFERS
pg_stat_iogains byte-level columns (read_bytes,write_bytes,extend_bytes);op_bytesremovedeffective_io_concurrencydefault changed from 1 to 16- AIO monitoring via
pg_aiossystem view for in-progress I/O operations
PostgreSQL 17:
- Checkpoint statistics moved from
pg_stat_bgwritertopg_stat_checkpointer - Column renames:
checkpoints_timedtonum_timed,checkpoints_reqtonum_requested buffers_backendandbuffers_backend_fsyncremoved frompg_stat_bgwriter(now inpg_stat_io)
PostgreSQL 15:
wal_compressionexpanded from boolean to supportpglz,lz4, andzstdalgorithms
Common Mistakes
| Mistake | Correct Pattern |
|---|---|
Using uuid_generate_v7() or gen_random_uuid() for ordered keys | PG18 provides built-in uuidv7() for time-ordered UUIDs; pre-PG18 use pg_uuidv7 extension |
Using max_async_ios as a configuration parameter | The correct PG18 parameter is io_max_concurrency (max concurrent I/O ops per process) |
Querying pg_stat_bgwriter for checkpoint statistics on PG17+ | Checkpoint stats moved to pg_stat_checkpointer in PG17; columns renamed (num_timed, num_requested) |
| Using SELECT * in high-frequency queries | Select only needed columns to reduce I/O and improve cache hit ratios |
| Ignoring sequential scans on tables over 10k rows | Add targeted indexes on columns used in WHERE, ORDER BY, and JOIN clauses |
| Setting shared_buffers above 40% of RAM without testing | Start at 25% and benchmark; excessive allocation causes OS page cache contention |
| Leaving autovacuum at default settings for high-churn tables | Tune autovacuum_vacuum_scale_factor to 0.01 for tables with frequent UPDATE/DELETE |
| Over-indexing columns rarely used in queries | Every extra index slows UPDATE/INSERT and prevents HOT (Heap Only Tuple) updates |
| Expecting B-tree skip scan to work with range predicates | PG18 skip scan only works with equality operators on trailing columns |
| Ignoring "External Merge Disk" in query plans | Increase work_mem for specific sessions; it indicates sort spills to disk |
Setting io_method = io_uring without verifying build flags | PostgreSQL must be built with --with-liburing and requires Linux kernel 5.1+ |
| Assuming PG18 AIO accelerates writes | AIO in PG18 only covers reads (seq scans, bitmap heap scans, VACUUM); writes remain synchronous |
Tuning Workflow
- Identify slow queries from
pg_stat_statements(sort bytotal_exec_time) - Analyze execution plans with
EXPLAIN (ANALYZE, BUFFERS, SETTINGS) - Check buffer hit ratios via
pg_statio_user_tables(target > 99%) - Monitor I/O patterns via
pg_stat_io(watch evictions and disk reads) - Optimize with targeted indexes, work_mem adjustments, or query rewrites
- Verify improvements by re-running EXPLAIN and comparing costs
- Maintain with aggressive autovacuum settings for high-churn tables
Delegation
- Discover slow queries and I/O bottlenecks: Use
Exploreagent to analyze pg_stat_statements, pg_stat_io, and slow query logs - Execute query plan analysis and index optimization: Use
Taskagent to run EXPLAIN ANALYZE, create indexes, and verify performance improvements - Design database scaling and partitioning strategy: Use
Planagent to architect sharding, partitioning, and replication topology