System Design Interview Question: Database Replication vs Sharding

"The database is the bottleneck. How do you scale it?" Walk through the options from cheapest to most complex.

1. Optimise and scale up

Indexes, query fixes, connection pooling and caching first. Then a bigger machine (vertical scaling). Modern servers are powerful; this goes surprisingly far.

2. Replication: scale reads

writes -> [Primary] --async replication--> [Replica 1] <- reads
                                         [Replica 2] <- reads

• Read-heavy workloads spread reads across replicas.
• Replicas give failover: promote one if the primary dies.
• Replication lag: a user may not see their own write if the next read hits a lagging replica. Fix with read-your-writes routing (read from the primary right after writing) or session consistency.
• Synchronous replication avoids data loss on failover but adds write latency.

3. Partitioning within one database

Split huge tables (by date, for example) so queries and maintenance touch less data, while it's still one server.

4. Sharding: scale writes and storage

Split data across multiple independent databases by a shard key:

• Hash-based (hash of user_id): even distribution; range queries across shards are hard.
• Range-based (by date or ID range): easy range scans; risk of hot shards (all new data on the last shard).
• Directory-based (lookup table, e.g. tenant to shard): flexible, but the directory must be fast and reliable.

Choosing a shard key

• Most queries should include it, so they hit one shard.
• High cardinality and even load; avoid keys that create hot spots.
• For multi-tenant SaaS, tenant ID is often natural: a tenant's data lives together.

Costs of sharding

• Cross-shard joins and transactions become hard or slow.
• Rebalancing when adding shards (consistent hashing or many virtual shards help).
• Operational complexity: backups, migrations and monitoring per shard.

Mention managed or built-in options (Vitess, Citus, distributed SQL databases, DynamoDB) that handle much of this for you.