PostgreSQL Performance Tuning: Measure First, Change Less
Date Published
Start with the workload
Performance tuning begins with latency objectives and workload shape: read versus write ratio, concurrent sessions, transaction duration, data set size, and growth. Capture a baseline under representative load. CPU percentage alone is not a diagnosis; correlate application latency with database waits, I/O latency, locks, checkpoints, and query statistics.
Fix expensive query patterns first
Use pg_stat_statements to rank queries by total time, mean time, calls, rows, and temporary I/O. Inspect representative plans with EXPLAIN (ANALYZE, BUFFERS) in a safe environment. Missing indexes, poor join order, stale statistics, unnecessary row retrieval, and chatty application loops usually outperform any global configuration tweak as optimization targets.
Treat memory as a budget
shared_buffers, work_mem, maintenance_work_mem, connection count, and operating-system cache compete for finite RAM. work_mem is available per sort or hash operation, not per server, so multiplying it by active queries can cause sudden memory exhaustion. Set conservative defaults and raise memory for controlled maintenance or reporting sessions when evidence supports it.
Keep WAL and checkpoints smooth
Checkpoint spikes create latency even when average I/O looks healthy. Size max_wal_size for the workload, use a sensible checkpoint_completion_target, and watch checkpoint write and sync times. Fast storage does not excuse uncontrolled WAL growth. Replication slots, archiving failures, and long transactions can retain WAL until the filesystem fills.
Change one thing and keep the rollback
Apply one measured change, run the same workload, and compare percentiles rather than a single average. Check correctness and resource use as well as speed. Keep configuration changes versioned and note whether a restart is required. If the metric that justified the change does not improve, revert it; complexity without evidence is operational debt.