Database failover, RPO and RTO: an interactive replication simulator
When a primary database dies, a replica is promoted to take its place. Two numbers describe how that goes. The RPO, recovery point objective, is how much recent data can be lost: writes the primary had acknowledged but not yet copied anywhere. The RTO, recovery time objective, is how long writes are unavailable while the failure is detected, a replica promoted and clients reconnected. Replication mode decides the first; detection and automation decide the second.
The playground writes 1,000 times a second to a primary with two replicas, one nearby and one in another region, then crashes the primary. Choose how replication acknowledges writes, let the replicas fall behind, and see what is lost and how long the outage lasts.
The primary acknowledges each write as soon as it has committed it locally. Its replicas were seconds behind, so when it crashed, 2,659 writes that clients had been told were saved existed nowhere else: the last 2.6 s of data is gone. Whatever the replication mode, the database is unavailable for about 25 s: 10 s to decide the primary is dead, 5 s to promote a replica, 10 s for clients to find it.
Asynchronous replication: fast, and you can lose writes
With asynchronous replication the primary says “saved” as soon as the write is on its own disk, and ships it to replicas afterwards. Writes take 1 ms, but whatever has not reached a replica when the primary dies is gone after failover. With replicas milliseconds behind that is a handful of writes; when a heavy batch job has put them seconds behind, the playground loses 2,659 acknowledged writes, the last 2.6 seconds of data. Replication lag is your RPO, so it is the number to monitor and alert on.
Semi-synchronous and synchronous: no loss, slower writes
Waiting for a replica before acknowledging means every acknowledged write exists in two places, and the promoted replica has them all. Semi-synchronous waits for one replica, ideally the nearby one: about 6 ms a write instead of 1. Synchronous to both waits for the slowest, here the replica in another region: about 56 ms. And if the replicas fall behind, every write waits for them: 2.6 seconds in the playground. That is why MySQL’s semi-sync times out and falls back to asynchronous, quietly giving up the guarantee, and why PostgreSQL lets you wait for any one of several standbys rather than a specific one.
| Asynchronous | Semi-synchronous | Synchronous to all | |
|---|---|---|---|
| Acknowledged when | On the primary’s disk | On one replica too | On every replica |
| Data lost on failover | Up to the replication lag | None, if that replica is promoted | None |
| Write latency | Local commit | Plus the nearest replica | Plus the slowest replica |
| A replica stalls | No effect on writes | Waits, then often falls back to async | Writes stop |
Where the downtime goes
RTO is mostly waiting. Detecting that the primary is really dead takes a few heartbeats, and a shorter timeout fails over on every hiccup; see heartbeats and failure detection. Promoting a replica means choosing the most up-to-date one and making it writable. Then every client has to find it: DNS records with a TTL, connection pools holding dead connections, and retries backing off. Managed databases with automatic failover typically take 30 seconds to a couple of minutes end to end; a proxy in front of the database, such as PgBouncer or ProxySQL, cuts the reconnect part.
Split brain and fencing
The old primary may not be dead, only cut off. If it keeps accepting writes after a replica is promoted, the two diverge and some writes will be lost when they are reconciled. Failover systems fence the old primary: revoke its lease, cut it off at the proxy, or shut it down, before promoting a new one. Consensus-based databases avoid the problem by only allowing a primary that holds a majority, as in Raft.
Replication settings in practice
# PostgreSQL: a commit waits for at least one of two standbys
synchronous_standby_names = 'ANY 1 (replica_a, replica_b)'
synchronous_commit = on # 'remote_apply' also waits until the standby has applied it
# MySQL semi-synchronous replication
rpl_semi_sync_source_enabled = 1
rpl_semi_sync_source_wait_for_replica_count = 1
rpl_semi_sync_source_timeout = 10000 # ms; after this it silently falls back to async
# watch how far behind replicas are, and alert on it
SELECT client_addr, replay_lag FROM pg_stat_replication;In a system design interview
State the RPO and RTO and how you get them: “Orders and payments use semi-synchronous replication to a standby in another zone of the same region, so an acknowledged order survives the primary’s loss at the cost of a few milliseconds per write. Failover is automatic, about 30 seconds. A cross-region replica is asynchronous for disaster recovery, with an RPO of seconds that we alert on.” Expect the follow-ups: what happens if the standby is down, how you prevent split brain, and what the application does during the 30 seconds.
Read the guide to replication and consistency or backups and disaster recovery, see read-your-writes consistency for what lag does to reads, or watch a strong candidate design a payment system.
Questions people ask
What are RPO and RTO?
The recovery point objective, RPO, is how much data you can afford to lose in a failure, measured as time: an RPO of 5 seconds means the last 5 seconds of writes may be lost. The recovery time objective, RTO, is how long the system can be unavailable before it is serving again. Replication mode drives RPO; detection and failover automation drive RTO.
Can asynchronous replication lose data?
Yes. The primary acknowledges a write once it is on its own disk and sends it to replicas afterwards, so if the primary fails, writes that had not yet reached a replica are lost when a replica is promoted. The amount lost equals the replication lag at the moment of failure, which is why lag should be monitored and alerted on.
What is semi-synchronous replication?
A mode where the primary waits until at least one replica has received a write before acknowledging it, so every acknowledged write exists on two machines. It adds one round trip to the nearest replica to each write. MySQL’s implementation falls back to asynchronous replication if no replica answers within a timeout, giving up the guarantee to keep accepting writes.
What is the trade-off of synchronous replication?
Durability against latency and availability. Waiting for replicas before acknowledging means no acknowledged write is lost when the primary fails, but every write takes as long as the slowest replica it waits for, and if a required replica stalls or is unreachable, writes stall too. Waiting for any one of several replicas reduces that risk.
How long does a database failover take?
Typically tens of seconds to a few minutes: time to detect that the primary has failed, which depends on heartbeat timeouts; time to choose and promote the most up-to-date replica; and time for clients to reconnect, which depends on DNS TTLs, connection pools and retry behaviour. Proxies and short DNS TTLs reduce the last part.
What is split brain in database failover?
A situation where the old primary is not actually dead, only unreachable, and keeps accepting writes after a replica has been promoted, so two primaries diverge. It is prevented by fencing the old primary, revoking its lease, blocking it at the proxy or shutting it down, before promotion, or by consensus protocols that only allow a primary with a majority.
How do you choose which replica to promote?
The one that has received the most of the primary’s log, so the least data is lost; with semi-synchronous replication, one that is guaranteed to have every acknowledged write. Tools such as Patroni for PostgreSQL and Orchestrator for MySQL compare replication positions automatically, and managed databases do this for you.