PostgreSQL 18 adds richer I/O stats and plan inspection tools. Use this 2026 cheat sheet to tune queries, indexes, vacuum, and config. Read now.
Start with plans and I/O, not config knobs
When a query is slow, inspect the plan before you change shared_buffers or work_mem. PostgreSQL 18’s richer I/O stats and plan inspection tools make that first step more concrete: you can see which nodes drive cost, how much data each step actually reads or writes, and whether time is spent in sequential scans, nested loops, sorts, or hash builds. Treat the plan as a map of tradeoffs—row estimates vs reality, index vs sequential access, and memory vs disk—rather than as a single score to minimize.
Run EXPLAIN (and EXPLAIN ANALYZE when you can afford a real execution) on the exact statement and bind values you care about. Compare estimated rows to actual rows; large gaps usually mean stale statistics, weak predicates, or skewed data. Use the new I/O detail to separate CPU-bound nodes from storage-bound ones. Fix the plan shape first with better predicates, joins, and indexes; only then decide whether the workload needs more cache, more parallel workers, or different vacuum settings.
Index for the queries you run, not every column
Indexes speed lookups and sorts, but each one costs write amplification, storage, and vacuum work. Prefer indexes that match real WHERE, JOIN, and ORDER BY patterns: equality columns first for B-trees when that matches selectivity, covering indexes when you repeatedly fetch a small set of columns, and partial indexes when a filter always targets a subset of rows. Avoid “index everything” habits; unused indexes still slow inserts, updates, and autovacuum.
After you add or drop indexes, recheck plans under representative load. Confirm the planner picks the index for the hot paths and that you did not force a bad join order with over-specific indexes. If a table is write-heavy and read patterns are simple, fewer, well-chosen indexes often beat a dense forest of single-column ones.
Keep vacuum and statistics honest
Bloat and bad estimates are two of the most common “tuning” problems that are really maintenance problems. Autovacuum removes dead tuples and refreshes statistics so the planner can choose sensible plans. If tables churn heavily, default freeze and scale factors may leave dead space or stale stats too long; if tables are mostly static, aggressive vacuum wastes I/O. Tune by table when needed: more frequent vacuum on high-update relations, and careful freeze settings on large, long-lived tables so you avoid sudden wraparound pressure.
- Watch for tables that grow in size without matching live row growth—classic bloat signal.
- After bulk loads or mass updates, run ANALYZE (or VACUUM ANALYZE) so plans reflect the new distribution.
- Use the improved plan and I/O views to see when sequential scans are scanning more pages than the live data should require.
Config as a second pass, not the first lever
Memory and I/O settings matter, but they amplify good plans rather than replace them. Shared buffers and OS page cache trade off how much of the working set stays hot; work_mem affects sort and hash behavior per operation (and multiplies under concurrency); maintenance_work_mem speeds index builds and vacuum but competes with query memory; effective_io_concurrency and related I/O-related settings help when storage can service parallel reads. Raise values only when plans show memory spills, cache misses, or underused I/O capacity—and measure after each change.
A practical 2026 loop: capture the slow statement, read the plan and I/O stats, fix indexes and SQL, keep vacuum and stats current, then adjust config for the residual bottlenecks. That order keeps the cheat sheet short, repeatable, and grounded in how PostgreSQL actually spends time.