Database Scaling, Explained by Breaking Things: Indexing, Replication, Caching

System design interviews have a standard question: "your database is slow, what do you do?" The expected answer is a ladder. Add an index. Add a cache. Add read replicas. Shard when nothing else works. Most candidates can recite the ladder; the follow-up questions ("what breaks when you add the replica?") are where recitation runs out.

The fastest fix I know for that is not more reading, it is watching each rung fail and get repaired. Below is a walkthrough of the ladder using three free browser simulators, each of which lets you cause the problem before applying the cure. Disclosure: I help build these simulators; they are free, browser-based, and need no signup.

Rung 1: the index, or, stop scanning everything

Before any distributed anything, the boring fix: most "slow database" tickets die at a missing index.

The DB Indexing Simulator gives you a small user table and query buttons: find by email, find by age, find by city, age range. Run them against the bare table and the simulator reports the simulated rows examined by a sequential scan. Then add a B-tree index on the column and run the same query: the execution plan flips from sequential scan to index seek and the examined-rows number collapses. The counts are simplified on purpose (real databases also pay for index and heap visits), but the before/after gap is the real phenomenon.

Two lessons land harder here than in any textbook:

1. The index only helps the query shaped for it. An index on email does nothing for the age-range query. Watching one query drop from "every row" to "a handful" while its neighbor stays slow is the entire mental model of "the database is slow" triage: slow for which query?
2. Indexes are not free. Every write now maintains the tree too. The simulator makes the read side visible; the write tax is the reason you do not index every column, and it is the standard interview follow-up.

The production version of this exercise is EXPLAIN (ANALYZE, BUFFERS) on a real Postgres, which shows actual rows and buffer reads rather than estimates, just with worse graphics.

Rung 2: the cache, or, stop asking the database at all

When the query is already indexed and still too frequent, the next rung is not making the database faster; it is not calling it.

The Caching Simulator runs the cache-aside loop visibly: a request checks the cache, misses, falls through to the database, and populates the cache on the way back; the next request hits. Metrics update as you go: hits, misses, hit rate, and the latency difference between the two paths. The timings are illustrative (the simulated database is deliberately slow and a bit random), but the shape is the one that matters: hits answer immediately, misses pay the full round trip.

The part that makes this simulator worth an evening is eviction. The cache is small on purpose, and you choose the policy: LRU (drop what was used longest ago), FIFO (drop the oldest entry regardless), or LFU (drop what is used least often). Run the same request pattern against each and the hit rate tells you which policy fits which access pattern: LRU rewards temporal locality, LFU rewards skewed popularity and can cling to formerly-hot keys (the simulator uses exact LFU with no decay; production systems like Redis approximate these policies and decay LFU counters), and FIFO gives you the comparison baseline.

What no simulator can fully give you, and the honest caveat: production caching pain is mostly invalidation, deciding when cached data is wrong. The simulator teaches the mechanics and the vocabulary; the two-hard-problems joke stays true.

Rung 3: replicas and shards, or, more machines, new problems

The last rung is the one interviews actually probe: horizontal scaling, and what it costs you.

The Database Replication, Sharding and Scaling simulator is an interactive control panel over a simulated fleet, organized as three independent tabs, and the useful order to visit them mirrors the real decision:

1. Scaling tab. Start vertical: scale the single primary up, watch it absorb more traffic, then hit the ceiling. Buying a bigger box until you cannot is the honest first answer in a design interview, and the tab makes the ceiling concrete with connection pools and error rates.
2. Replication tab. Add read replicas and watch reads spread out, then look at the number that arrives with them: replication lag, with an explicit consistency preference control. The moment you read from a replica you can read the past. Read-your-own-writes is the follow-up this prepares you for: a user updates their profile, the read hits a lagged replica, the update "disappears".
3. Sharding tab. Split the keyspace into write shards and the tab shows the two taxes: query fanout (in its hash-sharded model, a query without the shard key asks every shard; real systems vary but scatter-gather is the general risk) and rebalance movement (adding a shard means data has to move). Those two numbers are why every experienced engineer's answer to "should we shard?" begins with "not yet".

The three tabs teach the shape of the real decision: each step buys headroom and hands you something new to manage. Replicas buy read scale and introduce staleness. Shards buy write scale and introduce routing, fanout and rebalancing. The interview answer that stands out is not naming the steps; it is naming the new problem each one creates.

The ladder, assembled

Next time the question comes: slow for which query? If one query, index it and prove it with the scan count. If the same answers are read constantly, cache them and know your eviction policy and its failure mode. If read volume is the bottleneck, replicas, and now you own replication lag. If write volume is the bottleneck, shard, and now you own the shard key, fanout, and rebalancing. Each step trades a resource limit for a new cost: write overhead for indexes, invalidation for caches, staleness for replicas, routing and rebalancing for shards.

An evening across the three simulators makes each of those sentences something you have watched happen rather than memorized. For the theory behind what you just watched, use the simulators to build intuition, then Designing Data-Intensive Applications to make it rigorous, and PostgreSQL's own EXPLAIN docs to make it practical. (The simulators live alongside the rest of our free DevOps games.)