Replication Internals: WAL Shipping, LSNs, Lag and Failover

The practical lesson said "the primary streams its changes"; this is what is being streamed. Every engine with a write-ahead log already produces an ordered, durable, self-describing sequence of changes before it modifies any page (Write-Ahead Logging). Replication is that sequence with a network in the middle: a sender on the primary reads records as they are flushed and pushes them; a receiver on the replica writes them to its own log, fsyncs, and reports back how far it got; an applier replays them into the replica's pages exactly as crash recovery would (Crash Recovery). A replica is a database that is permanently in recovery, and that is not a metaphor — it is the same code path.

Because the stream is ordered, every position in it has a name. In PostgreSQL that is the LSN, a 64-bit byte offset into the WAL; in MySQL a binlog file and position, or the GTID of the last executed transaction. Everything that follows — lag, acknowledgement, failover safety, divergence — is a comparison of two such positions.

Physical (WAL shipping / streaming) sends the log records themselves — "on block 812 of relation 16412, replace bytes 4080–4136 with these". The replica applies them to identical pages and becomes a byte-for-byte copy: same indexes, same bloat, same free-space map, same catalog. That makes it cheap (no decoding, no SQL execution, apply is sequential page writes) and complete (DDL, sequences, everything), but rigid: same major version, same architecture, whole cluster or nothing, and the replica is read-only because any local write would corrupt the byte identity.

Logical decodes the same WAL into row changes — INSERT INTO orders (id, total) VALUES (…), UPDATE … WHERE id = … with old and new tuples — and sends those. The subscriber applies them as ordinary transactions, so it can be a newer version (zero-downtime upgrade), hold extra indexes or tables, receive only some tables, or be a data warehouse, a Kafka topic or a search index (change-data-capture). The price: decoding costs CPU on the primary, apply is single-threaded per subscription unless parallelised, DDL is not replicated, tables need a replica identity (a PK) for UPDATE and DELETE, and sequences and large objects are excluded. Logical decoding also needs the primary to keep WAL until the consumer confirms it — a replication slot — which is the origin of most "disk full" incidents involving replication.

The mode names *which position in the pipeline COMMIT waits for* before replying. Asynchronous: the primary fsyncs its own WAL and replies — 1–3 ms — and the record streams afterwards; a replica may be anywhere behind, and if the primary dies the un-streamed tail is gone. Synchronous: the primary sends the record, waits for each listed standby to report it has fsynced (remote_flush) or applied (remote_apply) it, then replies. Every commit now costs the slowest standby's round trip plus its fsync; within a datacenter ~1–2 ms extra, across regions 50–150 ms. And if a synchronous standby stops responding, every commit blocks: synchronous replication converts a replica failure into a primary outage unless a quorum form is used. Semi-synchronous (ANY 1 (a, b) in PostgreSQL; rpl_semi_sync in MySQL) waits for *any one* of several standbys — durable on two machines, fastest of the candidates, tolerant of one standby dying.

The choice is per transaction, not per cluster: SET LOCAL synchronous_commit = off for an audit-log insert that can be replayed, remote_apply for the payment whose confirmation page will be read from a replica. Remote apply deserves attention: it is the only mode under which a replica listed as synchronous can never serve a stale read of an acknowledged write, and it is the most expensive because it waits for replay, not just for the fsync.

A replica is at three positions at once: what it has received, what it has flushed to its own log, and what it has replayed into pages. Reads see only the third. Lag in bytes is primary_current_lsn − replay_lsn; lag in time is how long ago the primary wrote the record the replica is currently replaying (replay_lag in pg_stat_replication, computed from timestamps the primary embeds). Bytes tell you how much work remains; seconds tell you how stale a read is. Both are needed: a replica 2 MB behind on an idle primary may be seconds stale; one 2 MB behind on a busy primary may be 50 ms stale.

A stale read is a read served from a replay position below the LSN of the write the client was acknowledged for. The fixes in Replication and Read Scaling are all LSN comparisons: after a write, remember pg_current_wal_lsn(); before reading from a replica, check pg_last_wal_replay_lsn() >= that and wait or fall back to the primary. Lag has causes worth distinguishing: network (bytes per second), apply (the replica's single replay process cannot keep up — a large index build or a bulk update on the primary), and conflict (a long query on the replica holds pages that replay needs to change; PostgreSQL either waits max_standby_streaming_delay or cancels the query — the "canceling statement due to conflict with recovery" error, and the reason hot_standby_feedback exists, which in turn holds back VACUUM on the primary).

Promotion is a sequence, and every step has a failure mode. Detect that the primary is gone — with a timeout, which means "unreachable for 10 s", not "dead". Fence the old primary so it cannot keep accepting writes: power it off, revoke its storage, or rely on a lease it must renew through the majority. Choose the replica with the highest flushed LSN — promoting any other loses writes that replica had. Promote: the replica finishes replaying what it has, opens a new timeline (PostgreSQL writes a history file; MySQL starts issuing GTIDs under its own server UUID), and accepts writes. Re-point every other replica and the application (DNS, a proxy, a virtual IP, a connection string in a config store). Rejoin the old primary as a replica when it comes back — which requires discarding the WAL it wrote after the divergence point (pg_rewind finds it by comparing timeline histories; MySQL by GTID set difference) before it can follow the new leader.

What is lost: under async replication, every acknowledged write the old primary had not yet streamed to the promoted replica. Under sync or semi-sync to the promoted node, nothing. Split brain is the failure of step two: the old primary was partitioned rather than dead, kept accepting writes from clients that could still reach it, and now two histories exist that both contain acknowledged commits. There is no merge for byte-level WAL; one side is discarded. Every serious HA manager (Patroni, Stolon, Orchestrator, cloud managed services) runs leader election through a consensus store precisely so that a node can only be primary while it holds a majority-granted lease — the mechanism Distributed Fundamentals: Partial Failure, Quorums, Consensus, Recovery derives.

The primary recycles WAL segments once a checkpoint has made them unnecessary for its own recovery — but a replica that is behind, or disconnected, still needs them. Without protection, a replica that falls behind by more than wal_keep_size finds its next segment gone and must be rebuilt from a base backup. A replication slot (pg_create_physical_replication_slot, or the slot every logical subscription creates) records the oldest LSN its consumer has confirmed (restart_lsn) and forbids recycling anything after it. Slots make replicas robust to disconnection and are mandatory for logical decoding, which must also retain the catalog state needed to decode old records.

The cost is that a slot whose consumer has vanished — a decommissioned replica, a CDC connector that crashed — pins WAL forever. pg_wal grows until the disk is full and the primary stops. max_slot_wal_keep_size (13+) caps it by invalidating the slot instead; pg_replication_slots.wal_status (reserved, extended, unreserved, lost) and safe_wal_size are the columns to alert on. Slots also carry xmin when hot_standby_feedback is on, holding back VACUUM on the primary for as long as the replica's longest query runs — a replica can bloat the primary.

MySQL replicates from the binary log, a server-level log of transactions written after the storage engine has prepared them — InnoDB's redo log and the binlog are kept consistent by an internal two-phase commit, which is why sync_binlog = 1 and innodb_flush_log_at_trx_commit = 1 are set together for a crash-safe primary. In row format (the default) each event carries the before- and after-image of a row, making it closer to PostgreSQL's logical stream than to physical WAL shipping; MySQL replicas are therefore independently writable (dangerous), can differ in schema, and apply events by executing them. A replica's I/O thread pulls events into a relay log; applier threads execute them, in parallel where the primary's write-set tracking proves the transactions did not conflict.

A GTID (server_uuid:transaction_number) names each transaction globally, and every server maintains gtid_executed, the set it has applied. Replication becomes "send me everything not in my set" (MASTER_AUTO_POSITION), replicas can be re-pointed to a new primary without knowing file offsets, and divergence after a failover is visible as a set difference: if the old primary has A:1-105 and the promoted replica A:1-102, B:1-40, the old primary's A:103-105 are the lost or errant transactions. Semi-synchronous replication waits for at least one replica to acknowledge receipt (not apply) of the binlog event before committing; rpl_semi_sync_source_wait_point = AFTER_SYNC acknowledges only after the replica has it, which closes the window in which a crash could expose a commit no replica holds.