This page decides one thing: what a commit waits for before it returns, and what that choice costs when a server dies.
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.
| Choice | What you pay | When you pay it |
|---|---|---|
| Synchronous | One round trip plus one standby fsync, about 2 ms per commit cross AZ | On every commit |
| Asynchronous | Write rate multiplied by lag, 10,000 transactions at 2,000 commits/s and 5 s of lag | All at once, at failover |
Read the row for the mode you run, not the engine you like.
| Mode | COMMIT returns after | RPO for acknowledged writes | RTO, typical | Split-brain behavior | What the ACK means |
|---|---|---|---|---|---|
| PostgreSQL async | Local WAL fsync | Write 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 flush | 0 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_apply | Local flush plus one standby replay | 0, 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_SYNC | Binlog sync plus one replica receipt | 0 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-primary | A majority certifies the transaction | 0 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 manual | A majority of the group agreed. |
| MongoDB w: 1 | The primary applies it in memory | Unreplicated 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: true | A majority journals it | 0 for acknowledged writes. | Same election, about 12 s median, plus catch-up. | A minority cannot elect a primary. | A majority has it durable. |
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
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
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.
| Form | Waits for | Survives one standby loss |
|---|---|---|
| s1 | That one standby | No |
| FIRST 1 (s1, s2) | The first standby in priority order | No |
| ANY 1 (s1, s2) | Any one of the two | Yes |
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 point | Order | What other sessions can see |
|---|---|---|
| AFTER_SYNC (default) | Binlog sync, send, wait for ACK, then commit to InnoDB | Nothing unreplicated |
| AFTER_COMMIT | Commit to InnoDB first, then wait | Data 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
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
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.
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
How the window shrank
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.
The commit path, step by step:
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.
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
The semi-sync commit path, AFTER_SYNC:
Semi-sync, AFTER_SYNC, in one picture
Captured output. The status variable is the one that matters. The enabled variable can say ON while the protection is OFF.
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.
The write path for w: "majority", j: true:
w: majority, j: true, in one picture
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.
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
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
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
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.
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
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.
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.
| Engine | Failure | First metric to move | Mitigation |
|---|---|---|---|
| PostgreSQL | Sync standby down | Sessions waiting on SyncRep | Quorum ANY 1 (s1, s2), or remove the dead standby |
| PostgreSQL | Standby up but slow | Commit p99 latency | Standby disk as fast as the primary |
| PostgreSQL | synchronous_standby_names empty | None, that is the danger | Check the setting and the view at deploy time |
| PostgreSQL | Old primary returns and writes | Two servers not in recovery | Fence the old primary |
| PostgreSQL | Wrong standby promoted | LSN gap between standbys | Promote the highest flush LSN |
| PostgreSQL | Old primary rejoins | Timeline moved past | pg_rewind |
| MySQL | Semi-sync times out | Rpl_semi_sync_source_status OFF | Alert on the status, not the enabled variable |
| MySQL | Wrong replica promoted | GTID set difference | Promote the most advanced replica |
| MySQL | Minority partition in Group Replication | Group size | Odd member counts, set the unreachable timeout |
| MySQL | AFTER_COMMIT set | Wait point variable | Keep AFTER_SYNC, verify at deploy |
| MySQL | Errant GTIDs | GTID set difference | Empty transaction per errant GTID, or rebuild |
| MongoDB | Primary loses its majority | Election count in the logs | Three voting members, different failure domains |
| MongoDB | w: 1 write rolled back | Rollback files on disk | w: "majority" for anything that cannot be lost |
| MongoDB | Secondary behind the oplog window | Oplog window | Size the oplog for the longest planned downtime |
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.
| Alternative | Why rejected | Pick it when |
|---|---|---|
| Async replication | RPO is not 0 | A few seconds of lag is acceptable, with a 2 s lag alert |
| MySQL semi-sync | Falls back to async silently after a timeout | One ACK is enough and the team alerts on the status variable |
| MySQL Group Replication | Operational weight: certification, quorum, consistency level | Failover must be automatic and split-brain impossible by construction |
| MongoDB w: "majority" | Costs throughput | Payments, ledgers, anything auditable; w: 1 for telemetry |
| Two-phase commit across databases | Every commit waits for every participant | Use the outbox pattern instead |
| Read-your-writes routing | Fixes stale reads, not durability | Use both when both problems exist |
Three metrics, per engine. These are the only three to alert on first. Everything else is diagnosis.
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.
| Engine | Metric | Alert when |
|---|---|---|
| PostgreSQL | sync_state in pg_stat_replication | No sync row for more than 30 s |
| PostgreSQL | Commit p99 latency | More than 2x the pre-sync baseline |
| PostgreSQL | replay_lag at peak load | Over 2 s |
| MySQL | Rpl_semi_sync_source_status | Reads OFF, immediately |
| MySQL | Seconds_Behind_Source on the promotion candidate | Over 2 s |
| MySQL | GTID set difference, source vs replica | Any non-zero difference at failover time |
| MongoDB | Member states in rs.status() | No PRIMARY for more than 30 s |
| MongoDB | Oplog window from rs.printReplicationInfo() | Under 24 hours |
| MongoDB | Rollback files in the rollback directory | Any file appears |
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
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
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
MySQL versions
MongoDB versions
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
The fix, in order
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-pattern | Fix |
|---|---|
| One named sync standby, no quorum | ANY 1 (s1, s2), then verify a sync row appears |
| synchronous_commit = on with no synchronous_standby_names | Set the names, reload, check pg_stat_replication, add the check to the deploy checklist |
| Semi-sync enabled, never checked | Alert on Rpl_semi_sync_source_status, check it weekly |
| Sync standby in the same AZ, called disaster recovery | Move the sync standby to a second AZ, keep an async replica in a third region |
| w: 1 for money | w: "majority" per operation, or the replica set default, then check the rollback directory for a week |
| AFTER_COMMIT instead of AFTER_SYNC | Set AFTER_SYNC, verify it, do not assume it |
| Failover by hand with no fencing step | Patroni, Orchestrator, or a written fence step that stops the old primary first |
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.
| Assumption | Status |
|---|---|
| Fsync 0.1 to 2 ms, round trips by region pair | Assumed, not measured. Measure on the actual hardware before quoting the caps. |
| Aurora behaves like on-prem sync | Not 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 defaults | Stated from the documented defaults, not measured on this site's hardware. |
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.
| Engine | Tool | What it measures |
|---|---|---|
| PostgreSQL | pgbench, 1 client then 16 | Commit p99 and throughput, sync off, on, remote_apply |
| MySQL | sysbench | Commit p99, semi-sync off, on with AFTER_SYNC, Group Replication, plus the status variable during a replica kill |
| MongoDB | Insert loop, then a primary kill | p99 and throughput at w: 1, w: majority, w: majority plus j: true, then rolled-back writes under each setting |
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
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')
}
}
]
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.
Previous: DecisionsNext: Decision 3, Backup, PITR, and restore time (planned)