How to Scale a Database: Replication, Caching and Sharding

Sooner or later one database server isn't enough: too many reads, too many writes, or too much data. There are three main tools, each aimed at a different bottleneck: replication (copies, to scale reads), caching (hot data in memory, to scale reads further) and partitioning or sharding (split the data, to scale writes and storage). This page shows how they fit together; each has its own deep-dive page.

Vertical vs horizontal scaling

Vertical scaling is like upgrading your computer: add CPU, memory or disk to the one server. It's the quickest and easiest step, but it gets expensive, the biggest machines still have limits, and cloud providers only offer certain hardware sizes, so there's a ceiling. Horizontal scaling connects many machines to share the work: keep adding machines as needed, which is more flexible and usually cheaper at scale. Because of vertical scaling's ceiling, system designs almost always scale horizontally. See scalability.

Terminology: machines connected to work together form a distributed system, and each machine is a node. "Machine" and "node" mean the same thing in the rest of these guides.

Scaling reads with replication

Replication keeps extra copies of the data on other machines (replicas) and lets clients read from them, so more read requests can be served at once. The most common setup is single-leader (primary–replica) replication, the default in relational databases:

• Writes: the client writes to the primary, which asynchronously sends each change to the read replicas.
• Reads: the client can read from any replica.

Replication also gives redundancy (several copies, so a failed machine's traffic can be redirected with little disruption) and high availability (the service keeps running when some machines break). Multi-leader and leaderless replication are covered in replication.

Scaling reads with caching

A cache keeps frequently read data in fast storage, usually memory. The goal is for the cache to answer as many requests as possible and touch the database only on a miss. See caching.

Scaling writes with partitioning (sharding)

Replicas copy all the data and every write still hits the one primary, so replication doesn't help writes. Partitioning (sharding) splits a table's rows across machines: each partition has the same schema but different rows. A write goes to just one partition, so write load (and storage) spreads over all of them, and adding machines adds capacity.

The field that decides where a row goes is the partition key. Since the goal is to spread write load, keys should distribute as evenly as possible. The simplest scheme hashes with MOD: partition = user_id % 4.

Simple, but three problems appear quickly:

1. Range queries: with hash partitioning, "all records with user_id between x and y" must ask every partition. Fine for IDs, painful for fields you count or scan by range (timestamps, prices).
2. Hot partitions: in a social app where users comment on each other's profiles, partitioning comments by profile owner sends a celebrity's flood of comments to one partition, which runs far hotter than the rest. See hot partitions.
3. Rebalancing: when partitions fill up and you add a 5th, % 4 no longer covers it. Switching to % 5 changes the partition of most keys, so data moves across all partitions. Consistent hashing fixes this.

Partitioning strategies, range vs hash keys and rebalancing are covered in sharding.

Partitioning and replication together

Real systems combine both for fault tolerance: each partition is copied to several nodes. A record lives in exactly one partition, but that partition lives on several nodes, and each node is the leader for some partitions and a follower for others. If a node fails, nodes holding replicas of its partitions keep serving.

Summary and a typical order of moves

1. Optimize first: indexes, query fixes, connection pooling. See database optimization.
2. Scale up the machine while it's cheap.
3. Add a cache for hot reads, then read replicas.
4. Split by function (separate databases per service or domain). See database per service.
5. Shard when writes or data size outgrow one primary.