Database Performance
The interventions that resolve database bottlenecks, in the order they should be attempted.
Definition
The database is the most common bottleneck in application systems, because it is usually the only component that is not trivially horizontally scalable and the only one holding shared mutable state.
The order of intervention
Attempted in this order, each step is cheaper and less irreversible than the next:
1. Fix the queries. Query plans, missing indexes, N+1 patterns in application code, SELECT * over
wide rows, deep offset pagination, COUNT(*) on large tables for UI purposes. The majority of database
problems are resolved here and the fix takes hours.
2. Fix the connection handling. A pooler between the application and the database. Without one, many instances each holding a pool exhausts the connection limit, and connection setup dominates the work.
3. Cache. Move repeated reads out of the database entirely, with an explicit staleness decision per data type.
4. Read replicas. Move read load off the primary, accepting replication lag and handling read-after-write for the acting user.
5. Functional separation. Move one heavy domain to its own instance. Cheaper and far more reversible than sharding, and frequently sufficient.
6. Shard. Last, because it is the only step that changes the application's model of the world: no cross-shard joins or transactions, N migrations in different states, no cheap change of mind.
The specific things that cause incidents
- Long-running transactions holding locks, causing a queue whose length is set by your slowest code path. Especially: a transaction held open across a call to a third party.
- Lock ordering inconsistency across code paths, producing deadlocks under load.
- Stale statistics after a bulk load, so the planner picks a nested loop over millions of rows.
- A missing index discovered at scale, where the table was small enough for a scan until it was not.
- Autovacuum or maintenance falling behind under sustained write load, so bloat degrades everything.
- A reporting query on the primary during business hours.
Monitoring that matters
Slow query log, but also: queries by total time (frequency times duration, not worst case), lock waits, connection pool saturation, replication lag, cache hit ratio, and index usage — so unused indexes taxing every write can be removed.
Interview question
"Walk me through the order in which you would address a database bottleneck for an application with ten million users, and justify why sharding is last."