database-performance

# Database Performance ## Purpose Make the database fast by finding what it is actually spending time on. Database performance work fails when it starts from intuition; it succeeds when it starts from `EXPLAIN ANALYZE` and the slow-query log. ## When to Use - An endpoint is slow and the database is suspected. - CPU or I/O on the database server is saturated. - Lock waits, timeouts, or deadlocks in production. - Planning for a tenfold increase in data volume. ## Capabilities - Query profiling and execution-plan analysis. - Index strategy: what to add, and what to remove. - Lock contention and deadlock diagnosis. - Connection pool sizing. - Denormalization, materialization, and partitioning as scaling tools. ## Inputs - The slow query log, or the top queries by total execution time. - Actual execution plans, with row counts. - Table sizes, index sizes, and hardware. ## Outputs - The identified bottleneck, with evidence. - A change — index, query rewrite, or schema — and its measured effect. - Monitoring so the regression is caught next time. ## Workflow 1. **Rank by total time, not by mean** — The query taking 20ms and running 50,000 times per minute is the problem. The 3-second report that runs hourly is not. 2. **Read the plan** — `EXPLAIN (ANALYZE, BUFFERS)`. Look for: a sequential scan on a large table, an estimate that differs from the actual by more than 10x, and a nested loop over many rows. 3. **Fix the biggest thing** — Usually a missing index, an N+1 from ...