Sign in Get started

Scaling Databases & Data Consistency · Capstone Case Studies

Capstone: Scale a Read-Heavy Catalog 100×

A marketplace catalog must go from 2,000 to 200,000 reads per second — without rewriting the product.

Scenario

Example company: a marketplace with 30 million listings in Postgres. Today: 2,000 catalog reads/s, 150 writes/s (sellers editing listings, stock changes), one primary + one replica. A partnership with a large search engine and a TV campaign are expected to bring 100× read traffic within six months. Writes will grow maybe 3×.

Current pain points:

  • Listing pages run 9 queries each, including an N+1 for seller ratings.
  • The "category browse" query uses OFFSET pagination and a count(*).
  • Sellers complain that edits sometimes don't show on their listing page.

Requirements

  • p99 < 200 ms for listing pages and category browse at 200,000 reads/s.
  • Sellers always see their own edits immediately.
  • Price and stock shown to buyers may be up to 30 s stale on browse pages, but checkout must use fresh data.
  • Cost-conscious: don't multiply database spend by 100.

Deliverables

  1. Fix the query layer before adding hardware: what changes and why.
  2. The read architecture (caches, replicas, read models) and which reads go where.
  3. Consistency rules: seller read-your-writes, buyer staleness, checkout freshness.
  4. Failure modes and protections (cache stampedes, replica lag, cold starts).
  5. A rollout and load-testing plan with the metrics you'd watch.

Hints

  • Classify every read by freshness need first.
  • 100× reads with 3× writes is a caching problem much more than a database problem.
  • Think about what happens at minute 0 of the TV ad when the cache is cold.

Draft each deliverable below before revealing the model answer and rubric.

Further reading: Use The Index, Luke · PostgreSQL: high availability and replication

Deliverables

Fix the query layer first.

Model answer

  • Eager-load seller ratings (one IN query or a denormalized seller_rating on the listing) → listing page from 9 to 2–3 queries.
  • Composite indexes from the real query list (e.g. (category_id, status, popularity DESC, id)), verified with EXPLAIN ANALYZE on production-size data.
  • Keyset pagination for browse; replace count(*) with "many results" or an estimate.
  • pg_stat_statements before/after to confirm total time per page dropped.

Rubric

Look for: measuring first, fixing N+1, index design matched to queries, removing OFFSET/count. Misses: jumping straight to replicas or a bigger instance.

Design the read architecture.

Model answer

  • CDN / HTTP caching for anonymous browse and listing pages (short TTL + stale-while-revalidate) — absorbs most of the 100×.
  • Redis cache-aside for listing and seller objects (TTL 30 s + event-driven invalidation from the outbox/CDC).
  • Read replicas behind a pooler for cache misses and non-cacheable reads; size replicas for the miss rate, not total traffic.
  • Read model for category browse (precomputed sorted lists per category, or a search engine fed by CDC).
  • Primary only for writes, seller dashboards and checkout.

Rubric

Look for: layered caching with most traffic never reaching the database, replicas sized by miss rate, a read model or search index for browse, and a clear routing table. Misses: 100 replicas, caching checkout data.

Define the consistency rules.

Model answer

  • Sellers: after an edit, their reads go to the primary for N seconds (sticky marker) or wait for the replica LSN; their cache entries are invalidated synchronously on save.
  • Buyers on browse: up to 30 s stale is acceptable → CDN/Redis TTL ≤ 30 s, replica reads allowed.
  • Checkout: reads price and stock from the primary inside the reservation transaction (conditional update), never from cache.
  • Show "price may have changed" if checkout's fresh price differs from the cached one.

Rubric

Look for: explicit per-path guarantees, read-your-writes for sellers, fresh data at checkout with atomic reservation, and UX for price changes. Misses: one global staleness rule.

Failure modes and protections.

Model answer

  • Cold cache at campaign start: pre-warm top categories/listings; stale-while-revalidate; request coalescing so one miss per key hits the database.
  • Stampede on popular keys: singleflight/locks and jittered TTLs.
  • Replica lag: lag-aware routing with fallback caps; shed non-essential reads.
  • Cache outage: circuit-breaker to a degraded mode (serve CDN stale, disable personalization) rather than sending 100× load to the database.

Rubric

Look for: cold start, stampede, lag, and cache-down scenarios each with a protection. Misses: assuming the cache is always warm and up.

Rollout and load testing.

Model answer

  • Load test at 2× the target with realistic key popularity (Zipf), including a cold-cache start and a cache flush mid-test.
  • Watch: hit ratio per layer, database QPS and total time, replica lag, pool waits, p99 by endpoint, error rate.
  • Roll out layer by layer behind flags (CDN rules, Redis cache, routing) with the ability to disable each instantly.

Rubric

Look for: realistic load tests including cold starts and failures, layer-by-layer rollout with kill switches, and the right metrics. Misses: uniform random keys in load tests.

Open in the app to mark lessons complete, take notes and track your progress.