System Design · Lesson 4 of 8 · Updated ·
Databases at Scale — Replication, Sharding, SQL vs NoSQL and CAP
The database is usually the hardest part to scale, because it holds state. App servers can be cloned easily; data must stay correct.
1. First: the cheap wins
Before anything complex:
- Add the right indexes (see Indexing).
- Fix slow queries; avoid N+1 queries.
- Add a cache for hot reads.
- Use connection pooling.
2. Replication: copies of the data
- One primary handles writes; replicas copy its data and serve reads.
- Most apps read far more than they write (often 10:1 or 100:1), so replicas multiply read capacity.
- If the primary fails, a replica is promoted (failover).
Replication lag: replicas may be a few milliseconds behind. After a user updates their profile, read it from the primary for that request ("read your own writes").
3. Sharding: splitting the data
When one machine can't hold the data or handle the writes, split rows across several databases (shards).
| Strategy | How | Watch out |
|---|---|---|
| Range | users A–H, I–P, Q–Z | hot ranges (many names start with S) |
| Hash | hash(user_id) % 4 | even spread; resharding moves data (use consistent hashing) |
| Geographic | India users in Mumbai, US users in Virginia | users who move |
| Directory | a lookup table says which shard | the lookup service must scale too |
Sharding costs: cross-shard joins and transactions become hard, and rebalancing is work. Shard only when you must.
4. SQL vs NoSQL
| SQL (PostgreSQL, MySQL) | NoSQL (MongoDB, Cassandra, DynamoDB, Redis) | |
|---|---|---|
| Data model | tables with a fixed schema | documents, key-value, wide-column, graph |
| Relationships / joins | excellent | limited |
| Transactions (ACID) | strong | varies (often limited) |
| Scaling writes | harder (sharding) | built for horizontal scale |
| Best for | money, orders, users, anything relational | huge volume, flexible data, simple access patterns (chats, logs, feeds, sessions) |
Default to SQL for most apps; add NoSQL for specific needs. Many companies use both.
5. The CAP theorem
In a distributed database, when the network partitions (servers can't talk to each other), you must choose:
- CP — stay consistent: refuse some requests until the network heals. (Banking, inventory.)
- AP — stay available: answer with possibly old data and reconcile later. (Likes, view counts, feeds.)
Eventual consistency: all copies become the same eventually (usually within seconds). Fine for a like count; not for your bank balance.
Common mistakes
- Sharding too early instead of indexing and caching.
- Reading from a replica right after a write and confusing users.
- Picking NoSQL because it's "faster" for data that is clearly relational.
Quick recap
- Index, optimise, cache first.
- Replication: primary writes, replicas read; watch replication lag.
- Sharding: range, hash (consistent hashing), geo, directory — costs joins and simplicity.
- SQL by default; NoSQL for scale and flexible data. CAP: choose CP or AP during partitions.