Failover Lost 5 Minutes of Writes. Promise Zero Data Loss?
Scenario
At around two in the morning the primary database host died. Automation did its job: it promoted the asynchronous replica and the application came back within minutes. The problem surfaced in the morning — the replica had been about five minutes behind at the moment of promotion. Roughly two thousand committed transactions vanished, among them over a hundred paid orders for which customers already hold confirmation emails. Support is untangling the mess, and the replication setup itself is years old and was never re-examined against current business volume.
In the post-incident review, leadership sets the requirement: "Guarantee this never happens again — zero data loss, and it must not slow anything down." The CTO's summary is blunt: if the database said committed, the data must survive anything. You are asked to sign up for that.
The Quick Fix on the Table
Promise zero data loss with zero performance impact. It ends the uncomfortable meeting, it sounds like accountability, and it defers the hard conversation — until the next failover, when physics collects the debt with interest.
The quick fix is on the table and the room is waiting for your call. Would you sign off on it? Take a position and justify it — out loud or on paper — before revealing the analysis.
Why the Quick Fix Fails
- It promises something the architecture cannot deliver. Asynchronous replication acknowledges a commit before the replica has the data — that is its definition. Any failover to an async replica can lose the in-flight window. Signing up for "zero loss" on top of async replication is signing up to be wrong.
- Zero loss and zero overhead are mutually exclusive. Guaranteed durability across nodes means the commit waits for at least one replica to confirm — synchronous replication. That wait is real latency on every single write, and it grows with the distance between nodes. You can pick the cost; you cannot pick "no cost."
- Synchronous replication trades loss risk for availability risk. With strict sync replication, a slow or dead replica can stall or block commits on the primary. "Never lose data" quietly becomes "sometimes refuse writes" unless you design for it deliberately.
- An unexamined promise prevents the real fix. If the official position is "this can never happen," nobody builds the reconciliation tooling, the lag monitoring, or the promotion gates that would make the actual failure mode survivable.
- The blast radius was avoidable even with async. Five minutes of lag at promotion time was not fate — it was an unmonitored, unbounded lag window plus automation willing to promote regardless. That part is fixable without touching the CAP trade-off at all.
The interviewer nods: “Fine, the quick fix is off the table. So what exactly would you do — step by step?” Sketch your plan before revealing the approach.
The Right Approach
- Reframe the requirement as RPO and RTO numbers. Bring leadership two dials, not a slogan: RPO (how much committed data we may lose) and RTO (how long we may be down). "Zero RPO" is a valid choice — but it must be chosen with its price tag visible.
- Present the replication options with honest costs. Async (today): near-zero write overhead, RPO = replication lag. Semi-synchronous / quorum commit (e.g. Postgres
synchronous_commit = remote_writewithsynchronous_standby_names = 'ANY 1 (...)'): RPO ≈ 0 for single-node failure, at the cost of added per-commit latency and careful multi-standby design so one sick replica cannot freeze the primary. - Measure the real latency cost before deciding. Stand up a staging pair, enable synchronous commit, and load-test: p50/p99 write latency and peak throughput before and after. Decisions change when "some slowdown" becomes a concrete number like "+4ms p50 in-region, +40ms cross-region."
- Consider tiering durability by transaction type. Postgres allows
SET LOCAL synchronous_commitper transaction — pay the sync price for payments and orders, keep async speed for telemetry and session updates. This often satisfies the business intent at a fraction of the blanket cost. - Bound and monitor lag regardless of the choice. Alert on replication lag (e.g.
pg_stat_replicationbyte lag) long before it reaches the agreed RPO, and make the promotion automation refuse or page a human when lag exceeds the threshold — a stale replica should never be promoted silently. - Build the reconciliation path for the residual risk. Keep WAL/archived logs from the old primary so lost transactions can be identified and replayed or compensated; have a runbook for re-sending confirmations and reconciling payments. The incident hurt mostly because nobody could say what was lost.
- Get the chosen RPO/RTO signed off and test it. Write the numbers into the service's operating agreement and rehearse failover quarterly with a game day. A tested five-second RPO beats an untested zero-second promise.
Final pushback: “Your plan costs more time and money than the quick fix. Convince me.” How do you defend your position under pressure?
How to Defend It
- "I can give you zero data loss — with synchronous replication and a measurable latency cost on every write. I can give you today's performance — with a small, bounded loss window. I can't give you both, and anyone who promises both is deferring this meeting to the next outage."
- "'Committed means committed' is exactly the standard sync replication implements. Let's load-test it and put a real number on the cost instead of assuming it's unaffordable — or free."
- "We don't have to buy one durability level for everything. Orders and payments can commit synchronously while low-value writes stay fast — that targets the money where the risk is."
- "The five-minute loss wasn't just async replication — it was unmonitored lag and automation that promoted a stale replica without a check. Those fixes are cheap and we should do them this week no matter what."
- "Whatever RPO we choose, it's only real once we've rehearsed a failover and measured it. I'd rather report a tested number than an aspirational zero."