How do you choose between SQL and NoSQL and scale each (replication, sharding, federation)?

Step 1: Use Cases, Constraints, Assumptions, Back-of-Envelope

Use cases

We'll scope to handle only the core flows that expose the bottlenecks; everything else is deferred.

• User needs strong transactions — SQL (ACID, joins) for orders, payments, friendships.
• Service needs massive write scale — NoSQL (Cassandra/Dynamo wide-column/KV) for timelines, metrics, crawl frontier.
• Service scales reads — add read replicas (async), measure lag.
• Service scales writes — shard by hash/range, federate by function (users DB vs posts DB).

Out of scope

• Specific vendor benchmarks, graph DB internals, time-series DB compression beyond overview.

State assumptions

• SQL single master: ~5k writes/s max (B-tree + WAL), beyond that shard.
• Read replicas: each adds ~0.8x read throughput, lag 5-20ms DC.
• Shards: 16-256 shards, each 500 GB, rebalancing is live migration