For backend engineers: make slow queries fast and keep production Postgres healthy.
Reach for this when a production database is slow or strained and you need a verifiable fix, not folklore. It walks the real diagnose-to-resolve path: catch N+1s in your ORM, read an EXPLAIN plan down to the bottleneck node, advise the right index, rewrite pathological SQL, decide when to partition, size connection pools, ship migrations with zero-downtime locking, and chase down stale reads from replica lag. Every fix is EXPLAIN-checkable, so you ship the change knowing it actually moved the number.
Click to play with sound.
Arranged in the author's recommended order. Walk through them in sequence, or open any one on its own.
Detect ORM loop-of-queries (N+1) patterns from query logs and eliminate them with batched eager loading, keeping the N+1s that are cheap.
Read a captured EXPLAIN ANALYZE plan, name the single bottleneck node, and prescribe a targeted fix for it.
Recommends, orders, and prunes indexes for a specific query or table – composite column order, selectivity rules, partial and covering indexes, duplicate/unused cleanup, and write-amplification tradeoffs.
Rewrites a structurally inefficient SQL query into a faster equivalent that returns identical results – correlated subqueries to joins or LATERAL, accidental cross joins to explicit ON predicates, OR-across-columns to UNION, SELECT * to projected columns.
Produces a partitioning or sharding plan for an oversized table – range/list/hash strategy, partition-key choice, composite-key and pruning constraints, and a verdict on whether to partition at all.
Diagnoses connection pool exhaustion from captured evidence and sets correct pool sizes and timeouts across app pools and PgBouncer.
Rewrite a schema migration into independently deployable, zero-downtime steps that never take a long-held blocking lock on a live table.
Diagnoses read-replica lag and the stale-read bugs it causes, then applies read-after-write consistency strategies to fix them.