Replication: streaming, logical, and the lag that bites you

Everything so far in this track has been about one Postgres server doing its job well. But one server is two kinds of dangerous. It's a single point of failure — its disk, its kernel, its data-center all become your uptime — and it's a single ceiling — every read and every write funnels through one machine's CPU and I/O. Replication is the answer to both: keep one or more copies of the database on other machines, kept up to date with the original.

The instant you have more than one copy, though, you've signed up for the defining problem of distributed data: a write happens on one machine, and there is now a window — however brief — where that write exists on some copies and not others. Everything hard about replication is a consequence of that window. This lesson is Postgres-flavored, but the shape is universal, and it's the on-ramp to the consistency-at-scale stage later.

Why replicate: three different goals

It's worth separating the reasons, because they pull in different directions:

• High availability — if the primary dies, a replica can be promoted to take over. This is about surviving failure.
• Read scaling — reads can be served from replicas, spreading load off the primary. Writes still all go to the one primary (in the standard single-leader setup). This is about capacity.
• Locality, backups, analytics — a replica near your users cuts read latency; a replica is a live backup; a replica can absorb heavy analytical queries without disturbing the production primary.

The standard topology behind all three is single-leader (a.k.a. primary/replica): exactly one node accepts writes — the primary — and it propagates changes to one or more read-only replicas. Having a single writer is what keeps things sane: there's always one authoritative order of writes. (Multi-leader and leaderless designs exist and are a Stage 2 topic; they trade that simplicity for conflict resolution.)

How the changes actually travel: streaming vs logical

Postgres has two replication mechanisms, and the difference is what gets shipped.

Physical / streaming replication ships the WAL — the exact write-ahead log from the write-path lesson. Recall the WAL is a sequential record of every physical change to the database's pages. Streaming replication simply sends that byte stream to each replica as it's generated, and the replica replays it, reproducing the primary's pages block-for-block. The replica ends up a physically identical copy. This is the default, it's efficient, and it's what you use for HA and read replicas. The constraints follow from "it's replaying physical page changes": a replica is read-only (it can't diverge — it's just applying the primary's changes), it copies the whole cluster (all databases, all tables — you can't pick a subset), and both ends must run the same major Postgres version (page formats must match).

Logical replication ships row-level changes instead: it decodes the WAL into logical events — "row inserted into expenses with these values," "row x updated," "row y deleted" — and sends those to subscribers, which apply them as ordinary writes. This is more flexible precisely because it's semantic rather than physical: you can replicate a subset of tables, replicate across major versions (useful for near-zero-downtime upgrades), replicate into a different schema or even a non-Postgres system, and the subscriber is a normal writable database. That flexibility is why logical replication (and its underlying "logical decoding") is the foundation of change-data-capture pipelines — streaming your database's changes into a search index, a data warehouse, or a Kafka topic. The cost is more overhead per change and more moving parts than raw WAL streaming.

Rule of thumb: streaming for a faithful HA/read-replica copy; logical when you need to replicate selectively, across versions, or out to another system.

The core trade-off: synchronous vs asynchronous

Now the window. When the primary commits a write, does it wait for replicas to have it, or not?

Asynchronous replication (the default) — the primary commits, flushes its own WAL, returns success to the client immediately, and ships the WAL to replicas whenever it can. Fast: the client's write latency is just the primary's local commit, no network round trip to a replica. The catch is the window — for a brief moment the committed write is on the primary only. If the primary dies in that window before the replica received it, that write is gone, even though the client was told it succeeded. Async trades a small durability risk for low latency.

Synchronous replication — the primary does not return success until at least one replica has confirmed the write is safely in its WAL. Now no single-node failure can lose an acknowledged write: it's on at least two machines before the client hears "committed." The cost is exactly one network round trip added to every commit — write latency goes up — and a sharper danger: if the synchronous replica is down or unreachable, the primary has no one to wait for, so writes stall until a replica comes back. You've traded availability for durability. (Postgres lets you tune this: synchronous_commit and synchronous_standby_names control how many replicas must confirm and how durably, so you can pick a point on the spectrum rather than a hard binary.)

This is a direct preview of CAP/PACELC: sync replication leans consistent (an acknowledged write is everywhere) at the cost of availability when a replica is lost; async leans available (the primary never waits) at the cost of a durability window and stale replicas.

Replication lag and the bug it causes in your app

Async replicas are, by definition, always a little behind the primary — by milliseconds normally, by seconds or worse under load or a slow network. That gap is replication lag, and it produces the single most common application-level replication bug, one you can hit on day one of adding a read replica:

Trigger the bug yourself. With reads routed to the replica, write an expense and immediately read the list — your own write is missing until replication catches up. Flip routing to the primary and it's always there. Notice reducing lag never fixes it; only the routing does.

Related lag hazards worth naming: monotonic reads (a user refreshes and sees data disappear because two successive reads hit two replicas at different lag — fixed by pinning a session to one replica) and analytical replicas drifting far enough behind that dashboards quietly report stale numbers.

Failover: when the primary dies

Replication's HA payoff is failover: the primary fails, and a replica is promoted to become the new primary, so writes can resume. This can be manual or automated by a cluster manager (Patroni, repmgr) or handled entirely by a managed provider. Two dangers define the difficulty:

• Lost writes on async failover. If replication was asynchronous, the promoted replica is missing the primary's un-shipped tail — every write in that lag window is lost on promotion. Sync replication avoids this (at its latency cost); this is the durability half of the sync/async choice made concrete.
• Split-brain. The scariest failure. If the old primary isn't definitively stopped before a replica is promoted — say a network partition made it merely look dead — you can end up with two nodes both accepting writes, diverging into two conflicting histories that are agony to reconcile. Preventing split-brain needs fencing (forcibly ensuring the old primary can't accept writes — STONITH, "shoot the other node in the head") and usually a consensus/quorum mechanism so only one node can win the promotion. This is why robust automatic failover is hard, and why it's a classic reason to let a managed provider own it.

Go deeper

• PostgreSQL docs — streaming replication & standby servers — The authoritative mechanics of shipping and replaying WAL to a standby, plus synchronous_standby_names and the durability knobs — the physical basis of everything in the first half.
• PostgreSQL docs — Logical Replication — Publications/subscriptions, what row-level replication can do that physical cannot (subset, cross-version, CDC), and where its limits are.
• Jepsen — distributed systems safety analyses — Rigorous, adversarial tests of what real databases actually guarantee under partition and failover — the best cure for believing replication is simpler than it is.

Check yourself

Answer out loud, as if an interviewer asked. If you hand-wave, reread that section.

1. A single database server is dangerous in two distinct ways, and replication addresses both. Name them, and explain why read scaling and high availability are different goals even though both use replicas.
2. Streaming replication ships the WAL; logical replication ships row-level changes. Give two things logical replication can do that streaming cannot, and explain why streaming is nonetheless the default for a plain HA read replica.
3. Explain the synchronous-vs-asynchronous trade-off in terms of write latency and the durability window. What specifically happens to writes if a synchronous replica goes down, and how does that map onto consistency vs availability?
4. Walk through the read-your-writes bug end to end: user adds an expense, then the group screen shows it missing. Why does reducing replication lag not fix it, and what routing change does?
5. On failover with asynchronous replication, some writes can be lost even though clients were told they committed. Explain why, and what synchronous replication trades to prevent it.
6. Define split-brain, explain the network-partition scenario that causes it on failover, and name the mechanism used to prevent two nodes from both accepting writes.