Skip to content
Tutorials Center
My learning

Topics

Lesson 91 of 125 ยท Performance

Replication, Read Replicas and Sharding

Learn how databases scale beyond one server. Understand replication, read replicas, replication lag, sharding keys and the consistency tradeoffs of each.

You have tuned your queries, added the right indexes and given the server more memory. Eventually a single database server is still not enough: too many reads, too many writes, too much data, or the simple fear of what happens when that one machine dies. At that point you scale out by using more than one server.

Two techniques do most of the work. Replication keeps copies of the same data on several servers, which helps with read capacity and availability. Sharding splits the data itself across servers, which helps with write capacity and total size. Both come with a price in complexity and consistency, and this lesson explains exactly what you pay.

Scale up first

Before distributing anything, check that you need to. A single modern server with tuned queries handles an astonishing amount of work: thousands of transactions per second and terabytes of data. Read Query Optimization and Database Tuning first. Caching with a tool like Redis can also absorb a large share of repeated reads. Distributed databases are harder to build, run and debug, so add them when measurements say you must.

Replication: copies of the data

In primary-replica replication (older documents say master-slave), one server accepts writes and one or more replicas receive a copy of every change and serve reads.

flowchart LR
    APP["Application"] -->|"writes"| P["Primary"]
    APP -->|"reads"| R1["Replica 1"]
    APP -->|"reads"| R2["Replica 2"]
    P -->|"change stream"| R1
    P -->|"change stream"| R2

Writes go to one primary; reads can be spread over many replicas that follow it.

How does a replica stay up to date? The primary already writes every change to a log before applying it (the write-ahead log in PostgreSQL, the binary log in MySQL), so it can send that log to the replicas. Each replica replays the changes in the same order. You can imitate the idea in plain SQL: a trigger records every change in a log table (see Triggers), and the "replica" applies the log up to some position.

SQL A replica that is two changes behind

Press Run (or Ctrl + Enter) to see the result here.

The replica still shows the old balances. Now let it catch up by replaying entries 3 and 4, and the two copies agree again. Try changing seq <= 2 to seq <= 4 and re-running. The gap between primary and replica is called replication lag. In practice it is usually milliseconds, but it can grow to seconds or minutes when the primary is busy or the network is slow.

Replication lag and consistency

Lag is the central tradeoff of read replicas. Because the replica may be behind, a user can write something and then not see it:

sequenceDiagram
    participant U as User
    participant P as Primary
    participant R as Replica
    U->>P: UPDATE profile SET name = 'Ada'
    P-->>U: OK
    U->>R: SELECT name FROM profile
    R-->>U: old name (change not applied yet)
    P->>R: replicate the change

A read that hits the replica before the change arrives returns stale data.

This is eventual consistency: if writes stop, all replicas converge, but at any moment they may differ. Common ways to cope:

  • Read your own writes. After a user changes something, send that user's next reads to the primary for a few seconds, or until the replica has reached the log position of the write.
  • Send critical reads to the primary. Checking a balance before a transfer must not use stale data. Reports and product pages usually can.
  • Pin a session to a replica so that a user never sees data go backwards between two page loads.
  • Synchronous replication makes the primary wait for at least one replica to confirm each commit. Nothing is lost if the primary fails, but every write gets slower, and if the replica is down writes may stall. Asynchronous replication (the default in most setups) is faster but can lose the last few commits in a crash.

Replicas also give you high availability. If the primary dies, you fail over by promoting a replica to primary. Tools and managed services (Amazon RDS, Cloud SQL, Patroni for PostgreSQL, Orchestrator for MySQL) automate this, but you must decide how much recent data you can afford to lose. Replicas are not backups: a mistaken DROP TABLE is replicated to every copy within moments. See Backup and Maintenance.

Replication solves read scaling and availability. It does not help with writes, because every write still has to happen on the single primary (and be replayed everywhere). When writes or data volume outgrow one machine, you need sharding.

Sharding: splitting the data

Sharding divides rows among several independent databases, called shards. Each shard holds a subset of the data and handles reads and writes for it. Orders for customers 1-1,000,000 might live on shard A, the next million on shard B, and so on.

flowchart TB
    APP["Application<br/>(routing layer)"] -->|"customer 7"| S1["Shard 1"]
    APP -->|"customer 8"| S2["Shard 2"]
    APP -->|"customer 9"| S3["Shard 3"]

The router uses the shard key to pick which database holds each row.

The most important decision is the shard key: the column that decides where a row lives. A good key:

  • is present in nearly every query, so the router can go straight to one shard;
  • spreads data and traffic evenly;
  • keeps related rows together (all of a customer's orders on the same shard, so joins stay local).

There are three common schemes:

Scheme How it works Strength Weakness
Range key ranges per shard (ids 1-1M, 1M-2M) Range scans stay local; easy to understand Hot spots: new rows all land on the newest shard
Hash hash(key) % N picks the shard Even spread Range queries touch every shard; changing N moves most rows
Directory (lookup) a table maps each key to a shard Fully flexible, easy to move one tenant The lookup table is a bottleneck and must stay correct

You can see the difference between range and hash sharding in plain SQL. Here, recent users (ids 9 to 12) are by far the busiest, which is typical:

SQL Range versus hash sharding under skewed traffic

Press Run (or Ctrl + Enter) to see the result here.

With range sharding, one shard takes 16 of the 20 events, while the hash spreads them as 10, 5 and 5. (Hash is not perfect with so few rows, but it never piles up on the newest ids.) Real systems use a good hash function rather than % 3, and often consistent hashing or many small "virtual shards" so that adding a server moves only a fraction of the data.

What sharding makes harder

Sharding is powerful and expensive. Plan for these costs:

  • Cross-shard queries. A query without the shard key must ask every shard and merge the answers (scatter-gather). Cross-shard joins and transactions are slow or unsupported.
  • Unique ids. AUTO_INCREMENT on each shard produces duplicates, so use UUIDs, time-ordered ids or a central id service (see Auto-Increment).
  • Resharding. Moving from 4 shards to 8 means moving data while the system runs.
  • Operations. Schema changes, backups and monitoring multiply by the number of shards.

Merging answers needs care. The average of averages is wrong when shards differ in size. Each shard must return a sum and a count:

SQL Scatter-gather: combine partial results correctly

Press Run (or Ctrl + Enter) to see the result here.

Shard 2 is small, yet it counts as much as shard 1 in the naive result. Summing totals and counts first, then dividing once, gives the true average. The same rule applies to any aggregate that is not simply additive (see Aggregate Functions).

Choosing a strategy

Work through the options in order, and stop as soon as one is enough:

  1. Fix queries and indexes.
  2. Add caching for hot reads.
  3. Add read replicas, and handle lag in the application.
  4. Move rarely used or large tables elsewhere ("vertical" partitioning, such as moving logs to their own database).
  5. Partition big tables inside one server (see Table Partitioning and Tablespaces).
  6. Shard, or move to a database designed for horizontal scale (see SQL and NoSQL). Managed "distributed SQL" systems such as CockroachDB, YugabyteDB, Vitess for MySQL and Citus for PostgreSQL do the routing and resharding for you, with their own tradeoffs.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What does adding read replicas mainly help with?

    Replicas serve reads and can be promoted if the primary fails. All writes still go through the single primary.
  2. 2. A user saves a change and immediately reloads the page, but sees the old value. What is the most likely cause?

    Asynchronous replicas can be slightly behind. Reading your own writes from the primary hides this.
  3. 3. Why are replicas not a replacement for backups?

    Replication copies every change, including mistakes. Backups keep older points in time you can return to.
  4. 4. Which problem is typical of range-based sharding on an auto-increasing id?

    Recent rows have the highest ids and are usually the most active, so one shard takes most of the traffic.
  5. 5. Three shards return their own AVG(total). What is the correct way to get the overall average?

    Averages are not additive. Combining sums and counts weights each shard by its size.

Summary

  • Scale up and optimize first; distribute only when measurements demand it.
  • Replication copies changes from a primary to replicas via a change log. It scales reads and gives failover, but replicas can lag, so reads may be stale.
  • Handle lag with read-your-writes routing, primary reads for critical data, or synchronous replication (slower writes).
  • Sharding splits rows across servers by a shard key, scaling writes and storage. Range, hash and directory schemes trade hot spots, evenness and flexibility.
  • Sharding makes cross-shard queries, unique ids, transactions and resharding hard. Combine partial aggregates as sums and counts.
  • Replicas are not backups.

Next: Table Partitioning and Tablespaces, which splits large tables inside a single server.