3.9 Worked examples

① The N+1 problem, costed. A page shows 50 comments, each needing its author's name.

• N+1: 1 query for comments + 50 author lookups = 51 round trips. At 0.5 ms each = 25.5 ms of pure network time, before any work.
• One join: 1 round trip = 0.5 ms.
• Now put the database in another AZ (RTT 1.5 ms): 76.5 ms vs 1.5 ms — 51×. The bug is invisible in development against localhost and catastrophic in production. That's why the chapter says these are "subtle bugs difficult to find by testing."

② Normalize or denormalize? Run the numbers per field. A social feed, 5,000 reads/s, 100 writes/s.

• Post text (changes ~never): denormalizing costs 100 writes/s × fan-out; saves 5,000 lookups/s. Denormalize.
• Like count (changes ~10×/s per hot post): denormalizing into 1M timelines = 10M writes/s. Absurd. Normalize, hydrate on read.
• Username/avatar (changes rarely, but appears in millions of timelines): a single username change would rewrite millions of rows. Normalize, hydrate on read. Exactly the X design — and note the decision differs per field within the same record.

③ Why hydration scales and the join didn't. Hydrating 1,000 post IDs + 1,000 sender IDs:

• Parallelizable: 2 batched multi-gets, not 2,000 round trips
• Cost independent of graph shape: a user following 10 people and one following 10,000 both hydrate ~1,000 timeline entries Contrast the original posts ⋈ follows join, whose cost is linear in followee count — 200 lookups for a normal user, 10,000 for a power user. The join's cost was unbounded; hydration's is bounded by page size.

④ Document locality, measured. A 200 KB user document; a page needs 2 KB of it.

• Read: the database loads all 200 KB — 100× the needed bytes.
• Write: updating one field rewrites all 200 KB. At 100 updates/s that's 20 MB/s of write amplification for 200 B of actual change. "Keep documents fairly small and avoid frequent small updates" is this arithmetic.

⑤ Recursive traversal: Cypher vs SQL, and why depth matters. Finding all locations within the US.

• Cypher: -[:WITHIN*0..]-> — 4 lines total.
• SQL: a recursive CTE, ~31 lines, and you must handle cycles and choose traversal order yourself.
• Fixed 2-hop? A plain SQL join is fine and cheaper. The variable-depth requirement is what buys the graph database its complexity.

⑥ Star schema I/O. fact_sales: 1B rows × 100 columns × 8 bytes = 800 GB. Query touches 3 columns.

• Row store: reads ~800 GB.
• Column store: 1e9 × 3 × 8 = 24 GB; with 4:1 compression, ~6 GB.
• OBT (dimensions folded in): the join disappears, but the fact table grows — say 140 columns. Query still reads only its 3 columns, so OBT costs storage, not scan time. That's the trade.

⑦ Event sourcing rebuild time. 500M events; a projection processes 50,000 events/s. Rebuild = 500e6 / 50e3 = 10,000 s ≈ 2.8 hours. In three years at the same rate: ~8.5 hours. Your rebuild time is your recovery objective, and it grows monotonically forever. This is why snapshots exist, and why "we can always replay" needs a number attached.

---