Replication & failover

Async vs sync vs semi-sync: what each one risks

Replication copies every write from the primary to one or more replicas. The only interesting question is when the primary is allowed to tell the client "done."

Asynchronous: the primary commits locally, acks the client, and ships the change to replicas whenever it can. Fast, and the replica never slows a write down. The cost is a window: a write that was acked but has not reached a replica yet is lost if the primary dies. That lost window is your RPO (recovery point objective), the amount of data a failover can drop.

Synchronous: the primary waits for a replica to acknowledge before it acks the client. Now a committed write survives losing the primary, because a replica already has it. The cost moved to latency, and to availability: if the replica you are waiting on is down, the write has no one to wait for and blocks.

Semi-synchronous (MySQL's term) sits between them: the primary waits for at least one replica to confirm it received the write, then acks, without waiting for that replica to apply it. Less latency than full sync, and normally no lost writes, but the guarantee is softer than it looks (the senior notes pull that apart).

Wrong "Synchronous replication is just async with better durability, so always use it." Sync buys zero-loss failover and charges you write latency plus an availability cliff when a required replica is unreachable. Most systems run async precisely because a replica hiccup must not freeze the primary.

Right pick per workload. Async where a few seconds of lost writes on a rare failover is acceptable. Sync (or semi-sync) for money or anything you cannot replay, and only after you have arranged enough replicas that losing one does not stall writes.

Replication lag and reading your own writes

Replicas apply the primary's changes slightly behind, from milliseconds to seconds or more under load. If you write to the primary and then read from a replica, you can read the old value. A user edits their profile, the next page loads from a replica, and their change is "gone."

This breaks read-your-writes consistency, and it is the most common replica bug in practice. db-scaling covers the routing side of read replicas. The fix here: right after a write, either read from the primary, or wait until the replica has caught up to that write before reading it there. Do not assume a replica is current just because the write returned.

What a failover actually involves

"The primary died, promote a replica" hides four steps, and each one has a way to go wrong:

• Detection. Something decides the primary is dead. The hard part: a network partition looks exactly like a dead primary from the outside. Deciding wrong is how you get two primaries.
• Fence the old primary. Force the old primary to stop accepting writes before anyone else takes over. Skip this and a primary that was only partitioned keeps taking writes.
• Promotion. A standby becomes the new primary.
• Reroute clients. Traffic has to find the new primary. A DNS change is slow to propagate and existing pooled connections keep pointing at the old address.

The nightmare is split-brain: two nodes both believe they are the primary and both accept writes. Their histories diverge, and when the network heals there is no clean merge, so someone's committed data gets thrown away. Preventing split-brain is why real failover uses a single external authority to decide who leads (a leader lock only one node can hold) and fencing to stop the loser. The senior notes cover how that authority and the fencing actually work.

One boundary to keep straight: replication and failover keep you available through the loss of a node. They are not backups. A dropped table or a bad migration replicates to every replica instantly, so recovering from that is disaster recovery (point-in-time restore from backups), a separate topic with its own module. High availability protects against a node dying, not against a mistake you copied everywhere.

The synchronous_commit levels, and the sole-standby trap

"Synchronous replication" is not one setting. In PostgreSQL 18, synchronous_commit picks how far a commit waits, and each level is a different point on the durability curve:

off          # no wait even for local flush; a crash can drop recent commits
local        # wait for local WAL flush only; does NOT wait for any standby
remote_write # standby received it and wrote to its OS, not yet fsync'd
on (default) # commit is on disk (WAL) on primary AND standby
remote_apply # standby has REPLAYED it; the change is visible to queries there

off is worth calling out because it sounds reckless but is not corruption: the docs are explicit that a crash may lose some recent allegedly-committed transactions, yet the database stays consistent, as if those transactions had aborted cleanly. local, remote_write, and remote_apply only differ from on when synchronous_standby_names is set; with it empty, only on and off mean anything.

synchronous_standby_names chooses who must ack. FIRST 2 (s1, s2, s3) is priority based: the first two named are synchronous, the rest are fallbacks. ANY 2 (s1, s2, s3) is quorum based: any two of the three suffice.

Wrong "Turn on synchronous replication and you are safe." A synchronous commit waits for the required standby, so if you configure FIRST 1 (r1) and r1 goes down, top-level write commits block until r1 returns. The doc's own guidance is that such commits "may never be completed" if the sole synchronous standby crashes. Right name more candidates than you require (so a lost standby is replaced), or use ANY, so that zero-loss does not become zero-availability the first time one replica reboots. Read-only transactions and rollbacks never wait, so only writes stall.

For read-your-writes on a replica specifically, remote_apply is the only level that guarantees the acknowledging standby has applied the change before the primary acks. The cheaper alternative is to record the write's log position (LSN in Postgres, GTID in MySQL) and have the reader wait until the replica reaches it.

Semi-sync: received is not applied, and it silently degrades

MySQL 8.4 semi-sync looks like a middle path, but two details bite. First, the source waits for the replica to acknowledge that the transaction's events are written and flushed to its relay log, not that they were applied to the storage engine. So a semi-sync replica can still serve a stale read, and after promotion it may need to finish applying its relay log. rpl_semi_sync_source_wait_point controls timing: AFTER_SYNC (default) waits for the ack after the binlog is synced but before the storage-engine commit; AFTER_COMMIT waits after the local commit, so clients on the source can see a transaction that no replica has confirmed yet.

Second, semi-sync is not a hard guarantee. If no replica acks within rpl_semi_sync_source_timeout, the source reverts to asynchronous replication and keeps going, returning to semi-sync when a replica catches up. So "we run semi-sync" means "we run sync until it is inconvenient, then async," which changes your RPO story during exactly the incidents you cared about. rpl_semi_sync_source_wait_for_replica_count (default 1) sets how many acks are required. One more consequence: MySQL's own docs say a crashed semi-sync source must be discarded after failover, not reused as the replication source, because it may hold transactions no replica ever acknowledged.

Physical vs logical replication

Physical replication ships the WAL: exact block addresses, byte for byte. The replica is a binary clone, which is what streaming standbys and crash failover want. It cannot replicate a subset of tables, cannot cross major versions, and the standby is read-only.

Logical replication decodes WAL into row changes keyed by replication identity (usually the primary key) and ships those over a publish/subscribe channel. That unlocks what physical cannot do: replicate a subset of tables, go between different major versions (so it is the tool for near-zero-downtime major upgrades), cross platforms, and write to the subscriber, which is an ordinary read/write database.

The restrictions are what trip people planning a failover onto a logical subscriber. Schema and DDL are not replicated (copy it by hand and keep it in sync). Sequences are not replicated: the data in a serial or identity column arrives, but the sequence object on the subscriber still reads its start value, so on switchover you must advance sequences or the new primary hands out colliding ids. Large objects are not replicated at all. Only tables (including partitioned tables) can be published. Treat logical replication as a data-movement and upgrade tool; physical replication remains the default for HA.

Fencing, split-brain, and STONITH

Split-brain is two primaries accepting writes at once, and it is a correctness catastrophe: once two histories diverge there is no merge, so healing the partition means discarding one branch of committed data. Every serious failover design spends its complexity budget on making sure exactly one node is ever the primary.

The mechanism is a single external authority plus fencing. The authority is a leader lock that only one node can hold. Fencing is what stops the node that lost it: STONITH ("shoot the other node in the head") is node-level fencing, classically by cutting power or forcing a reboot, so a stuck or partitioned old primary physically cannot keep serving. Cluster stacks like Pacemaker treat a cluster with no fencing configured as unsupported for exactly this reason.

Wrong "Synchronous replication prevents split-brain." It prevents lost writes, a different problem. Two synchronously replicated nodes can still both think they are primary if nothing fences the loser. Conversely, fencing prevents split-brain but does not recover the async writes a failed primary acked and never shipped. Data loss and split-brain need separate fixes, and a good design addresses both.

Orchestrators: the Patroni model, switchover vs failover

Patroni is the common answer for PostgreSQL HA: a Python agent beside each Postgres that stores cluster state in a distributed configuration store (DCS: etcd, Consul, or ZooKeeper, run with 3 or 5 nodes so a quorum exists). The DCS provides the single source of truth via a leader key with a TTL. The primary's agent renews that key every loop_wait (default 10s); if it cannot renew within ttl (default 30s), Postgres stops accepting commits and the node demotes itself, and a healthy standby races to grab the now-free key and promote. The safety constraint is loop_wait + 2 * retry_timeout <= ttl, which is why the defaults are 10, 10, 30.

Patroni's fencing is a watchdog. It arms a hardware or software watchdog (softdog) to reset the whole node a safety_margin (default 5s) before the TTL, so if the agent hangs or the box is too overloaded to demote in time, the machine reboots rather than lingering as a second primary. That is Patroni's STONITH: the leader key bounds the window in software, the watchdog enforces it in hardware.

The consensus that makes the leader key safe (a quorum agreeing on one holder) lives in the DCS. etcd runs Raft; consensus-raft covers the algorithm, including why a leader lease still leans on bounded clock assumptions. Patroni is a client of that guarantee, not a reimplementation of it.

Two operations that interviewers conflate. A switchover is planned: the cluster is healthy, you move the primary role on purpose (for a kernel patch or a resize), and it can be scheduled. A failover is unplanned: the leader is already gone and a standby is promoted to recover. Switchover is graceful and controllable; crash failover is the emergency path with a real chance of data loss, which is why you rehearse the graceful one constantly and design the emergency one to lose as little as possible.

Reintegrating the demoted old primary is its own step. After a failover its timeline has diverged from the new primary, so a bare restart makes it a second, wrong-history primary. pg_rewind rewinds it to the divergence point and lets it rejoin as a standby by copying only the blocks that changed, which is far cheaper than a fresh base backup.

Test failover before it tests you

The design above is worth little untested, because the failure you never rehearsed is the one that pages you at 3am. Two habits separate teams that survive a failover from teams that discover their script's bug during the incident. Run scheduled switchover drills and game days on a real (staging) cluster, and measure the numbers you actually promise: RTO (how long until writes resume) and RPO (how much data the cut lost). And test the partition case, not just a clean kill -9 of the primary: a process you killed is easy, a primary that is alive but unreachable is what produces split-brain, and only fencing you have actually exercised will stop it.