Database Scaling & Replication

The database is usually the first real bottleneck, because it holds state and cannot simply be cloned like a web server. Scaling it is a sequence of steps, each adding capacity and complexity.

Start with the cheap wins — indexes, query tuning, caching, a bigger machine — before reaching for replicas and shards.

Diagram

How it works

1. Optimise: add the right indexes, fix slow queries, use connection pooling.
2. Cache: put hot, read-mostly data in Redis or an in-process cache.
3. Scale up: move to a larger instance — often the cheapest step.
4. Read replicas: send read-only queries to copies of the primary.
5. Partition: split very large tables by date or key inside one database.
6. Shard: split data across multiple databases when one primary can’t take the writes.

Key concepts

Primary–replica

One node accepts writes and ships its change log to replicas that serve reads.

Replication lag

Replicas can be milliseconds to seconds behind, so a user may not see their own write.

Synchronous replication

The write waits until a replica confirms. Safer, but slower.

Multi-primary

Several nodes accept writes. Needs conflict resolution; use only when required.

Failover

Promoting a replica to primary when the primary fails, manually or automatically.

Trade-offs

Interview questions