Scaling reads

Most products read far more than they write. A link is created once and followed thousands of times. A post is written once and viewed by many. Reads are the first thing to outgrow a single database.

Crux: climb the ladder one rung at a time, cheapest first: make each read cheaper, then spread reads across copies, then avoid hitting the database at all. Each rung buys speed and costs freshness.

The problem

A database has a fixed amount of work it can do per second. If reads dominate, say a ratio of 10 to 1 or 100 to 1 (an assumption to confirm in the prompt), then read capacity is your first ceiling. Latency matters too: a far-away user waits for the network no matter how fast the database is.

Before choosing, ask:

• What is the read-to-write ratio, and what is the peak read rate? (See estimation.)
• How stale may a read be: zero seconds, a few seconds, a day?
• Is the same data read by many users (cacheable), or is each read unique (personal)?

The ladder

Rung 1: make each read cheaper

• Indexes for the columns you filter and sort on. A missing index is the most common cause of a slow read.
• Denormalise where a read would otherwise join many tables: store the answer the page needs in one row, accepting that writes now update two places.
• Precompute expensive aggregates (a count, a leaderboard) into their own table or materialised view, refreshed on a schedule or by events.
• Read only what you need: select the columns, paginate, never return an unbounded list.

This is free in infrastructure terms. Do it first.

• A bigger machine (more memory, faster disks) is also a legitimate early step. It is quick and needs no code change, but it has a ceiling and a price curve that bends upwards, so treat it as buying time.

Rung 2: read replicas

Copy the data to followers. Send writes to the primary, send reads to replicas.

• Use when: the database is the bottleneck, and slightly old data is acceptable.
• Know: replication is usually asynchronous, so a replica can be behind by some amount, the replication lag. It grows under load.
• Careful: a user saves a change and refreshes, but the read goes to a lagging replica, and the change seems lost. A common fix is read your own writes: for a short time after a user writes, send that user's reads to the primary. Writes still go through one primary, so replicas do not help write scaling.

Replicas copy all the data. When the dataset itself is too big for one machine, or a single table's index no longer fits in memory, partition the data so each node holds a slice and reads for a key go to one node. That is sharding, and it changes your queries (anything across slices gets harder), so it comes after replicas. See scaling writes for choosing the key.

Rung 3: caches

Keep hot results in memory, in front of the database (Redis or Memcached are the usual names).

• Cache-aside (the default): the app checks the cache; on a miss it reads the database and stores the result. Simple, and the app controls what is cached.
• Write-through: writes go to cache and database together, so reads stay fresh, at the cost of slower writes.
• TTL (time to live): every entry expires after a set time, which caps how stale it can be. Often enough on its own.
• Invalidation: deleting or updating the entry when the source changes. It is famously error-prone, so prefer a short TTL unless freshness matters.

Problems to name:

• Cache stampede: a hot entry expires, and hundreds of requests all miss and hit the database together. Fix with request coalescing (one request refills, others wait), a lock on refill, or randomising TTLs so entries do not expire together.
• Hot key: one key gets so much traffic that its cache node is the bottleneck. Fix by keeping a small copy in each app server's memory, or storing the key under several names and picking one at random.
• Cache is not the source of truth. Design so the system still works, more slowly, when the cache is empty or down.

Rung 4: CDN and edge caching

A CDN (content delivery network) keeps copies at servers near users. It is best for content that many users share and that changes rarely: images, videos, scripts, and public pages or API responses with a cache lifetime set in the headers.

• Use when: the same bytes go to many users, or users are far from your servers.
• Careful: personal data must never be cached as if it were shared. Know how to purge or version URLs (app.v42.js) when content changes.

Beyond the ladder

If a single primary and replicas still cannot hold the data, you are into partitioning, which is covered next in scaling writes. Separate read models (search index, precomputed feeds) are also an option when reads need a different shape from the stored data.

A worked example: following a short link

Assumptions (labelled, not facts): 100 million link clicks per day, 1 million new links per day, so reads outnumber writes 100 to 1.

• Rate: 100 million per day, using 100,000 seconds as one day, is about 1,000 reads per second on average. If peak is 5 times the average (an assumption), plan for about 5,000 per second.
• Is the data hot? A short code maps to one URL, and that mapping almost never changes. Perfectly cacheable.
• Design: the app checks Redis for the code. On a miss it reads the database, then stores the result with a TTL of a day. A link that is deleted or edited removes its cache entry.
• Effect: if most clicks go to a minority of popular links (an assumption you should say aloud), the cache absorbs the vast majority of traffic, and the database sees mostly misses and writes.
• Failure story: if the cache dies, the database must survive the full 5,000 per second briefly. Check that it can, or add replicas so it can.
• Staleness: a deleted link may still redirect until the entry expires or is purged. For a link shortener that is acceptable, and an explicit delete clears the entry anyway.

Choosing

• Slow query? Index or reshape it. Do not add a cache to hide a missing index.
• Database CPU high, data modest, staleness fine: replicas.
• A few items take most of the reads: cache.
• Same content to many users, far away: CDN.
• Needs always-fresh data on every read: be careful with all of the above, and say what you give up.

Deep dives interviewers use

Queries slow down as the table grows

Check the query plan first: a missing or unusable index is the usual cause. Then reduce what each query has to look at: partition by time so old rows sit apart from recent ones, archive cold data, and keep a compact summary table for the common questions. If the dataset still outgrows one node, shard.

Millions of reads for one cached item

One hot key makes one cache node the limit. Keep a short-lived copy in each app server's own memory (a small local cache in front of the shared one), or store the value under several keys and read a random one. Both trade a little staleness and memory for spreading load. A CDN does the same job for content that is safe to share.

Many requests rebuild an expired entry at once

This is the stampede. Layer the defences: only one request per key may rebuild (a short lock or in-process coalescing); other requests wait briefly or serve the stale value while a background refresh runs; and TTLs are randomised so a batch of entries does not expire together. For the very hottest keys, refresh before expiry.

Updates must be visible immediately

Pick the weakest rule that the product needs:

• Delete the cache entry on write (the next read refills it). Simple, with a small window where a racing read can put an old value back.
• Write through, so the cache holds the new value as soon as the write commits.
• Version in the key (profile:42:v7), so a new version is a new key and old entries die by TTL.
• Bypass the cache for the one user who just wrote (read your own writes), while others see the cached copy.

If the answer must be fresh for everyone on every read, a cache is the wrong tool for that data, and you scale the primary instead.

When not to bring this up

A system with few users, or one whose data is mostly written and rarely read (an audit log), does not need a ladder. Say so, and spend the time on the part that is hard.

Common mistakes

• Adding a cache before measuring or fixing queries.
• Ignoring invalidation, then serving wrong data for hours.
• Ignoring replication lag, and the "my change disappeared" bug.
• Caching personal data in shared caches.
• Forgetting the stampede and the hot key.
• Letting the system depend on the cache so much that a cold cache brings it down.
• Quoting a hit rate with no basis. Say what the hit rate would be and why, and treat it as an assumption.

Check yourself

• Why do indexes come before replicas?
• What is replication lag, and how can a user notice it?
• Name three ways to cut cache stampedes.
• When would you pick a short TTL over explicit invalidation?
• What belongs on a CDN, and what must never be?
• Do read replicas help with write throughput? Why not?