Decision 2 of 10 · Replication, failover and split-brain

Synchronous replication and failover

This page decides one thing: what a commit waits for before it returns, and what that choice costs when a server dies.

60-second layer

The decision: how many copies must confirm a write before the application hears "saved".

The rule of thumb: if losing one second of acknowledged writes is a data-loss incident, use synchronous replication with a quorum, not a single standby. If a few seconds of lag is acceptable, async is cheaper and faster. The price of sync is one network round trip plus one standby fsync on every commit. The price of async is write rate multiplied by lag, paid all at once on failover.

ChoiceWhat you payWhen you pay it
SynchronousOne round trip plus one standby fsync, about 2 ms per commit cross AZOn every commit
AsynchronousWrite rate multiplied by lag, 10,000 transactions at 2,000 commits/s and 5 s of lagAll at once, at failover

On this page

Cross-engine comparison

The same decision, three engines

Read the row for the mode you run, not the engine you like.

ModeCOMMIT returns afterRPO for acknowledged writesRTO, typicalSplit-brain behaviorWhat the ACK means
PostgreSQL asyncLocal WAL fsyncWrite rate x lag. At 2,000 commits/s and 5 s of lag, 10,000 transactions.Promote the standby and replay its backlog. Seconds to minutes.Nothing built in. The old primary must be fenced or two primaries accept writes.No ACK. Nobody confirms.
PostgreSQL sync, on, ANY 1 (s1, s2)Local flush plus one standby flush0 for one failure at a time. The standby has the WAL on disk before the commit returns. Either standby may hold the latest commit, so promote the one with the highest flush LSN. If the primary and that standby fail together, the acknowledged writes on it are lost.Promote the most advanced standby by LSN. Tens of seconds with Patroni on defaults, longer by hand. See section 4 for the step by step.Quorum survives one standby loss. The old primary still needs fencing.The standby flushed the WAL to its disk.
PostgreSQL sync, remote_applyLocal flush plus one standby replay0, and reads on that standby see the commit.Same as sync, plus replay time on the standby.Same as sync.The standby replayed the commit. It is visible there.
MySQL semi-sync, AFTER_SYNCBinlog sync plus one replica receipt0 if the replica that ACKed is the one promoted. Otherwise the un-ACKed window.Promote the most advanced replica. Seconds with Orchestrator.GTID plus fencing, or the two sides diverge.The replica wrote the events to its relay log. Not applied.
MySQL Group Replication, single-primaryA majority certifies the transaction0 for certified transactions.Election plus backlog apply. Seconds.A minority partition stays in the group and its writes block. No split-brain. By default it waits forever: group_replication_unreachable_majority_timeout defaults to 0. MySQL 8.4 manualA majority of the group agreed.
MongoDB w: 1The primary applies it in memoryUnreplicated writes. They can roll back.Election, median about 12 s with defaults, plus catch-up.The old primary rolls back on rejoin.The primary applied it. Nothing else.
MongoDB w: "majority", j: trueA majority journals it0 for acknowledged writes.Same election, about 12 s median, plus catch-up.A minority cannot elect a primary.A majority has it durable.
Decision record

Context: a payments ledger. A lost acknowledged payment is a data-loss incident, not a delay. Two availability zones, one region. Commit rate peaks at 800 commits/s from one service, spread across multiple sessions. One serial session caps at 500 commits/s cross AZ, so the 800 figure only works across sessions.

Options considered: async with a 2 s lag budget; PostgreSQL sync with one named standby; PostgreSQL sync with quorum ANY 1 (s1, s2); MySQL semi-sync; MongoDB w: "majority".

Choice: PostgreSQL sync, synchronous_commit = on, synchronous_standby_names = 'ANY 1 (s1, s2)', one standby per AZ. Commit p99 rises by about 2 ms. That is the price, measured before rollout, not discovered after.

Consequences: writes hang if both standbys are down. That is a known outage mode, and it needs an alert on sync_state and a written runbook step for removing a dead standby from the list. On failover, promote the standby with the highest flush LSN. With ANY 1, either standby may hold the latest commit, and promoting the other one loses acknowledged writes. The ledger never loses an acknowledged payment to a single server failure. That was the requirement. The latency is the cost.

Quorum, in one picture

AppCOMMIT
→
Primaryflushes WAL locally
→
ANY 1 (s1, s2)any one standby flushes
→
AppCOMMIT returns
10-minute layer · section 1 of 8

1. What

PostgreSQL

Synchronous replication means the primary's commit waits until a standby confirms the WAL. Two settings control it. synchronous_standby_names names the standbys that count. synchronous_commit sets how far the standby must get before the commit returns.

The levels, weakest to strongest:

Sync levels, weakest to strongest

offreturns before local fsync
→
locallocal fsync only
→
remote_writestandby wrote to OS buffers
→
onstandby flushed to disk, RPO 0
→
remote_applystandby replayed it, visible there

Boundary: synchronous replication guarantees durability on the standby, not visibility, except at remote_apply. It does not guarantee the standby is caught up for reads. It does not protect against a bug that corrupts the primary and replicates the corruption. It is not a backup. RPO 0 holds for one failure at a time. If the primary and the standby holding the latest commit fail together, the acknowledged writes that only that standby held are lost with it.

synchronous_standby_names has three forms. A bare name, s1, waits for that one standby. FIRST 1 (s1, s2) waits for the first standby in priority order. ANY 1 (s1, s2) waits for any one of the two. Only the ANY form survives one standby going down. A bare list, s1, s2, means FIRST 1 (s1, s2), not both.

FormWaits forSurvives one standby loss
s1That one standbyNo
FIRST 1 (s1, s2)The first standby in priority orderNo
ANY 1 (s1, s2)Any one of the twoYes

MySQL

Semi-synchronous replication makes the source wait for one replica to acknowledge receipt before the commit returns. It is a plugin, rpl_semi_sync_source on the source and rpl_semi_sync_replica on the replica. Both sides must have it installed, or replication stays async.

The wait point matters. rpl_semi_sync_source_wait_point = AFTER_SYNC is the default. The source writes the transaction to its binlog, syncs the binlog, sends it, and waits for the ACK. Only then does it commit to InnoDB and return. No other session can see a transaction that a replica has not received. AFTER_COMMIT commits to InnoDB first and waits after. Other sessions can then read data that a failover may still lose. Keep the default.

Wait pointOrderWhat other sessions can see
AFTER_SYNC (default)Binlog sync, send, wait for ACK, then commit to InnoDBNothing unreplicated
AFTER_COMMITCommit to InnoDB first, then waitData a failover can still lose

The ACK means the replica wrote the events to its relay log. It does not mean the replica applied them. Whether that relay log write is fsynced before the ACK depends on sync_relay_log, which defaults to 10000, meaning the relay log is fsynced every 10,000 events. With the default, a replica OS crash can lose a relay-logged transaction that was already ACKed. Set sync_relay_log = 1 if the ACK must mean durable.

Group Replication is a different mechanism. A group of servers agrees on every transaction before any of them commits it. In single-primary mode, one server accepts writes and the rest are secondaries. Each transaction is certified against the group's write sets, then a majority must agree. The commit returns after that agreement. No single replica ACK is the guarantee. The group is.

Group Replication, in one picture

Primaryproposes the transaction
→
Groupcertifies against write sets
→
Majorityagrees
→
Primarycommits and returns

MongoDB

Write concern is set per operation, not per server. w: 1 waits for the primary alone. w: "majority" waits for a majority of data-bearing voting members to acknowledge, and a member acknowledges only after receiving and applying the write. j: true requires the journal fsync as well. Since MongoDB 5.0 the default write concern is w: "majority", except in a replica set with an arbiter where the data-bearing members only just reach a voting majority, for example two data members plus an arbiter, where the default drops to w: 1.

w: "majority" implies j: true when writeConcernMajorityJournalDefault is true, which is the default. So in a normal three-member replica set, the default write waits for two members to have the write durable.

Write concern, weakest to strongest

w: 1primary alone
→
w: majoritytwo of three durable
→
j: truejournal fsync as well
Read concern is the other half: local can return data that is later rolled back. majority returns only data at or before the majority commit point.

Read concern is the other half. readConcern: "majority" returns only data at or before the majority commit point, the newest oplog entry a majority has. The default read concern is local, which can return data that is later rolled back.

Boundary: w: "majority" protects acknowledged writes. It does not make reads fresh. A secondary can still serve stale data unless the read uses readConcern: "majority" or reads from the primary.

10-minute layer · section 2 of 8

2. Why

Async replication has a loss window, and the window is exactly write rate multiplied by lag. At 2,000 commits/s and 5 s of lag, a failover loses 10,000 acknowledged transactions. The lag is not a constant. It is milliseconds at idle and seconds at peak, because lag is set by standby apply throughput, not by the network. The loss window is largest at the moment the primary is busiest, which is the moment it is most likely to die.

The loss window, as arithmetic

Write rate2,000 commits/s
×
Lag5 s
=
Lost at failover10,000 transactions

How the window shrank

Log shippingwindow: minutes
→
Streamingwindow: milliseconds
→
Synchronouswindow: zero

Before synchronous replication, the options were a smaller window or a faster failover. Neither closes the window. A faster failover loses fewer transactions. It still loses some. Synchronous replication is the only mechanism in this list that makes the window zero, and the price is latency on every commit.

The price is paid in a specific place: the commit path. Nothing else slows down. Reads are unaffected. A single long transaction pays the wait once, at commit. A chatty application that commits once per row pays it once per row.

What came before it: file-based log shipping, where WAL files moved after commit and the window was minutes. Streaming replication shrank the window to milliseconds. Synchronous replication closed it. Each step cost latency and complexity, and each step was worth it only for data where the loss window was unacceptable.

10-minute layer · section 3 of 8

3. How

PostgreSQL

The commit path, step by step:

  1. The backend writes the commit record to the WAL buffers and asks for a flush.
  2. The WAL writer fsyncs the local WAL. In parallel, the walsender streams the record to the standby.
  3. The standby walreceiver writes the record to its pg_wal and fsyncs it.
  4. The walreceiver sends back its flush LSN.
  5. The backend wakes and returns COMMIT to the client.

The two flushes run in parallel. The added latency is the standby side only: one network round trip plus the standby fsync. The backend waits with wait event SyncRep, visible in pg_stat_activity.

Sequence diagram, cross-AZ standby. Timings use the assumptions in section 4: RTT 1.5 ms, standby fsync 0.5 ms.

App                 Primary              Standby s1 (other AZ)
 | --- COMMIT --------> |                     |
 |                      | -- WAL record ------> |  t = 0
 |                      |    fsync local (0.5 ms, in parallel)
 |                      |                      |  write + fsync (0.5 ms)
 |                      | <-- ACK flush LSN --- |  t = 2.0 ms
 | <-- COMMIT ok ------- |                     |

Captured output. sync_state is the column that tells the truth.

SELECT client_addr, state, sync_state, replay_lag FROM pg_stat_replication; 10.0.1.52 | streaming | sync | 00:00:00.003 10.0.1.53 | streaming | async | 00:00:41.208 SHOW synchronous_standby_names; -- empty means nobody is synchronous, whatever the runbook says

Captured output: pg_waldump on one insert, PostgreSQL 18, captured Nov 17, 2026. One heap INSERT, two index inserts, one COMMIT. These are the records the standby receives:

rmgr: Heap        len (rec/tot):    100/   100, tx:        761, lsn: 0/01C74768, prev 0/01C74730, desc: INSERT off: 4, flags: 0x00, blkref #0: rel 1663/16384/16430 blk 0
rmgr: Btree       len (rec/tot):     64/    64, tx:        761, lsn: 0/01C747D0, prev 0/01C74768, desc: INSERT_LEAF off: 4, blkref #0: rel 1663/16384/16445 blk 1
rmgr: Btree       len (rec/tot):     64/    64, tx:        761, lsn: 0/01C74810, prev 0/01C747D0, desc: INSERT_LEAF off: 2, blkref #0: rel 1663/16384/16469 blk 1
rmgr: Transaction len (rec/tot):     34/    34, tx:        761, lsn: 0/01C74850, prev 0/01C74810, desc: COMMIT 2026-11-17 07:35:08.109358 UTC

MySQL

The semi-sync commit path, AFTER_SYNC:

  1. The source writes the transaction to its binlog and syncs the binlog.
  2. The source sends the events to the replica and waits.
  3. The replica I/O thread writes the events to its relay log and sends an ACK.
  4. The source commits to InnoDB and returns to the client.

Semi-sync, AFTER_SYNC, in one picture

Sourcewrites binlog, syncs it
→
Replicawrites relay log
→
Replicasends ACK
→
Sourcecommits, returns

Captured output. The status variable is the one that matters. The enabled variable can say ON while the protection is OFF.

SHOW STATUS LIKE 'Rpl_semi_sync_source_status'; -- ON means the source is waiting for replica ACKs right now -- OFF means it timed out and fell back to async SELECT @@gtid_executed; -- run on source and replica; the difference is the failover gap

Captured output: mysqlbinlog on one transaction, MySQL 8.4.13, captured Nov 17, 2026. Session setup lines and row bytes omitted here; the full transaction is in the appendix:

# at 2547
#261117  7:35:08 server id 1  end_log_pos 2626 CRC32 0xc91bbfba 	GTID	last_committed=7	sequence_number=8	rbr_only=yes	original_committed_timestamp=1794900908468887	immediate_commit_timestamp=1794900908468887	transaction_length=344
# at 2626
#261117  7:35:08 server id 1  end_log_pos 2711 CRC32 0x427573ee 	Query	thread_id=10	exec_time=0	error_code=0
SET TIMESTAMP=1794900908/*!*/;
BEGIN
/*!*/;
# at 2711
#261117  7:35:08 server id 1  end_log_pos 2781 CRC32 0xed9543c9 	Table_map: `shop`.`users` mapped to number 88
# has_generated_invisible_primary_key=0
# at 2781
#261117  7:35:08 server id 1  end_log_pos 2860 CRC32 0xd68aef49 	Write_rows: table id 88 flags: STMT_END_F
# at 2860
#261117  7:35:08 server id 1  end_log_pos 2891 CRC32 0x8010bc83 	Xid = 21
COMMIT/*!*/;

Group Replication replaces this path. The transaction is broadcast to the group, certified against the write sets of transactions already in the group, and committed only after a majority agrees. In single-primary mode the primary is the only writer, so certification rarely conflicts. The commit returns after the majority agreement, which costs roughly two network round trips inside the group, not one.

MongoDB

The write path for w: "majority", j: true:

  1. The primary applies the write and records it in the oplog, in the same storage transaction as the change.
  2. Secondaries tail the oplog and apply the entry.
  3. Each secondary journals the entry.
  4. Once a majority has it, the primary advances the majority commit point and returns the ACK.

w: majority, j: true, in one picture

Primaryapplies write, records oplog
→
Secondariesapply entry, journal it
→
Majoritycommit point advances
→
Primaryreturns the ACK

Captured output: one oplog entry, MongoDB 8.0, captured Nov 17, 2026:

[
  {
    lsid: {
      id: UUID('608e5f81-73d0-40d9-ae02-9b7e70d2fe5c'),
      uid: Binary.createFromBase64('47DEQpj8HBSa+/TImW+5JCeuQeRkm5NMpJWZG3hSuFU=', 0)
    },
    txnNumber: Long('1'),
    op: 'i',
    ns: 'shop.users',
    ui: UUID('4aa7ca1d-0ed7-453e-b1c9-3f6d1e8659fa'),
    o: {
      _id: 999,
      name: 'Lab',
      email: 'lab@example.com',
      status: 'active',
      amount: 1
    },
    o2: {
      _id: 999
    },
    stmtId: 0,
    ts: Timestamp({ t: 1794900932, i: 1 }),
    t: Long('1'),
    v: Long('2'),
    wall: ISODate('2026-11-17T07:35:32.008Z'),
    prevOpTime: {
      ts: Timestamp({ t: 0, i: 0 }),
      t: Long('-1')
    }
  }
]

ts is the oplog timestamp. t is the term. op is the operation, i for insert. ns is the namespace. o is the document.

Captured output. rs.status() shows the lag each member carries. Values below are an example with the real field names.

rs.status().members.map(m => ({ name: m.name, stateStr: m.stateStr, lagSeconds: (new Date() - m.optimeDate) / 1000 })) // [ // { name: "mongo1:27017", stateStr: "PRIMARY", lagSeconds: 0 }, // { name: "mongo2:27017", stateStr: "SECONDARY", lagSeconds: 0 }, // { name: "mongo3:27017", stateStr: "SECONDARY", lagSeconds: 2 } // ]
10-minute layer · section 4 of 8

4. When

Assumptions, stated before the arithmetic. Standby fsync 0.5 ms, a small WAL record on SSD. Same-AZ round trip 0.3 ms. Cross-AZ round trip 1.5 ms. Cross-region round trip 40 ms. These are the inputs. The outputs follow from them.

Added latency per commit is one round trip plus the standby fsync:

Added latency per commit, to scale

Same AZ
0.8 ms
Cross AZ
2.0 ms
Cross region
40.5 ms

Cross region is 50 times same AZ. That is the whole decision, in one bar.

A single session committing serially is capped at about 1 / (RTT + fsync):

Serial commit cap per session, to scale

Same AZ
about 1,250 commits/s
Cross AZ
500 commits/s
Cross region
about 24 commits/s

One session, one commit at a time. More sessions raise the total, never the per-session cap.

Group commit helps concurrent sessions, not that one. The WAL writer fsyncs once per batch, so ten sessions committing at once share one fsync and one round trip each. Aggregate throughput rises with concurrency. A single session stays capped.

Use synchronous replication if:

Avoid it if:

RTO and RPO, stated together:

RTO for PostgreSQL with Patroni, step by step, on defaults. Patroni documents ttl 30 s, loop_wait 10 s, and retry_timeout 10 s. Patroni docs

RTO, step by step, Patroni on defaults

Detect20 to 30 s, leader key expires
→
Decidea few seconds, one loop
→
Promoteseconds, plus backlog replay
→
Redirectseconds, proxy or DNS
→
App reconnectdepends on pool and retries

Total on defaults is tens of seconds, dominated by detection. One more documented default can stretch it: primary_start_timeout is 300 s by default, and the Patroni docs give the worst case as loop_wait plus primary_start_timeout plus loop_wait. The 20 to 30 s figure above is the leader key expiry alone, not that worst case.

10-minute layer · section 5 of 8

5. What breaks

PostgreSQL

Failure 1: the synchronous standby goes down. Blast radius: all writes hang. Every commit waits for an ACK that never comes. Reads continue. Detection: pg_stat_activity fills with SyncRep wait events, and pg_stat_replication shows no row with sync_state = 'sync'. First metric to move: count of sessions waiting on SyncRep. Mitigation: quorum ANY 1 (s1, s2) so one standby loss does not stop writes, or remove the dead standby from synchronous_standby_names and reload.

Failure 2: the standby is up but slow. A saturated disk makes the standby fsync take 50 ms instead of 0.5 ms. Blast radius: commit p99 rises by the same 50 ms on every session. Detection: flush_lag and replay_lag in pg_stat_replication grow. First metric to move: commit p99 latency. Mitigation: the standby needs the same disk performance as the primary. A slow standby is a slow primary.

Failure 3: synchronous_standby_names is empty. The commit returns after local flush only. Blast radius: the deployment believes it has RPO 0 and has RPO equal to the async window. Detection: SHOW synchronous_standby_names returns empty, and no row in pg_stat_replication has sync_state = 'sync'. First metric to move: none. That is the danger. Mitigation: check the setting and the view in the deploy checklist, not the config file.

Failure 4: the old primary comes back after failover and accepts writes. Blast radius: two primaries, divergent data, a manual merge. Detection: two servers both report pg_is_in_recovery() = false. Mitigation: fence the old primary. Patroni does this through the distributed consensus store. By hand, it means stopping the old primary and confirming it is stopped before promoting the new one.

Failure 5: failover promotes the wrong standby. Blast radius: acknowledged writes the other standby held are lost, even though the deployment runs sync with RPO 0. With ANY 1 (s1, s2), either standby may be the one that ACKed the latest commit. Detection: compare flush_lsn across standbys before promoting. The standby with the highest flush LSN is the most advanced. First metric to move: the LSN gap between standbys. Mitigation: promote the most advanced standby by LSN. Patroni picks from the synchronous set. By hand, it means comparing LSNs before promoting, not picking the standby that answers first.

Failure 6: the old primary comes back and needs to rejoin as a standby. Blast radius: it cannot just follow the new primary, because its timeline forked at promotion. Any writes it took after the fork sit on a timeline the new primary does not have. Detection: the old primary reports a timeline the new primary has moved past. Mitigation: pg_rewind. Stop the old primary, run pg_rewind against the new primary, and it copies back only the blocks that changed since the timelines diverged, then the old primary restarts as a standby. That is far faster than a fresh base backup on a large database. It needs wal_log_hints on or data checksums set at initdb, and full_page_writes on, and WAL back to the divergence point, or the -c flag to fetch it from the archive. If the rewind fails halfway, the data directory may not be recoverable, so rebuild it from a fresh backup. Patroni runs pg_rewind for a rejoining old primary when use_pg_rewind is set. PostgreSQL 18 pg_rewind docs

MySQL

Failure 1: semi-sync times out. Blast radius: the source reverts to async for the whole server. Every dashboard still says semi-sync is enabled, because the enabled variable is still ON. The protection is gone and nothing pages. Detection: Rpl_semi_sync_source_status reads OFF. First metric to move: that status variable. Mitigation: alert on the status variable, not the enabled variable.

Failure 2: failover promotes a replica that is not the one that ACKed. Blast radius: the transactions the ACKing replica held are missing on the new source. Detection: compare gtid_executed on the old source and the new source. The difference is the gap. First metric to move: the GTID set difference. Mitigation: promote the most advanced replica. Orchestrator does this by GTID. By hand, it means comparing sets before promoting.

Failure 3: a minority partition in Group Replication. Blast radius: the minority members stay in the group and their writes block. They do not leave on their own, and they do not enter the ERROR state on their own. Transactions the minority accepts sit blocked because the group cannot reach quorum, until an operator stops Group Replication on those members or the unreachable majority timeout fires. Detection: members report UNREACHABLE in performance_schema.replication_group_members. First metric to move: group size. Mitigation: odd member counts, three or five, so a majority is always clear. Set group_replication_unreachable_majority_timeout to a value the team can live with. It defaults to 0, which means wait forever. When it fires, the pending transactions on the minority roll back and the members move to ERROR, then auto-rejoin tries to bring them back. MySQL 8.4 manual

Failure 4: AFTER_COMMIT is set instead of AFTER_SYNC. Blast radius: other sessions can read data that a failover loses. Detection: SHOW VARIABLES LIKE 'rpl_semi_sync_source_wait_point'. Mitigation: keep the default. Verify it at deploy time.

Failure 5: errant GTIDs block a replica from rejoining. An errant GTID is a transaction in a replica's gtid_executed set that the source never ran. It gets there when someone writes directly to the replica, when a replica is briefly promoted during a failed failover and takes writes, or when maintenance runs on the replica without disabling the binlog. Blast radius: the replica cannot rejoin cleanly, because the source has no binlog for those GTIDs and cannot send them, and the other replicas cannot take them from the promoted replica either. In Group Replication the same shape shows up as a member that cannot rejoin because it has more executed transactions than the group. Detection: GTID_SUBTRACT of the replica set minus the source set returns a non-empty set. That is the errant set. First metric to move: the GTID set difference. Mitigation: if the errant writes are few and harmless, commit an empty transaction on the source for each errant GTID so the sets line up again. If the writes mattered or the set is large, rebuild the replica from a fresh copy of the source. RESET MASTER wipes the GTID history and the binlogs, so use it only on a server with no replicas, never as a shortcut on a live one.

MongoDB

Failure 1: the primary loses its majority. Blast radius: it steps down. Writes stop until a new primary is elected. Reads from the primary stop too. Detection: the logs show an election, and rs.status() shows no PRIMARY. First metric to move: election count in the logs. Mitigation: three voting members, in different failure domains, so one loss leaves a majority.

Failure 2: a w: 1 write is rolled back. Blast radius: the write disappears from the new primary and lands in rollback/ files on the old primary as BSON. Inserts and updates are saved there. Deletes and drops are not. Detection: the old primary enters ROLLBACK state on rejoin, and files appear in the rollback directory. First metric to move: rollback files on disk. Mitigation: w: "majority" for anything that cannot be lost.

Failure 3: a secondary falls behind the oplog window. Blast radius: it cannot catch up by tailing the oplog and needs a full initial sync. During the sync it is not available as a failover target. Detection: rs.printReplicationInfo() shows the oplog window in hours, and the secondary lag exceeds it. First metric to move: the oplog window. Mitigation: size the oplog for the longest planned downtime, and watch the window, not just the lag.

EngineFailureFirst metric to moveMitigation
PostgreSQLSync standby downSessions waiting on SyncRepQuorum ANY 1 (s1, s2), or remove the dead standby
PostgreSQLStandby up but slowCommit p99 latencyStandby disk as fast as the primary
PostgreSQLsynchronous_standby_names emptyNone, that is the dangerCheck the setting and the view at deploy time
PostgreSQLOld primary returns and writesTwo servers not in recoveryFence the old primary
PostgreSQLWrong standby promotedLSN gap between standbysPromote the highest flush LSN
PostgreSQLOld primary rejoinsTimeline moved pastpg_rewind
MySQLSemi-sync times outRpl_semi_sync_source_status OFFAlert on the status, not the enabled variable
MySQLWrong replica promotedGTID set differencePromote the most advanced replica
MySQLMinority partition in Group ReplicationGroup sizeOdd member counts, set the unreachable timeout
MySQLAFTER_COMMIT setWait point variableKeep AFTER_SYNC, verify at deploy
MySQLErrant GTIDsGTID set differenceEmpty transaction per errant GTID, or rebuild
MongoDBPrimary loses its majorityElection count in the logsThree voting members, different failure domains
MongoDBw: 1 write rolled backRollback files on diskw: "majority" for anything that cannot be lost
MongoDBSecondary behind the oplog windowOplog windowSize the oplog for the longest planned downtime
10-minute layer · section 6 of 8

6. Alternatives rejected

Async replication. Rejected where RPO is 0. It is the right choice where a few seconds of lag is acceptable, and it is the cheapest choice in latency. The break point is the loss budget: if the business accepts losing up to 5 s of writes, async with a 2 s lag alert is the simpler system.

MySQL semi-sync. Rejected for MySQL where the timeout fallback is unacceptable. Semi-sync bounds loss only while it is active. After a 10 s timeout it is async, silently. Pick it when one ACK is enough and the team alerts on the status variable. Pick Group Replication when the fallback itself is the risk.

MySQL Group Replication. Rejected where the team does not want the operational weight of a group: certification, quorum management, and the consistency level to tune. Pick it when failover must be automatic and split-brain must be impossible by construction, not by runbook.

MongoDB w: "majority". Rejected for high-volume telemetry and click events, where losing two seconds of events in a rare failover is a fair trade for throughput. Pick w: 1 there, and say so in writing. Pick majority for payments, ledgers, and anything auditable.

Two-phase commit across databases. Rejected. It makes every commit wait for every participant and turns one slow participant into a global outage. Use the outbox pattern instead: write the event to a local table in the same transaction as the business row, then relay it. The local commit is the atomic unit. The relay is async and retryable.

Read-your-writes routing instead of sync. Rejected as a durability mechanism. Routing a read to the primary after a write fixes stale reads. It does nothing for a primary that dies with unreplicated writes. It is a read-consistency fix, not a durability fix. Use both when both problems exist.

AlternativeWhy rejectedPick it when
Async replicationRPO is not 0A few seconds of lag is acceptable, with a 2 s lag alert
MySQL semi-syncFalls back to async silently after a timeoutOne ACK is enough and the team alerts on the status variable
MySQL Group ReplicationOperational weight: certification, quorum, consistency levelFailover must be automatic and split-brain impossible by construction
MongoDB w: "majority"Costs throughputPayments, ledgers, anything auditable; w: 1 for telemetry
Two-phase commit across databasesEvery commit waits for every participantUse the outbox pattern instead
Read-your-writes routingFixes stale reads, not durabilityUse both when both problems exist
10-minute layer · section 7 of 8

7. Operating it

Three metrics, per engine. These are the only three to alert on first. Everything else is diagnosis.

PostgreSQL

  1. sync_state in pg_stat_replication. Alert when no row has sync_state = 'sync' for more than 30 s. That means the safety feature is off.
  2. Commit p99 latency, before and after enabling sync. Alert when p99 rises more than 2x the pre-sync baseline. That is the price moving.
  3. replay_lag at peak write load. Alert over 2 s. That is the failover gap for the async replicas.

MySQL

  1. Rpl_semi_sync_source_status. Alert the moment it reads OFF. That is the protection silently gone.
  2. Seconds_Behind_Source on the replica that would be promoted. Alert over 2 s.
  3. GTID set difference between source and replica. Alert on any non-zero difference at failover time. That is the exact loss.

MongoDB

  1. Replica set member states in rs.status(). Alert when there is no PRIMARY for more than 30 s, or when a member is not in SECONDARY or PRIMARY state.
  2. Oplog window from rs.printReplicationInfo(). Alert when the window drops under 24 hours.
  3. Rollback files in the rollback directory. Alert on any file appearing. That is a lost write, found after the fact.

Alert thresholds, in one place: sync missing for 30 s, commit p99 2x baseline, replay lag 2 s, semi-sync status OFF immediately, oplog window under 24 hours, rollback files any.

EngineMetricAlert when
PostgreSQLsync_state in pg_stat_replicationNo sync row for more than 30 s
PostgreSQLCommit p99 latencyMore than 2x the pre-sync baseline
PostgreSQLreplay_lag at peak loadOver 2 s
MySQLRpl_semi_sync_source_statusReads OFF, immediately
MySQLSeconds_Behind_Source on the promotion candidateOver 2 s
MySQLGTID set difference, source vs replicaAny non-zero difference at failover time
MongoDBMember states in rs.status()No PRIMARY for more than 30 s
MongoDBOplog window from rs.printReplicationInfo()Under 24 hours
MongoDBRollback files in the rollback directoryAny file appears

Runbook, PostgreSQL sync standby down

  1. Confirm: SELECT application_name, state, sync_state FROM pg_stat_replication; No row shows sync.
  2. Check the standby: is the process up, is the disk full, is the network up. pg_stat_activity shows how many sessions wait on SyncRep. That is the blast radius, counted.
  3. If the standby is recoverable in minutes, wait. Writes hang, reads work. Say so in the incident channel.
  4. If it is not recoverable, remove it from synchronous_standby_names and reload. With ANY 1 (s1, s2), the other standby takes over and writes resume. With one named standby, removing it drops the deployment to async. Say that in the incident channel too.
  5. Verify: sync_state = 'sync' returns, and commit p99 returns to baseline.
  6. Record the change and the time to revisit it. A standby removed from the list and never added back is a deployment running async while the runbook says sync.

Runbook, MySQL semi-sync OFF

  1. Confirm: SHOW STATUS LIKE 'Rpl_semi_sync_source_status'; OFF with the enabled variable ON means a timeout.
  2. Find why the replica stopped ACKing: replica down, network partition, or replica I/O thread stuck.
  3. Fix the replica. Semi-sync returns on its own once a replica catches up.
  4. Check what was lost while it was async: compare gtid_executed on source and replica.
  5. If the timeout recurs, the timeout is wrong for the network, not the network for the timeout. Raise it, or move to Group Replication.

Runbook, MongoDB election storm

  1. Confirm: rs.status() shows elections in the logs and no stable PRIMARY.
  2. Check heartbeats: a member that cannot reach the primary for electionTimeoutMillis, 10 s by default, calls an election. Flapping network causes repeated elections.
  3. Fix the slow member or the network, not the election settings first. Lowering the timeout makes elections faster and more frequent. It does not fix the cause.
  4. Check for rollback files on the old primary after it rejoins. Any file there is a write the new primary never saw.

Topology: sync inside the region, async to a remote region

The common shape is a sync quorum in one region, two standbys in different AZs, plus an async replica in a remote region for disaster recovery. The remote replica hangs off a sync standby by cascading replication, or off the primary as a plain async standby. In PostgreSQL, cascading replication is async: the primary's sync settings do not wait for a cascaded standby, so the remote replica adds no commit latency. The tradeoff is the RPO split. A failover inside the region is RPO 0. A failover to the remote region loses whatever the async lag was at the time, and the remote region then has to rebuild the in-region quorum before sync protection is back. Treat the remote replica as disaster recovery, not high availability: it saves the data when the whole region is gone, and it is not the failover target for a single dead server.

Topology: sync inside the region, async to a remote region

Primaryone region
→
s1 + s2sync, ANY 1, different AZs
→
Remote replicaasync, cascaded, disaster recovery only

Capacity formula. The sync standby must keep up with the primary's WAL rate, or lag grows without bound. Required standby apply throughput is greater than the primary's peak WAL generation rate, with headroom. In numbers: if the primary generates 50 MB/s of WAL at peak, the standby must apply more than 50 MB/s, sustained, on its own disk. The formula for the serial commit cap is in section 4: 1 / (RTT + fsync) per session. The formula for aggregate throughput is sessions multiplied by that cap, bounded by the WAL writer's batching and the standby's apply rate. Measure both before rollout: commit p99 with sync off, then with sync on, at the same load. The difference is the price. The standby's disk throughput is the ceiling.

Capacity: the standby must keep up

Primarygenerates 50 MB/s of WAL at peak
→
Standbymust apply more than 50 MB/s, sustained
10-minute layer · section 8 of 8

8. Version notes

PostgreSQL 10 added quorum commit, the ANY and FIRST forms of synchronous_standby_names. Before that, the setting was a plain list and only the first standby counted. PostgreSQL 17 moved checkpoint counters from pg_stat_bgwriter to pg_stat_checkpointer, which changes where the checkpoint numbers in the operating section come from, not the replication mechanism. PostgreSQL 18 adds nothing to synchronous replication itself.

MySQL 5.7 made AFTER_SYNC the default wait point. Before that, AFTER_COMMIT was the default, and other sessions could read data a failover would lose. MySQL 8.0.14 added the group_replication_consistency levels, including BEFORE_ON_PRIMARY_FAILOVER, which holds reads and writes on a new primary until it applies its backlog. The default is still EVENTUAL. MySQL 8.0.30 changed the redo log files to #innodb_redo/#ib_redoN. That changes the file names in the crash story, not the replication protocol.

MongoDB 5.0 made w: "majority" the default write concern. Before that, the default was w: 1, and an application that set nothing got primary-only acknowledgment. MongoDB 4.4 removed the old plan cache commands, which is unrelated to replication and noted here only because it is the same class of mistake: a command that worked in an old version and silently does nothing in the current one.

PostgreSQL versions

PG 10quorum commit, ANY and FIRST
→
PG 17checkpoint counters move to pg_stat_checkpointer
→
PG 18nothing new for sync

MySQL versions

5.7AFTER_SYNC becomes the default
→
8.0.14consistency levels, BEFORE_ON_PRIMARY_FAILOVER
→
8.0.30redo files become #innodb_redo

MongoDB versions

4.4old plan cache commands removed
→
5.0w: majority becomes the default
2 AM story · as a postmortem

Postmortem: the binlog that filled the disk

The timeline is in phases because the site does not record clock times for it.

Detection: writes started failing. Then the database refused writes entirely. The disk was full.

Diagnosis: the disk was broken down by owner before anything was touched. The binlog was the filler. The purge had not kept up, and the reason was not a dead replica. MySQL purges binlogs by time: binlog_expire_logs_seconds sets the age, 2592000 seconds (30 days) by default, auto-purge is on by default, and expired files go at startup and when the binlog is flushed. A replica that is disconnected holds nothing back. Only a connected replica that has not yet read a file keeps that one file. The dead replica did not pin the binlog history. That is PostgreSQL replication-slot behavior, not MySQL.

Root cause: binlog retention times write rate outgrew the disk, with no bound sized to either. The site does not record the retention value, the write rate, or how long the fill took, so it does not record which of the three usual causes it was: retention set too long for the disk, retention set to 0 or auto-purge off, or a write burst that filled the disk faster than purge could keep up. Check SHOW BINARY LOGS, SHOW VARIABLES LIKE 'binlog_expire_logs_seconds', and the disk usage by owner before naming the cause.

Contributing factors: no alert on disk growth rate, so the fill was discovered at 100 percent, not at 70. Retention was never sized against write rate and disk size, so nobody knew how many days of binlogs the disk could hold. No runbook step for checking replica positions before purging, so a manual purge would have risked error 1236 on any replica that still needed a purged file.

What changed: check every replica position with SHOW REPLICA STATUS, then free space with PURGE BINARY LOGS. Set binlog_expire_logs_seconds to a value the write rate and the disk can actually hold. Alert at 70 percent disk and on disk growth rate, not at 98 percent. Never remove binlog files by hand. A replica that still needs a purged file fails with error 1236 and must be rebuilt from a backup.

How the disk filled

Retentionbinlog_expire_logs_seconds
×
Write ratebinlog bytes per day
→
Diskfills to 100%, writes fail

The fix, in order

1check every replica position
→
2PURGE BINARY LOGS
→
3set retention the disk can hold
→
4alert at 70% disk and on growth rate

Numbers to check: the site records no disk size, no retention value, and no durations for this incident, so none are stated here. Fill them in from the incident record: total disk size and how full the binlog directory was, the binlog_expire_logs_seconds value at the time, the write rate in binlog bytes per day, the age of the oldest binlog file, and how long the fill took from the last healthy disk reading to 100 percent.

Why this postmortem is on this page: a dead replica is not what blocked the purge, and that distinction is the point. The dead replica still matters on this page for a different reason: it is the state that silently degrades semi-sync to async, and a single synchronous standby that dies turns into hung writes. One dead replica, two different failure modes in the replication modes, plus a disk fill with a separate cause. Watch replica health for the replication modes, and size retention against write rate for the disk. The two fixes are different, so keep them in separate runbook steps.

Anti-patterns · and the way out

Anti-patterns and the migration path out

  1. One named sync standby, no quorum. synchronous_standby_names = 's1'. One standby failure stops all writes. Migration: ALTER SYSTEM SET synchronous_standby_names = 'ANY 1 (s1, s2)'; SELECT pg_reload_conf(); Then verify sync_state = 'sync' appears for one of the two.
  2. synchronous_commit = on with no synchronous_standby_names. The commit returns after local flush only, and nothing says so. Migration: set the names, reload, and check pg_stat_replication for a sync row. Add the check to the deploy checklist.
  3. Semi-sync enabled, never checked. The plugin is installed, the variable was set once, and the status flipped OFF weeks ago. Migration: alert on Rpl_semi_sync_source_status, not on the enabled variable. Check it in the weekly review.
  4. A sync standby in the same availability zone, called disaster recovery. One zone event kills the primary and its only copy. Migration: move the synchronous standby to a second AZ. Keep an async replica in a third region for real disaster recovery.
  5. w: 1 for money. The application set nothing and the driver default did the choosing. Migration: set w: "majority" per operation for payments and ledgers, or set the replica set default with setDefaultRWConcern. Then check the rollback directory for a week. Any file there is a write the old setting lost.
  6. AFTER_COMMIT instead of AFTER_SYNC. Other sessions read data a failover loses. Migration: SET GLOBAL rpl_semi_sync_source_wait_point = 'AFTER_SYNC'; It is the default. Verify it, do not assume it.
  7. Failover by hand with no fencing step. The old primary comes back and accepts writes. Migration: Patroni for PostgreSQL, Orchestrator for MySQL, or a written fence step that stops the old primary and confirms it is stopped before any promotion. The built-in election in MongoDB and Group Replication already fences by quorum. Use it.
Anti-patternFix
One named sync standby, no quorumANY 1 (s1, s2), then verify a sync row appears
synchronous_commit = on with no synchronous_standby_namesSet the names, reload, check pg_stat_replication, add the check to the deploy checklist
Semi-sync enabled, never checkedAlert on Rpl_semi_sync_source_status, check it weekly
Sync standby in the same AZ, called disaster recoveryMove the sync standby to a second AZ, keep an async replica in a third region
w: 1 for moneyw: "majority" per operation, or the replica set default, then check the rollback directory for a week
AFTER_COMMIT instead of AFTER_SYNCSet AFTER_SYNC, verify it, do not assume it
Failover by hand with no fencing stepPatroni, Orchestrator, or a written fence step that stops the old primary first
Uncertainty, stated plainly

Where I am less sure

The fsync and round-trip numbers in section 4 are assumptions, not measurements. Fsync latency on a small WAL record varies from 0.1 ms to 2 ms depending on the disk, and shared cloud disks are worse under contention. Cross-region round trips depend on the region pair. The mechanisms in this page do not depend on those numbers. The serial caps do. Measure them on the actual hardware before quoting them.

Aurora is not covered here. Aurora ships redo to a storage quorum instead of WAL files, so there are no WAL files to fill the disk and the classic checkpoint model does not apply. The sync standby model in this page is the on-prem and RDS model. Aurora deserves its own page.

Patroni failover time is stated as tens of seconds on defaults, dominated by the 30 s leader key expiry. The exact time depends on the distributed consensus store, the failure detection settings, and how much backlog the standby must replay. I have not measured it on this site's hardware.

AssumptionStatus
Fsync 0.1 to 2 ms, round trips by region pairAssumed, not measured. Measure on the actual hardware before quoting the caps.
Aurora behaves like on-prem syncNot covered. Aurora ships redo to a storage quorum, so this page does not apply. It needs its own page.
Patroni failover, tens of seconds on defaultsStated from the documented defaults, not measured on this site's hardware.
Proof · what was measured, and how

Tested on

Checked against docs: PostgreSQL 18, MySQL 8.4 LTS, MongoDB 8.0, Nov 2026.

Measured on: nothing measured yet. Captured on: Nov 17, 2026, lab run, PostgreSQL 18, MySQL 8.4.13, MongoDB 8.0.

Hardware and dataset: not yet fixed. The benchmark method below names what to run. No measured benchmark numbers are published on this page yet. The numbers in section 4 are derived from the stated assumptions, not measured. That distinction is the point of this box.

Method, PostgreSQL: pgbench with one client doing serial single-row commits, synchronous_commit off, then on, then remote_apply. Record commit p99 and transactions per second at each level. Then repeat with 16 clients to show group commit. Compare p99 before and after. The scripts will live in the repo under benchmarks/.

Method, MySQL: the same shape with sysbench, semi-sync off, then on with AFTER_SYNC, then Group Replication in single-primary mode. Record commit p99 and the Rpl_semi_sync_source_status value during a replica kill.

Method, MongoDB: an insert loop with w: 1, then w: "majority", then w: "majority", j: true. Record p99 and throughput. Then kill the primary mid-loop and count rolled-back writes under each setting.

EngineToolWhat it measures
PostgreSQLpgbench, 1 client then 16Commit p99 and throughput, sync off, on, remote_apply
MySQLsysbenchCommit p99, semi-sync off, on with AFTER_SYNC, Group Replication, plus the status variable during a replica kill
MongoDBInsert loop, then a primary killp99 and throughput at w: 1, w: majority, w: majority plus j: true, then rolled-back writes under each setting
Appendix: derivations, raw output, benchmark method, source links

Derivations

Added latency per commit, synchronous PostgreSQL:

added latency = RTT + standby fsync
same AZ:       0.3 ms + 0.5 ms = 0.8 ms
cross AZ:      1.5 ms + 0.5 ms = 2.0 ms
cross region: 40.0 ms + 0.5 ms = 40.5 ms

Serial commit cap per session:

cap = 1 / (RTT + standby fsync)
same AZ:       1 / 0.8 ms  = about 1,250 commits/s
cross AZ:      1 / 2.0 ms  = 500 commits/s
cross region:  1 / 40.5 ms = about 24 commits/s

The local fsync overlaps with the standby round trip, so it is not added. If the local disk is slower than the standby round trip, the local fsync becomes the bound instead.

Async loss window:

lost transactions = write rate x replay lag
2,000 commits/s x 5 s = 10,000 transactions

Raw output

Captured output: pg_waldump on the primary, one insert, PostgreSQL 18, captured Nov 17, 2026:

rmgr: Heap        len (rec/tot):    100/   100, tx:        761, lsn: 0/01C74768, prev 0/01C74730, desc: INSERT off: 4, flags: 0x00, blkref #0: rel 1663/16384/16430 blk 0
rmgr: Btree       len (rec/tot):     64/    64, tx:        761, lsn: 0/01C747D0, prev 0/01C74768, desc: INSERT_LEAF off: 4, blkref #0: rel 1663/16384/16445 blk 1
rmgr: Btree       len (rec/tot):     64/    64, tx:        761, lsn: 0/01C74810, prev 0/01C747D0, desc: INSERT_LEAF off: 2, blkref #0: rel 1663/16384/16469 blk 1
rmgr: Transaction len (rec/tot):     34/    34, tx:        761, lsn: 0/01C74850, prev 0/01C74810, desc: COMMIT 2026-11-17 07:35:08.109358 UTC

Captured output: mysqlbinlog on the source, one transaction, MySQL 8.4.13, captured Nov 17, 2026:

# at 2547
#261117  7:35:08 server id 1  end_log_pos 2626 CRC32 0xc91bbfba 	GTID	last_committed=7	sequence_number=8	rbr_only=yes	original_committed_timestamp=1794900908468887	immediate_commit_timestamp=1794900908468887	transaction_length=344
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1794900908468887 (2026-11-17 07:35:08.468887 UTC)
# immediate_commit_timestamp=1794900908468887 (2026-11-17 07:35:08.468887 UTC)
/*!80001 SET @@session.original_commit_timestamp=1794900908468887*//*!*/;
/*!80014 SET @@session.original_server_version=80413*//*!*/;
/*!80014 SET @@session.immediate_server_version=80413*//*!*/;
SET @@SESSION.GTID_NEXT= '5a3f27e0-e259-11f1-bf9a-f6cfb7b23e56:14'/*!*/;
# at 2626
#261117  7:35:08 server id 1  end_log_pos 2711 CRC32 0x427573ee 	Query	thread_id=10	exec_time=0	error_code=0
SET TIMESTAMP=1794900908/*!*/;
BEGIN
/*!*/;
# at 2711
#261117  7:35:08 server id 1  end_log_pos 2781 CRC32 0xed9543c9 	Table_map: `shop`.`users` mapped to number 88
# has_generated_invisible_primary_key=0
# at 2781
#261117  7:35:08 server id 1  end_log_pos 2860 CRC32 0xd68aef49 	Write_rows: table id 88 flags: STMT_END_F

BINLOG '
rAP8ahMBAAAARgAAAN0KAAAAAFgAAAAAAAMABHNob3AABXVzZXJzAAYDDw8PEvYJkAEgA1AAAAoC
AAEBAAID/P8AyUOV7Q==
rAP8ah4BAAAATwAAACwLAAAAAFgAAAAAAAEAAgAG/wAEAAAAAwBMYWIPAGxhYkBleGFtcGxlLmNv
bQZhY3RpdmWZu2J4yIAAAAEASe+K1g==
'/*!*/;
# at 2860
#261117  7:35:08 server id 1  end_log_pos 2891 CRC32 0x8010bc83 	Xid = 21
COMMIT/*!*/;

Captured output: one MongoDB oplog entry, MongoDB 8.0, captured Nov 17, 2026:

[
  {
    lsid: {
      id: UUID('608e5f81-73d0-40d9-ae02-9b7e70d2fe5c'),
      uid: Binary.createFromBase64('47DEQpj8HBSa+/TImW+5JCeuQeRkm5NMpJWZG3hSuFU=', 0)
    },
    txnNumber: Long('1'),
    op: 'i',
    ns: 'shop.users',
    ui: UUID('4aa7ca1d-0ed7-453e-b1c9-3f6d1e8659fa'),
    o: {
      _id: 999,
      name: 'Lab',
      email: 'lab@example.com',
      status: 'active',
      amount: 1
    },
    o2: {
      _id: 999
    },
    stmtId: 0,
    ts: Timestamp({ t: 1794900932, i: 1 }),
    t: Long('1'),
    v: Long('2'),
    wall: ISODate('2026-11-17T07:35:32.008Z'),
    prevOpTime: {
      ts: Timestamp({ t: 0, i: 0 }),
      t: Long('-1')
    }
  }
]

Benchmark scripts

The scripts will live in the repo under benchmarks/: pg-sync-bench.sh (pgbench, sync off, on, remote_apply, 1 and 16 clients), mysql-semisync-bench.sh (sysbench, async, semi-sync, Group Replication), mongo-wc-bench.js (insert loop, w:1, w:majority, w:majority plus j:true, then a primary kill and a rollback count). Each script prints commit p99 and throughput, and the page will carry the measured numbers once they exist.

Sources