Replication

By the end of this lesson you'll be able to reason about a production database the way an on-call engineer does: choose synchronous vs asynchronous replication for a given durability requirement, route reads to replicas without serving stale data, and survive a server failure with a clean, fenced failover.

Part of the free SQL course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

What You'll Learn

The Picture: One Primary, Many Replicas

Replication means keeping live copies of your database on more than one server. Exactly one node — the primary (a.k.a. leader/master) — accepts writes. The others — replicas (a.k.a. standbys/followers) — continuously copy the primary's changes and serve read-only queries. Picture a store: only the manager restocks shelves (writes), but every checkout counter can serve customers (reads).

Writes flow in one direction : client → primary → replicas. That single rule is what keeps the copies consistent — and it's why the interesting questions are all about timing (how far behind are replicas?) and failure (what if the primary dies?).

1. Primary vs Replica — Knowing Who You Are

Before anything else, a server needs to know its own role, because a replica that thinks it's a primary is a disaster waiting to happen. PostgreSQL answers this with one function, and you'll use it constantly — especially right after a failover.

The primary is the shop's manager (the only one allowed to restock), and replicas are the cashiers (they serve customers but can't change stock). When the manager is out, you promote one cashier to acting manager — but first you make sure the old manager can't quietly keep restocking too.

t (true) means read-only replica; f (false) means it's the primary and accepts writes. This is the check your failover automation runs to confirm a promotion actually worked.

2. Asynchronous Replication & Replication Lag

Asynchronous replication is the common default: the primary commits a change the instant it has the change safely on its own disk, then streams that change to replicas afterwards . It never waits for them. That makes writes fast, but it introduces replication lag — the gap (usually milliseconds) between the primary having data and a replica having it.

Async is like posting a letter : you drop it in the box and carry on; the recipient reads it whenever it arrives. The number you must watch is the lag, because it's also your worst-case data loss if the primary dies before a replica catches up.

3. Synchronous Replication — Trading Latency for Durability

Synchronous replication flips the deal: the primary will not tell your application COMMIT succeeded until a replica has also stored the change. That guarantees zero data loss if the primary instantly explodes — but every write now pays a network round-trip, so writes are slower. Sync is a phone call (you wait for "got it"); async is a letter.

This is the central trade-off of the whole lesson: durability vs latency . You don't have to choose once for the whole database — PostgreSQL's synchronous_commit setting can be changed per transaction , so money-movement can be synchronous while analytics logging stays fast and async.

remote_apply is the strictest level: COMMIT waits until the replica has applied the change, so a read on that replica is guaranteed to see it. off is the loosest: fastest writes, but you may lose the last few committed transactions in a crash.

Your Turn: sync or async?

You're recording a bank transfer that must never be lost on failover, even if it makes the write slower. Fill in the blank with the right synchronous_commit level. The expected answer and why are in the comments.

4. Streaming/Physical vs Logical Replication

There are two mechanisms for copying the data. Physical (streaming) replication ships the raw write-ahead log (WAL) — a byte-for-byte copy of the entire cluster. It's cheap, fast, and what you use for high availability and read replicas, but it's all-or-nothing and tied to the same major version.

Logical replication instead decodes changes into rows and replicates table-by-table using a publish/subscribe model. That lets you copy only certain tables, replicate across different major versions, or funnel several databases into one warehouse — at the cost of more moving parts (DDL changes and sequences aren't carried over automatically).

5. Read Scaling — Routing Reads to Replicas

Because replicas hold a full copy of the data, you can spread your read traffic across them while the primary handles only writes. Your application keeps two connection pools and routes each query by type: SELECT goes to a replica, anything that writes goes to the primary. Add more replicas, serve more reads — that's horizontal read scaling.

Your Turn: route the read & spot the risk

Send a heavy dashboard query to the right pool, then explain the lag risk in a comment. Both blanks have their expected answers in the code.

6. Failover & Promotion — Surviving a Dead Primary

When the primary fails, the show must go on: a replica is promoted to become the new primary that accepts writes. You can do this manually (run pg_promote() on a replica) or automatically with a cluster manager like Patroni, which detects the failure and promotes the healthiest replica within seconds — your app just reconnects through a pooler with no code change.

Deep Dive: Why Not Just Have Multiple Primaries?

It's tempting to let every node accept writes ( multi-primary , a.k.a. multi-master) for even more write capacity and no failover step. The catch is conflicts : if two primaries update the same row at the same time, which value wins? You're now signed up for conflict-resolution rules, possible lost updates, and far harder reasoning about consistency.

For the vast majority of apps, a single primary plus read replicas is simpler, correct by construction, and scales reads — which is the bottleneck most systems actually hit first. Reach for multi-primary only with a clear need (e.g. multi-region active-active writes) and eyes open to the trade-offs.

Common Errors (and the fix)

• Reading stale data from a lagged replica: a user saves, reloads, and the change is "missing" because they hit a behind replica. Fix: read-your-writes — route that user to the primary briefly, or make the write synchronous ( remote_apply ).
• Async data loss on failover: the primary dies before replicas catch up, and the last few async commits are gone. Fix: use synchronous replication for the data that must never be lost; accept the latency cost for that data only.
• Split-brain (two primaries): the old primary returns and keeps writing after a replica was promoted, so data diverges. Fix: fence the old primary before/at promotion and use a quorum-based manager (Patroni + etcd) so only one leader can exist.
• No monitoring of replication lag: you discover the lag only during an outage. Fix: alert on NOW() - pg_last_xact_replay_timestamp() and on pg_stat_replication long before you ever need a failover.
• Replication slot fills the primary's disk: a replica goes offline but its slot makes the primary keep every WAL file, eventually filling the disk. Fix: monitor pg_replication_slots and set a max_slot_wal_keep_size cap.

📘 Quick Reference

Command / setting

Purpose

Frequently Asked Questions

Q: Sync or async — which should I default to?

Default to asynchronous for its speed, then make specific high-value writes synchronous with a per-transaction SET LOCAL synchronous_commit = 'remote_apply' . Making everything synchronous is usually slower than you need.

No. Replicas are read-only — an attempted write errors with "cannot execute … in a read-only transaction". Writes go to the primary; that one-way flow is what keeps the copies consistent.

Q: How is replication different from a backup?

A backup is a point-in-time snapshot for recovering from data loss or corruption . Replication is a continuously-updated live copy for availability and read scaling . You need both — replication won't save you from a bad DELETE , because it faithfully copies the delete to every replica.

Q: Does adding read replicas speed up writes?

No — there's still a single primary doing all writes, and with synchronous replication more replicas can make writes slightly slower . Replicas scale reads ; scale writes with bigger hardware, partitioning/sharding, or (carefully) multi-primary.

Mini-Challenge: Design a Topology

Put it all together — a brief and a blank canvas. Design the replication topology for a read-heavy news site that must survive one server dying. The expected shape of a good answer is in the comments.

🎉 Lesson Complete

• ✅ One primary owns writes; replicas copy it and serve reads
• ✅ Async is fast but lags; sync trades latency for zero data loss
• ✅ Physical/streaming copies the whole cluster; logical copies chosen tables
• ✅ Route SELECT s to replicas to scale reads — but mind read-your-writes
• ✅ On failure, promote a caught-up replica and fence the old primary to avoid split-brain
• ✅ Next: Advanced Views — virtual tables that simplify complex queries

Practice quiz

Continue this course

• Previous: Backup & Restore
• Next: Advanced Views