Database Bottlenecks

What they ask

An open-ended design question in the same long technical session: "Suppose our bottleneck is writing to the database. What do you do? And if the bottleneck is reading from it?" There is no fixed answer; you lead and the two interviewers probe whichever direction you take.

What a strong answer covers

First, measure. Slow query log, EXPLAIN on the top offenders, connection pool saturation, lock waits, replication lag. Say this before proposing anything.

Read bottleneck

• Missing or wrong indexes, N+1 queries from the ORM, selecting more columns than needed.
• Caching at the right layer (application cache, Redis for hot keys, HTTP caching for public content) and the invalidation cost that comes with it.
• Read replicas and routing reads to them, with the consistency caveat.
• Denormalising or precomputing views for the heaviest reports.

Write bottleneck

• Batching inserts and cutting per-row round trips; fewer indexes on write-heavy tables.
• Moving non-critical writes to a queue and processing them asynchronously.
• Partitioning or sharding by a key that matches the access pattern, and what that breaks (cross-shard joins, transactions).
• Vertical scaling as the boring first step, and when it stops being enough.

What they look for

• Diagnosis before prescription, and trade-offs stated for every lever (cache staleness, replica lag, shard complexity).
• Concrete examples from something you actually built, even if small.
• Comfort with being pushed: "the cache did not help, now what?" is the normal follow-up.