Episode 8. Performance tuning. Day 8. The engine room. Top SQL, waits, contention.

Key facts. Performance Insights shows DB load by wait event. Enhanced Monitoring shows OS counters. pg_stat_statements ranks query cost. EXPLAIN with ANALYZE and BUFFERS shows the plan truth. Autovacuum reclaims bloat and freezes XIDs. Say: Tune by waits first, plans second, parameters last.

Scenario 1. One query is slow only sometimes. Same SQL, wildly different times. Help.

Understand. Ask: slow when: time of day, batch windows, traffic peaks? Same parameters or different binds? Plan stable or flipping? Locks or waits during the slow runs?

Prepare. Say the data in order. Performance Insights wait breakdown during slow windows. pg_stat_statements mean versus max time. EXPLAIN ANALYZE on fast and slow binds. Lock and buffer wait detail. Autovacuum progress and table bloat estimates.

Say your translation line: Same instinct as bind peeking and plan flip: parameters change the plan, and statistics decide.

Identify. Rank the hypotheses. One: plan flip from skewed binds or stale statistics. Two: lock contention or buffer busy waits under concurrency. Three: bloat from lagging autovacuum inflating heap reads. Four: IOPS or CPU saturation at peak. Fix: correct statistics and targeted indexes, tune autovacuum for the hot tables, add plan stability, then size up.

Support. Success: p95 of the target query stable for seven days, wait share of the top event halved, and an alarm on plan change plus bloat ratio.

Scenario 2: read scaling choice. Say: I'd add Aurora Replicas for read heavy skew, caching for repeatable hot reads, and partitioning for write hot tables, in that order. Then commit. Say: my recommendation is. Know the cost of each before recommending.

Your Day 8 lab: create bloat on a test table, run ANALYZE and VACUUM, compare EXPLAIN BUFFERS before and after. Delete the instance the same day.

Tomorrow, Episode 9: distributed theory. CAP, Paxos, Raft, and quorum. The Principal filter. Understand. Prepare. Identify. Support.