PostgreSQL Performance Tuning: A Practical Playbook

Most "slow Postgres" tickets are not Postgres being slow — they are a handful of avoidable defaults, a missing index, and a query plan nobody has looked at. This playbook walks the path our DBAs follow on a tuning engagement, from measuring before you touch anything to the configuration knobs that actually move the needle.

1. Measure before you change anything

Tuning without data is just guessing with extra steps. Turn on pg_stat_statements and let it collect a representative window of traffic first.

-- in postgresql.conf
shared_preload_libraries = 'pg_stat_statements'

-- then, after a reload + real traffic:
SELECT query, calls, mean_exec_time, rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

This single query surfaces the statements burning the most cumulative time. Fix the top five and you have usually reclaimed more than any config change will give you.

2. Read the plan, not the tea leaves

For each expensive query, run EXPLAIN (ANALYZE, BUFFERS). The two things to hunt for:

• Sequential scans on large tables where a selective index should exist.
• Estimate vs. actual row mismatches — if the planner expects 10 rows and gets 100,000, your statistics are stale or your WHERE clause isn't sargable.

Rule of thumb: a 10x or worse gap between estimated and actual rows means the planner is flying blind. Start with ANALYZE, then look at whether the predicate can use an index at all.

3. Index with intent

Indexes are the highest-leverage tuning tool and the easiest to overdo. A few principles that hold up in production:

• Composite order matters. Put equality columns before range columns: an index on (tenant_id, created_at) serves WHERE tenant_id = ? AND created_at > ? far better than the reverse.
• Use partial indexes for skewed predicates, e.g. WHERE status = 'active', to keep the index small and hot.
• Covering indexes with INCLUDE let an index-only scan answer the query without touching the heap.
• Drop unused indexes. Every index is write amplification; check pg_stat_user_indexes for ones with zero scans.

4. The config knobs that matter

Ignore the 300 settings; these are the ones with real impact on a typical OLTP box. Start from your total RAM:

• shared_buffers — ~25% of RAM is a sane starting point.
• effective_cache_size — ~50–75% of RAM; this is a hint to the planner, not an allocation.
• work_mem — per-sort/hash, so size it conservatively and remember it multiplies by concurrent operations.
• max_wal_size — raise it to smooth out checkpoint spikes on write-heavy systems.

5. Keep autovacuum healthy

Bloat is the silent performance killer. On high-churn tables, the global autovacuum defaults are too lazy — tune them per table:

ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_analyze_scale_factor = 0.01
);

Watch n_dead_tup in pg_stat_user_tables. If dead tuples climb faster than autovacuum clears them, you will see plans degrade and disk usage creep regardless of how well you indexed.

The short version

Measure with pg_stat_statements, read plans with EXPLAIN (ANALYZE, BUFFERS), index for your real access patterns, set the four config knobs sensibly, and keep autovacuum aggressive on hot tables. That sequence resolves the large majority of Postgres performance problems before anyone needs to reach for bigger hardware.