Database Design • Read-Heavy vs Write-Heavy OptimizationMedium⏱️ ~2 min
Read Heavy Optimization: Precomputation and Locality
Read optimization centers on two principles: move data closer to users and compute expensive operations ahead of time. This minimizes work on the critical read path, trading storage and staleness for dramatically lower latency and cost per read.
Locality optimization employs multiple cache layers. A typical architecture flows from client cache to regional cache to origin database. Meta's infrastructure uses massive cache tiers fronting MySQL, where a single in memory cache node handles 1 to 2 million operations per second. Their fleet serves hundreds of millions of requests per second globally with single digit millisecond median latency from cache. Origin fallbacks can push p99 to tens of milliseconds, but cache hit rates above 95% keep this rare. Edge Content Delivery Networks (CDNs) push static content even closer, achieving sub 10ms p50 latency by serving from memory at the edge.
Precomputation through materialized views avoids expensive joins and aggregations on the hot path. Twitter's timeline uses fanout on write for most users, precomputing timelines into per user inboxes to make reads constant time. This converts a potentially complex multi hop read into a simple range scan. Meta precomputes social graph edges like relationship checks and like counts to avoid traversing millions of connections per request. The system updates these views either synchronously for small fanout or asynchronously via change stream consumers for large fanout.
The cost is complexity in cache invalidation, replication lag causing staleness (typically seconds to minutes), and write amplification. Each additional index or materialized view can add 1x to 3x write overhead. Systems must monitor replication lag using Log Sequence Numbers (LSNs) and implement read after write consistency when needed by pinning sessions to primary databases or requiring replicas to catch up before serving.
💡 Key Takeaways
•Multi layer caching achieves 95%+ hit rates with p50 latency under 10ms: client cache (seconds TTL) to regional cache (Redis at 1-2M ops/sec) to origin (10-50ms)
•Materialized views convert expensive joins into simple lookups: Twitter timeline fanout on write makes reads constant time instead of aggregating from thousands of followed users
•Replication lag trade off: asynchronous replicas keep write latency low but introduce seconds to minutes of staleness, requiring session pinning for read after write consistency
•Write amplification cost: each additional index or materialized view adds 1x to 3x write overhead, must validate impact on write throughput and storage
•Edge CDN deployment serves over 90% of Netflix traffic from local caches with sub 10ms RTT, reducing origin load by orders of magnitude
📌 Examples
Meta TAO uses cache invalidation or updates on writes with asynchronous replication within regions, precomputing relationship edges to avoid multi hop graph traversals
Twitter hybrid fanout: most users get fanout on write (O(1) reads), celebrities trigger fanout on read to avoid writing to 30 million follower inboxes simultaneously
Netflix Open Connect edge appliances serve tens of Gbps each with sub 10ms RTT, caching video content locally to avoid cross ISP transit for 95%+ of bytes