CQRS Pattern

Here is the celebrity-timeline failure slowed down so you can see where the naive shared schema breaks and what the split changes.

Problem. A post must commit correctly once, while feeds and history must load in milliseconds for millions of readers. Write shape and read shape diverge.

Naive system. One normalized schema for everything: every feed scroll joins posts, follows, likes, and media. A celebrity timeline query fans out into multi-join work under read amplification while writers wait on the same hot tables.

Break it. Indexes tuned for writes hurt screen queries and vice versa. Dual-writing the DB and cache without one atomic boundary creates gaps. Pretending the read model is always fresh after a 201 lies to the author.

Mechanism. CQRS keeps a write model as the source of truth for business rules, and builds screen-shaped projections for queries. A successful command must not depend on a lagging projection for correctness. The user-visible consistency contract is explicit: the author gets read-your-writes within a budget; followers may see eventual consistency lag of T milliseconds to seconds. Projections are rebuildable; they are not a second write authority.

Real systems already use this split. A social timeline stores the post once, then fan-out writes the post ID into each follower's timeline cache so a GET is a precomputed list (celebrities with 100M+ followers often mix fan-out on read for them with fan-out on write for everyone else). A LinkedIn-style feed keeps activity storage for durability and a relevance-scored precomputed feed per member so writes optimize for durability while reads optimize for latency across hundreds of millions of members.

The mechanism above names projections; this is what they look like in practice, how the UI hides eventual consistency, and the lag budgets you should be able to compute in an interview.

orders:     {id, user_id, status, total, created_at}
order_items: {order_id, product_id, qty, price}
products:    {id, name, category, price}
users:       {id, name, email}

→ 4 JOINs needed for "order history with product details"

order_history: {
  order_id, user_name, user_email,
  items: [{product_name, qty, price}],
  total, status, created_at
}
→ Single read, zero JOINs, sub-millisecond response

product_stats: {
  product_id, product_name,
  total_orders, total_revenue,
  avg_rating, review_count,
  last_30_days_sales
}
→ Pre-computed, updated every event

product_search: {
  product_id, name, description, category,
  price, rating, in_stock, tags
}
→ Full-text search, faceted filtering

POST /orders → 201 Created (write model updated)
GET /orders  → still shows old data (read model not updated yet)
Lag: typically 10ms-2s depending on event pipeline

POST /orders → { order_id: 456, status: "created", items: [...] }
→ Client uses this response directly for UI update
→ No need to query read model immediately

Response header: X-Write-Timestamp: 1709300000
Subsequent read: if read_model.timestamp < X-Write-Timestamp
  → Read from write DB directly (bypass read model)
  → Or wait briefly for read model to catch up

Client: GET /orders?after_version=123
Server: if read_model.version >= 123 → return data
        else → return 304 (not ready) or stale data
Client polls every 500ms until version matches

25_000 writes/sec × 4 read models
  → 100_000 projection updates/sec required

Consumers apply 80_000/sec
  → backlog grows 20_000/sec
  → 60s of writes → 1.2M delayed updates

Alert on age of oldest unprojected event,
not only queue depth.