Interview patterns · Design 1

Scaling reads

How to serve far more reads than one database can handle: caches, read replicas, CDNs and read models, with worked numbers, and how to make sure users still see their own writes.

IntermediateInterview priority: High40 min
On this page
  1. The problem: fifty thousand page views a second, one database
  2. Signals that you are facing it
  3. The idea: answer each read as early and as cheaply as possible
  4. The toolbox
  5. Worked example: sizing the read path for the shop
  6. Read-your-writes: the trap replicas set
  7. When not to scale reads this way
  8. Which case studies use it
  9. In the interview
  10. Quiz

The problem: fifty thousand page views a second, one database

An online shop has 2 million products. At peak, shoppers open 50,000 product pages a second, while sellers change prices and stock about 500 times a second. That is a read-to-write ratio of 100:1, and it is typical: most systems are read far more than they are written.

The first version sends every page view to one Postgres primary. A large, well-tuned primary can serve tens of thousands of simple indexed reads a second while the hot data fits in memory, but a product page is rarely one simple read: it fetches the product, its price, its seller and a review summary. Three things run out at once:

  • CPU: the same queries run over and over, returning the same answers.
  • Connections: each Postgres connection is a separate process using several MB of memory, so a few thousand app threads each holding a connection exhaust the server long before the CPU does.
  • Latency: as the database queues work, a 2 ms lookup becomes 20–50 ms, every page slows down, and the slow pages hold their connections longer, which makes the queue worse.

The cure is not a bigger database. It is to make most reads never reach the primary at all.

Signals that you are facing it

In an interview, the read-scaling problem is hiding behind phrases like these:

  • A high read-to-write ratio: "users browse far more than they post", "a link is created once and clicked millions of times". Anything from 10:1 upwards.
  • A tight latency target on a page that combines lots of data: "the feed must load in under 200 ms".
  • Global users and large static content: images, video, scripts.
  • Expensive reads: joins, counts or rankings computed on every request.
  • Uneven popularity: a viral post, a celebrity profile, a flash sale.

If you see two or more of these, say early: "This is read-heavy, so I'll design the read path in layers so that most reads are answered before they reach the database."

The idea: answer each read as early and as cheaply as possible

Think of the read path as a series of filters. Each layer answers what it can and passes only the rest on. The further from the database a read is answered, the cheaper and faster it is.

Layers that each take a share of the reads away from the database

The order of preference is roughly:

  1. Don't ask again: the browser or app keeps what it already has (HTTP caching headers, local storage).
  2. Answer at the edge: a CDN (content delivery network, a fleet of caching servers in cities around the world) serves public content from near the user.
  3. Answer from memory: a shared cache such as Redis returns a stored copy in about 0.2–0.5 ms.
  4. Answer from a copy: a read replica, a database server that receives a continuous stream of the primary's changes and serves reads.
  5. Answer from a precomputed shape: a read model that already holds the result, so no joins are needed.
  6. Only what is left goes to the primary.

The toolbox

Fix the queries first

Before adding machines, make each read cheaper. An index (a sorted lookup structure the database keeps beside the table, see indexes) turns a full scan into a few page reads. An N+1 query (one query for a list, then one more per item in it) can turn one page view into 51 round trips; a single query with a join or an IN (...) list fixes it. A connection pooler such as PgBouncer lets thousands of app threads share a few hundred database connections. These changes cost nothing to run and often buy 2–10×.

A cache in front of the database

The workhorse. The app checks Redis first; on a miss it reads the database and stores the answer with a TTL (time to live, after which the copy expires). When the data changes, the app deletes the cached key. The caching lesson covers the strategies; here what matters is the effect: at a 95% hit ratio the database sees 1 read in 20.

Catch: copies go stale, invalidation code can be forgotten, a popular key expiring can trigger a stampede, and an empty cache after a restart sends everything to the database at once.

Read replicas

The primary takes every write and streams its change log (the WAL in Postgres, the binlog in MySQL) to replicas, which replay it and serve reads. See replication. Replicas help most with reads that a cache handles badly: the long tail of rarely repeated queries, admin pages, reports and search filters.

Catch: replication is usually asynchronous, so a replica is behind the primary by some replication lag, normally milliseconds but sometimes seconds or minutes. Replicas also add nothing for writes: every replica must replay every write, so they all need as much write capacity as the primary.

CDN and HTTP caching

Public pages, images, video segments and scripts can be cached at the edge with headers like Cache-Control: public, s-maxage=60, stale-while-revalidate=30 (s-maxage applies to shared caches such as CDNs; stale-while-revalidate lets the edge keep serving the old copy for 30 more seconds while it fetches a new one in the background). When content changes, the origin can purge it. See CDNs.

Catch: only content that is the same for many users can be shared. A personalised page has to be split: a cacheable shell plus a small, uncached API call for the personal parts ("Hi Priya, 3 items in your basket").

Denormalisation and read models

Denormalising means storing data redundantly, already in the shape a read needs: a like_count column instead of COUNT(*) over a likes table, or the seller's name copied into each product row. Taken further, it becomes a separate read model: a store built specifically for one kind of read, kept up to date from the main database's changes. A precomputed timeline, a search index in Elasticsearch, and a leaderboard in a Redis sorted set are all read models. Separating the write model from read models like this is called CQRS (command query responsibility segregation).

A read model built from the change log

The changes usually arrive by CDC (change data capture: a process such as Debezium that tails the database's log and publishes every change as an event) or by the app publishing events itself.

Catch: every write now updates several places (write amplification), the read model lags the source, and changing its shape means rebuilding it from history, a backfill that can take hours on a large table.

Comparing the tools

ToolGood atWeak atUse when
Indexes, query fixes, poolingFree speed; fewer round tripsDoesn't add capacity beyond one machineAlways, first
Cache (Redis)Hot, repeated reads; sub-msStaleness, invalidation, cold startsRead:write ≥ 10:1 and a small hot set
Read replicasLong-tail queries; zero app logic for missesLag; no help with writes; full copy per replicaMany varied reads; reports; cache misses
CDN / HTTP cachingGlobal, public, large contentPersonalised data; instant updatesImages, video, public pages, scripts
Read model / denormalisationExpensive reads turned into one lookupWrite amplification; lag; backfillsJoins or aggregates on a hot path

Worked example: sizing the read path for the shop

Peak is 50,000 page reads a second. Assume 60% of traffic is anonymous browsing of public product pages.

LayerArrivesHit ratioAnswered herePassed on
CDN (anonymous pages)30,000/s90%27,000/s3,000/s
Logged-in pages (CDN can't share)20,000/s0%020,000/s
Redis cache23,000/s95%21,850/s1,150/s
Replicas1,150/sall0

So the database tier sees about 1,150 reads a second, a load a single replica handles easily. Redis sees 23,000 operations a second, a fraction of one node's ~100 K ops/s, but run it as a cluster with a replica per shard so one failure doesn't empty it.

The cache memory: the hot 20% of products (400,000) × 2 KB per cached page fragment ≈ 0.8 GB, plus per-key overhead and 25% headroom: comfortably one small node.

Now the second-order question: how many replicas? Not "enough for 1,150 reads a second". If Redis is flushed or fails over, all 23,000 reads a second fall through for the minutes it takes to warm up. Either size the replicas to survive that (at, say, 8,000 page reads a second each, that is 3 replicas plus one spare) or protect the database with a concurrency limit and serve stale or degraded pages until the cache refills. Saying this out loud is what separates a strong answer.

Read-your-writes: the trap replicas set

Replicas and caches answer reads from copies, and copies are behind. Most of the time nobody notices. The one time everyone notices is when it is their own change that is missing.

A missing write, and two ways to hide the lag from the writer

Two guarantees matter here:

  • Read-your-writes: after you change something, you always see the change (other users may see it a little later).
  • Monotonic reads: you never see time go backwards. Without it, one refresh hits an up-to-date replica and shows the new comment, the next hits a lagging replica and the comment disappears.

Ways to provide them:

TechniqueHowCatch
Pin to primary after a writeRemember the time of the user's last write (in the session); for N seconds after it, read from the primaryFails if lag ever exceeds N; extra primary load after busy writes
Position tokenThe write returns the primary's log position; the read waits until a replica has replayed past it, or falls back to the primaryNeeds database support and plumbing through every request
Sticky replicaHash the user ID to one replicaGives monotonic reads, not read-your-writes; breaks when that replica fails
Update locallyThe client shows the change it just made without re-reading itOnly covers the screen that made the change
Read critical data from primaryCheckout, permissions, balances always read the primaryCosts primary capacity; keep the list short

A simple version of the first technique:

import random
import time

STICKY_SECONDS = 10    # must be longer than normal replication lag

def save_bio(session, user_id, bio):
    primary.execute("UPDATE users SET bio = %s WHERE id = %s", (bio, user_id))
    session["last_write_at"] = time.time()

def db_for_read(session):
    if time.time() - session.get("last_write_at", 0) < STICKY_SECONDS:
        return primary                   # this user just wrote: read the source of truth
    return random.choice(replicas)

Postgres supports the position-token version directly. After the write, the app reads pg_current_wal_lsn() (the current position in the write-ahead log) and stores it in the session; before reading from a replica it checks the replica has replayed that far:

-- on the primary, after the write commits
SELECT pg_current_wal_lsn();                         -- e.g. '0/3A0F8812'

-- on a replica, before serving this user's read
SELECT pg_last_wal_replay_lsn() >= '0/3A0F8812'::pg_lsn AS caught_up;

MySQL offers the same idea with GTIDs (global transaction IDs) and WAIT_FOR_EXECUTED_GTID_SET.

When not to scale reads this way

  • The data must be exactly current. Stock at the moment of purchase, account balances, permission checks and seat bookings should read the primary (and often lock, see contention). Cache them for browsing only.
  • Writes are the bottleneck. Replicas and caches do nothing for write load and can make it worse (more invalidations, more replay). See scaling writes.
  • Reads don't repeat. Highly personalised or unique queries get low hit ratios. Indexes, replicas or a search index help; a cache mostly adds a hop.
  • You're not there yet. A single Postgres with good indexes and a pooler handles thousands of page views a second. Every layer is one more system to run, monitor and keep consistent.

Which case studies use it

In the interview

Your turn: a live sports scores site

A sports site shows live scores for 300 matches. During a big final, 2 million people have the page open and it refreshes every 10 seconds. Scores change a few times a minute per match. Design the read path: which layers, what TTLs, and how many requests per second reach your servers?

Compare with our answer
  • Load: 2 M viewers ÷ 10 s = 200,000 requests a second, almost all for the same few matches. Very cacheable.
  • CDN in front of a scores API with s-maxage of 2–5 s: each edge location fetches from the origin at most once per TTL per match, so the origin sees roughly (number of edge locations × matches ÷ TTL), a few hundred to a few thousand requests a second, not 200,000.
  • stale-while-revalidate so viewers never wait on a refresh, and request coalescing at the edge (one origin fetch per key at a time).
  • Behind it, a Redis copy of each match's score, updated when a score changes (write-through), so origin fetches are memory reads.
  • Better still, push instead of poll for the final: see real-time updates.
  • Staleness: a few seconds is fine; note that viewers on different edges may see the goal a second apart.

A strong answer mentions: the 200 K/s estimate · CDN with a short TTL as the main layer · coalescing so a TTL expiry is one origin fetch · a cache or read model behind the origin · the staleness budget · push as an alternative.

Your turn: spot the bug

To take load off the primary, a team sends all reads to replicas. Shortly after launch, customers report that after changing their delivery address at checkout, some orders ship to the old address. Lag is usually 50 ms. What is happening and what would you change?

Compare with our answer

The checkout page saves the new address to the primary, then immediately re-reads the address from a replica to build the order. Usually 50 ms is enough; during busy periods lag grows to seconds and the replica still holds the old address, so the order is created with it.

A strong answer mentions:

  • It is a read-your-writes violation, caused by asynchronous replication lag.
  • A business-critical read (the address used for the order) must come from the primary, or the order should use the value the user just submitted rather than re-reading it.
  • For general pages, pin the user to the primary for a short window after writes, or use position tokens.
  • Monitor replica lag and remove lagging replicas from rotation.
  • Caches filled from replicas can store the old address too: delete or refresh keys after the write.
Scaling reads on one page
  • Trigger: read:write ≥ 10:1, tight page-load target, global users, expensive joins or counts.
  • Filter reads in layers: client cache → CDN → Redis → replicas → read model → primary.
  • Fix queries first: indexes, no N+1, connection pooler.
  • Worked filter: 50 K/s → CDN takes 27 K → cache 95% of 23 K → ~1.15 K/s to the database.
  • Replicas: async, lag usually ms but can spike; full copy each; add nothing for writes.
  • Read-your-writes: primary for N s after a write, or position tokens (Postgres LSN, MySQL GTID). Monotonic reads: sticky replica.
  • Read models (CQRS, CDC): one lookup instead of joins; costs write amplification, lag and backfills.
  • Size the database for a cold cache, or shed load and serve stale. The cache is now your capacity.
  • Never serve stock, balances or permissions from a stale copy when acting on them.
Check what stuck

1. A social app serves 40,000 profile reads per second and 200 profile updates per second. The database is at 90% CPU. Which change gives the biggest relief for the least risk?

2. A team pins each user to the primary for 5 seconds after any write. During a nightly bulk import, users again report that their edits vanish. Why?

3. After a Redis cluster failover, the database that served 2,000 reads per second suddenly receives 30,000 and starts timing out. What is the best long-term defence?

4. Which read is the worst fit for a read replica with asynchronous replication?

5. A profile page needs five joins and a count, taking 40 ms per view at 10,000 views per second. Caching helps little because profiles change often. What fits best?

6. A shop's 60,000 reads per second split 50/50 between anonymous and logged-in pages. The CDN hits 90% of anonymous pages and Redis hits 95% of what reaches the app. Roughly how many reads per second reach the database tier?

7. Refreshing a page sometimes shows a new comment and sometimes hides it again. Which fix targets this symptom directly?

Score 60% or more and the lesson is marked complete.

Passing the quiz also marks it complete. Progress is saved in this browser only.