Overview
Quick takeaways
A practical PostgreSQL tuning workflow covering query plans, indexing choices, join strategy, and the metrics that matter in production.
- Validate row estimates before rewriting the query.
- Look for sequential scans on large tables with highly selective filters.
- Check whether sort, hash, or nested loop operations are spilling or repeating unexpectedly.
Section 01
Start with the execution plan, not assumptions
The biggest gains usually come from understanding where PostgreSQL is already spending time. Before changing indexes or rewriting SQL, I start with EXPLAIN ANALYZE and compare estimated cost with actual rows, timing, and join choices.
That gap between estimated and actual behavior often shows whether the problem is stale statistics, poor selectivity, or a join order that no longer matches production data patterns.
- Validate row estimates before rewriting the query.
- Look for sequential scans on large tables with highly selective filters.
- Check whether sort, hash, or nested loop operations are spilling or repeating unexpectedly.
Section 02
Tune the access path before touching hardware
Production queries usually improve fastest when the access path gets narrower. That can mean a covering index, a more selective composite index, or a query rewrite that lets PostgreSQL use existing indexes more effectively.
I also review whether the query is forcing expensive casts, functions on indexed columns, or wide result sets that make otherwise good plans expensive.
- Design composite indexes around filter and join order, not just column popularity.
- Avoid SELECT * on frequently executed operational queries.
- Use partial indexes when a hot subset of rows drives most reads.
Section 03
Measure improvements in the context of the workload
A query that looks faster in isolation can still hurt concurrency, cache behavior, or write throughput. That is why I compare improvements against real workload signals such as p95 latency, lock time, buffer usage, and overall system pressure.
Good tuning work does not stop at a single query. It confirms that the change makes the system healthier under normal and peak traffic.
- Track p95 and p99 latency after the change, not just average execution time.
- Review buffer hits, I/O pressure, and lock contention alongside query timing.
- Document the reason for each index so future schema changes do not silently reverse the gain.