Stop 20 · System DesignTopics · 4/15

Database Replication

In short

One database means one point of failure and one machine doing every read. Replication keeps live copies on other servers, for safety and to spread the reading load.

Keeping copies of your database on multiple servers — a primary handles writes, replicas handle reads — improving read throughput and providing failover if the primary goes down.

The purple blob at a photocopy shop hands copied sets of notes to waiting students.

Think of it like this

Photocopying the topper's notes

One topper writes the original notes. The photocopy shop makes copies, so twenty students can read at once without fighting over one notebook. But new notes are written only in the original, and yesterday's copies miss today's chapter. That's replication, and replica lag.

Watch it happen

Primary, replicas and lag

API

Primary

Replica 1

Replica 2

INSERT order 981

1/6All writes go to the primary. It's the only copy allowed to change data.

The big ideas

What you need to know

Writes go to one place

In a primary-replica setup, only the primary accepts writes. Replicas are read-only copies.

Scale reads

Most apps read far more than they write. Replicas add read capacity without touching the primary.

Async means lag

Asynchronous replicas can be behind. Read from the primary when a user must see their own fresh change.

Failover

If the primary dies, promote a replica. Managed databases can do this automatically, with a short blip.

See it in code

Walk through the code

1import { Pool } from "pg";2 3const primary = new Pool({ connectionString: process.env.DB_PRIMARY_URL });4const replica = new Pool({ connectionString: process.env.DB_REPLICA_URL });5 6export async function placeOrder(userId: number, total: number) {7  const { rows } = await primary.query(8    "INSERT INTO orders (user_id, total) VALUES ($1, $2) RETURNING id",9    [userId, total],10  );11  return rows[0].id;12}13 14// Product listings can be a second stale; read them from the replica15export const listProducts = () => replica.query("SELECT * FROM products LIMIT 50");

1/3One pool for the primary, one for a read replica.

Two connection pools, one per role. Some ORMs and managed services can route reads to replicas for you, but the trade-off is the same.

Save yourself the pain

Common mistakes

Oops

Reading a user's new order from a replica right after creating it.

Do this instead

Lag can make it look missing. Read the user's own fresh writes from the primary.

Oops

Treating replicas as backups.

Do this instead

An accidental DELETE replicates to every copy almost instantly. You still need point-in-time backups.

Your mission

Primary and replica locally

~45 min

Quick check

A user updates their name, the page reloads and shows the old name. Reads come from a replica. Why?

Go deeper

1Diagram how a write to the primary eventually appears on a replica.
2Learn replication lag and why reads from a replica can occasionally be slightly stale.
3Understand the difference between synchronous and asynchronous replication trade-offs.