Imagine a hypothetical API whose traffic increases tenfold after a product launch. Database CPU reaches saturation, connection acquisition slows, query latency rises, and application instances retry timed-out requests. The team proposes sharding immediately.
Traffic increased 10× and the database became the bottleneck. Where would you start?
The database may be the visible bottleneck, but the first job is to determine which workload and feedback loop created it. Scaling the wrong layer can add complexity without restoring headroom.
Why the common answer is incomplete
Most candidates answer: “Add read replicas and shard the database.”
Replicas help eligible reads, but they do not fix expensive writes, lock contention, poor indexes, connection storms, or read-after-write requirements. Sharding distributes some load while introducing routing, rebalancing, cross-shard queries, and operational failure modes. If one query or retry loop creates most of the pressure, distributing it only makes the inefficiency more expensive.
A production response protects the database first, measures the dominant workload, and applies the smallest change that restores sustainable capacity.
A production approach
1. Stop the amplification
Apply admission control, request deadlines, bounded queues, and retry budgets before adding capacity. Reject or degrade low-priority work when the database is saturated. Retries should target errors likely to recover and include backoff with jitter. If every application instance opens more connections and retries during slowdown, the system converts a capacity limit into a self-sustaining overload.
2. Measure by query shape and resource
Use query fingerprints to rank total database time, calls, rows examined, rows returned, lock wait, temporary data, and write volume. Inspect connection-pool wait separately from execution time. Segment by endpoint, tenant, and workload class. Average query latency can look acceptable while one high-frequency query consumes most CPU or one batch job blocks interactive writes.
3. Bound connections at the application tier
Size connection pools from database capacity, not from the number of application threads. A fleet that scales from ten to one hundred instances must not multiply open connections by ten without a budget. Use a proxy or pooler when appropriate, release connections promptly, and keep transactions short. Queue briefly in the application rather than letting unlimited work compete inside the database.
4. Fix the dominant query path
Inspect execution plans with representative parameters and data distribution. Add or adjust indexes when they reduce the actual scan and write trade-off. Remove unnecessary columns, repeated queries, and unbounded result sets. Batch operations deliberately. An index that helps one read may slow every write and consume memory, so validate total workload impact rather than optimizing a single example.
5. Reduce avoidable reads
Cache stable, frequently requested data with tenant and authorization context in the key. Use request coalescing to prevent a popular cache miss from creating hundreds of identical database queries. Set expiration from data volatility and correctness requirements. For computed views, consider materialization or precomputation when update frequency is lower than read frequency.
6. Move delay-tolerant work off the request path
Put analytics updates, email preparation, enrichment, and other non-critical work into durable jobs when the user does not need immediate completion. Use an outbox or transactional job record so committed business changes are not lost between the database and queue. Async processing reduces interactive pressure but still consumes database capacity, so rate-limit workers and schedule bulk jobs around service objectives.
7. Add read capacity when semantics permit
Route stale-tolerant reads to replicas and keep read-after-write or transactional reads on the primary. Measure replication lag and expose it to routing decisions. Replicas also provide isolation for reporting and backups. They do not reduce write load and can increase primary work through replication, so add them after identifying that eligible reads are the limiting resource.
8. Partition or shard only with a clear boundary
When a single node cannot meet write volume, storage, maintenance, or availability requirements after simpler improvements, choose a partition key from access patterns and growth. Plan tenant distribution, hot partitions, global identifiers, rebalancing, cross-partition operations, and recovery. Start with a migration path and operational tooling; sharding is a permanent data-ownership decision, not an incident toggle.
Engineering trade-offs
Admission control protects recovery but rejects work. Caching reduces reads while introducing invalidation and staleness. New indexes improve selected queries while adding storage and write amplification. Replicas increase read throughput but add lag and failover complexity. Asynchronous work improves user latency while requiring durable state and eventual-consistency UX.
Vertical scaling can restore headroom quickly and may be the correct incident response, but it should buy time for measurement rather than replace it. Sharding expands capacity at the cost of routing, migrations, and cross-shard constraints. The right choice depends on whether the workload is CPU, I/O, connections, locks, storage, or one hot key.
Common mistakes
- Scaling application instances without capping their aggregate database connections.
- Retrying timed-out queries until the database receives more work than users sent.
- Adding a replica for a write or lock-contention bottleneck.
- Creating indexes from intuition without inspecting plans and write cost.
- Caching data without tenant boundaries or invalidation ownership.
- Moving work to a queue but allowing workers to drain it without rate limits.
- Sharding before defining the query and transaction boundaries.
Failure handling and observability
Monitor database CPU, I/O, memory, active and waiting connections, transaction age, lock waits, replication lag, and storage alongside query fingerprints. At the application, record pool wait, query time, rows returned, deadline, retries, cache status, and workload class. Alert on saturation indicators and queue age before user timeouts become the first signal.
During overload, shed optional work, pause backfills, reduce worker concurrency, and preserve capacity for critical transactions. Kill or quarantine runaway queries through reviewed policies. If a failover occurs, prevent write split-brain and validate replication position before promotion. After recovery, replay durable jobs gradually and confirm retry traffic does not recreate the incident.
Interview insight
The interviewer is not testing whether you can name a horizontally scalable database. They are testing whether you can diagnose resource saturation, control demand, and add complexity only where the workload requires it. A staff-level answer restores headroom first, finds the dominant path, and treats sharding as a last-mile ownership design.