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.

Learn Python, JavaScript, Java and more with free interactive lessons, real projects and AI-powered help. Beginner-friendly.

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.

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?).

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.

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.

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.

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.

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).

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.

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.

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.

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.

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.

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.

Practice quiz

• Browse all free courses
• Cheat sheets & quizzes
• Coding dictionary