Database & Cache Performance
Diagnosing the storage layer from the outside: slow-query workflow, scan versus index, lock waits with idle CPU, pool saturation, replication lag, hit rates that lie, stampedes and hot keys.
Every lesson below starts from an observable symptom and ends with the measurement that proves the fix worked. Numbers carry a label saying whether they were measured, estimated, simulated or invented to show a shape.
Nine numbers all get reported as "the database is slow" and they mean completely different things. Query duration measured at the application, split by statement, is the one that confirms it — and database CPU is the one that misleads most often.
Capture the statement, read the plan against reality, find where the estimate diverged, then decide which layer the fix belongs to — index, query, schema or application. Adding an index before reading the plan is guessing with extra steps.
The planner chooses a sequential scan over an index for good reasons: selectivity, table size, cache residency and the cost of random page access. Forcing the index because "indexes are fast" is the most confidently made wrong optimization in database work.
Nested loop, hash join and merge join are each optimal somewhere and catastrophic elsewhere. The planner picks one from a row estimate, so a wrong estimate does not make the query slightly slower — it makes the engine choose an algorithm built for a different problem size.
The database is 20% busy and every request takes four seconds. Nothing is overloaded — transactions are standing in line for the same rows. This is the shape that defeats capacity-based reasoning, because adding hardware makes the queue longer, not shorter.
A hundred concurrent requests, twenty connections, eighty in line. The database is 35% busy and every trace blames it, because the pool wait happens inside the span labelled "database" and outside anything the database can measure.
Replicas turn read capacity into a purchase, and the price is time. Lag is not a failure until the application assumes it is zero — and every read-after-write bug in a replicated system is that assumption meeting reality.
Hit rate is a ratio, and the thing that hurts you is a volume weighted by cost. The right question is never "how high is the hit rate" — it is which objects miss, how expensive each miss is, and how much load the misses put on whatever is behind the cache.
One popular key expires and ten thousand concurrent requests discover the miss simultaneously. Each one dutifully queries the database to repopulate it. The database receives ten thousand copies of the same query, and the cache that was protecting it becomes the mechanism that overloads it.
Sharding distributes keys, not traffic. One product goes viral, forty percent of requests land on one key, and the node holding it saturates while the cluster reports comfortable average utilization across every other node.