Databases, replication & sharding
In one line
Replication copies the same data to several machines for read scaling and availability; sharding splits different data across machines so writes and storage can grow beyond one server.
What it is
Choosing a database starts with access patterns. Relational databases (PostgreSQL, MySQL) give you SQL, joins and ACID transactions and suit structured data with relationships. NoSQL stores trade some of that for scale or flexibility: key-value (DynamoDB, Redis), wide-column (Cassandra), document (MongoDB) and graph databases.
Replication keeps copies of the same data on several nodes. In leader-follower replication one leader takes writes and streams changes to followers that serve reads. Replication can be synchronous (safe, slower) or asynchronous (fast, followers may lag behind).
Sharding (horizontal partitioning) splits rows across several databases by a shard key, so each shard holds only part of the data and takes only part of the writes. It is how systems grow past the write throughput or storage of one machine.
Why interviewers ask
The database is usually the hardest part of a design to scale, so interviewers always ask how data is stored, how it survives failures and how it grows. They want a justified database choice, a sensible shard key, awareness of replication lag, and a plan for hot keys and resharding.
How it works
- 1Pick the store from access patterns: need multi-row transactions and flexible queries? Relational. Huge write volume with simple key lookups? Key-value or wide-column.
- 2Add replication: the leader writes changes to its log (for example the PostgreSQL write-ahead log or the MySQL binlog) and followers replay it. Reads can go to followers; if the leader dies, a follower is promoted (failover).
- 3Understand replication lag: with asynchronous replication a follower may be a few milliseconds to seconds behind, so a user might not see their own write. Fix: read your own recent writes from the leader.
- 4Shard when one leader cannot take the writes or hold the data. Choose a shard key that spreads load evenly and keeps common queries on one shard, such as user_id for per-user data.
- 5Hash sharding: shard = hash(key) mod N spreads data evenly but makes range queries scatter across shards. Range sharding (A to F on shard 1, and so on) keeps ranges together but risks hot shards.
- 6Consistent hashing places shards and keys on a ring so adding a shard moves only about 1/N of the keys instead of nearly all of them, which plain mod N would do.
- 7Accept the costs: cross-shard joins and transactions become hard, so denormalise or keep related data on the same shard.
Worked example · Hash sharding four users across 4 shards
- Suppose hash(user_id) gives: alice -> 17, bob -> 22, carol -> 7, dave -> 40. With 4 shards, shard = hash mod 4.
- alice: 17 mod 4 = 1 -> shard 1. bob: 22 mod 4 = 2 -> shard 2. carol: 7 mod 4 = 3 -> shard 3. dave: 40 mod 4 = 0 -> shard 0. Load is spread evenly.
- A query for alice's orders goes straight to shard 1 because orders are also sharded by user_id. No other shard is touched.
- Now add a fifth shard with mod 5: alice 17 -> 2, bob 22 -> 2, carol 7 -> 2, dave 40 -> 0. Three of four users move. This is why real systems use consistent hashing or many fixed logical shards mapped onto fewer physical servers.
Picture it
Replication is printing several copies of the same phone book so more people can read at once and one lost copy does not matter. Sharding is splitting the phone book into volumes A to F, G to M and so on, so each volume is smaller and can be updated separately.
Key patterns
Leader-follower replication
When: Read-heavy workloads and failover
How: Writes to the leader, reads from followers; promote a follower when the leader fails.
Read-your-writes routing
When: Users must immediately see their own changes
How: Send a user's reads to the leader for a short window after they write, or wait for the replica to catch up to their write position.
Hash vs range sharding
When: Splitting data across machines
How: Hash for even spread of point lookups; range when you need ordered scans, and watch for hot ranges such as 'latest timestamps'.
Logical shards / consistent hashing
When: You expect to add machines over time
How: Map many small logical shards onto fewer servers, or use a hash ring, so growth moves only a fraction of data.
Denormalise by access pattern
When: NoSQL or sharded data where joins are expensive
How: Store data in the shape it is read, duplicating fields where needed, keyed by the query.
Real-world use
Instagram on PostgreSQL
Sharded PostgreSQL into thousands of logical shards mapped onto a smaller number of physical servers, and embedded the shard id in its generated 64-bit IDs so any ID tells you where the row lives.
YouTube and Vitess
Vitess was built at YouTube to shard MySQL transparently behind a routing layer; it is now an open-source CNCF project used by companies such as Slack.
Amazon DynamoDB
Hashes each item's partition key to place it on a partition, splits partitions as they grow, and replicates each partition across three availability zones.
Read replicas in managed databases
Amazon RDS and Cloud SQL let you add asynchronous read replicas with a click, a common first step to scale read-heavy apps before sharding.
In a project
A SaaS invoicing product has grown to 4 TB in one PostgreSQL primary, writes peak at 15,000 per second, and the nightly backup and vacuum jobs are causing slowdowns.
- Move reporting queries to two read replicas, and route a user's reads to the primary for 5 seconds after they write to avoid lag surprises.
- Choose tenant_id as the shard key because every query is scoped to one customer account, so queries stay on one shard.
- Create 256 logical shards mapped onto 8 physical PostgreSQL clusters, with a lookup table from tenant to logical shard, so future growth moves whole logical shards rather than rehashing rows.
- Migrate tenants gradually: dual-write, backfill, verify counts and checksums, then switch reads per tenant.
- Give the largest tenants dedicated shards to avoid hot spots.
Impact: Each shard holds about 500 GB and under 2,000 writes per second, p99 query latency drops from 350 ms to 60 ms, and adding capacity becomes a routine move of logical shards instead of a risky migration.
Trade-offs
Use it when
- Replication: more reads than one node can serve, or you need high availability
- Sharding: write rate or data size beyond one well-tuned primary
- NoSQL: simple key-based access at very high scale with flexible schema
Avoid it when
- Sharding early: you lose easy joins, global transactions and simple operations
- Reading from async replicas when the user must see the latest data (balances, just-saved settings)
- A shard key with low cardinality or skew (country, created date) that creates hot shards
Common mistakes
- Confusing replication (same data, more copies) with sharding (different data on each node).
- Picking a shard key that does not match the main query, causing scatter-gather on every request.
- Using hash mod N and forgetting that changing N moves almost every key.
- Ignoring replication lag and promising users they will see their write immediately on a replica.
- Choosing NoSQL by default without naming the access pattern that needs it.
Interview questions
Quick revision
- Replication = same data, more nodes (reads, availability). Sharding = split data (writes, size).
- Leader-follower: writes to leader, reads from followers, promote on failure.
- Async replication is fast but followers lag; sync is safe but slower.
- Shard key: high cardinality, even load, matches the main query.
- hash mod N moves most keys when N changes; consistent hashing moves about 1/N.
- Cross-shard joins and transactions are expensive; denormalise or co-locate.
- SQL for transactions and relationships; NoSQL for simple access at huge scale.
Check yourself
1. What is the main purpose of sharding?
2. With asynchronous leader-follower replication, which problem can occur?
3. Why is created_at timestamp often a poor shard key with range sharding?
4. A system uses shard = hash(key) mod N. What happens when N goes from 4 to 5?
See it in a full design
These walkthroughs use this lesson.
Saved on this device. Create a free account to keep it on every device.
Practise this with Sam
A real mock interview, out loud, with an honest scorecard. Free to start.