Database Replication Failover
The Question
How does database replication failover work?
What a Strong Answer Covers
- Replica promotion
- data loss risk
- RPO/RTO.
Senior-Level Answer
**Replication** is the process of copying data from a primary (master) database to one or more replicas (secondaries). It serves two purposes: durability (data survives primary failure) and read scaling (queries can be distributed across replicas).
**Replication modes:**
- **Synchronous (semi-sync)**: the primary waits for at least one replica to acknowledge the write before confirming to the client. Zero data loss on failover, but higher write latency. PostgreSQL's synchronous_commit, MySQL semi-sync. - **Asynchronous**: the primary confirms immediately and replicates in the background. Lower write latency, but a brief failover window exists where the primary committed data that has not yet reached replicas — this is called **replication lag**. On failover, the promoted replica may be missing the last few transactions.
**Failover process:**
1. **Detection**: monitoring detects primary unavailability. Must distinguish crash from network partition — a partitioned primary is still running and accepting writes (split-brain risk). 2. **Promotion**: the most up-to-date replica is elected primary. In asynchronous setups this may involve data loss if lag existed. 3. **Fencing (STONITH)**: the old primary must be reliably killed or isolated before the new one accepts writes. Without fencing, two primaries can write simultaneously, creating diverging data. 4. **Redirect**: clients or the load balancer update their connection to point to the new primary. Connection poolers like PgBouncer or ProxySQL handle this transparently. 5. **Re-add old primary as replica**: after recovery, the old primary rejoins as a replica and catches up via WAL shipping (Postgres) or binlog (MySQL).
**Automated failover tools**: Patroni (Postgres + etcd/Consul/ZooKeeper for consensus), MySQL InnoDB Cluster (built-in group replication), AWS RDS Multi-AZ (managed, ~60s failover).
**RPO and RTO**: Recovery Point Objective is how much data you can afford to lose (0 for synchronous, seconds for async). Recovery Time Objective is how long the system can be down. These are business requirements that drive the replication mode choice.
The interview-critical point: failover is not automatic unless you build it. Naive failover without fencing causes split-brain. Always ask 'what kills the old primary before the new one starts writing?'
Key Differences
| Aspect | Synchronous Replication | Asynchronous Replication |
|---|---|---|
| Data loss on failover | Zero (RPO = 0) | Seconds of data (RPO > 0) |
| Write latency | Higher (waits for replica ACK) | Lower (fire and forget) |
| Replica lag | None | Seconds to minutes possible |
| Geographic distance | Same or nearby DC only | Can span regions |
| Throughput impact | High write traffic adds latency | Minimal write impact |
| Complexity | Simpler failover logic | Requires lag monitoring + fencing |
| Use case | Financial, healthcare data | Social feeds, analytics, logs |
What Separates a 2/3 from a 3/3
2/3 — Passing but Incomplete
Correctly explains sync vs async replication tradeoffs and describes the failover promotion sequence including fencing.
3/3 — Strong Answer
Defines RPO/RTO, explains split-brain and how fencing prevents it, names a specific tool (Patroni or RDS Multi-AZ), and connects replication mode to business requirements.
Common Mistakes
- Describing failover without mentioning fencing — split-brain is the most dangerous failure mode
- Saying 'automatic failover just works' without explaining the consensus mechanism needed
- Confusing replication for read scaling (replicas) with replication for HA (synchronous standby)
- Not knowing that async replication can lose data — a critical correctness issue for financial systems
Follow-Up Questions
- What is split-brain and how does Patroni prevent it? — Split-brain = two nodes both believe they are primary and accept writes. Patroni uses etcd/Consul for distributed lock — only the node holding the lock is primary.
- How does replication lag affect read consistency? — Reads from async replicas may return stale data — applications must either tolerate this or route consistency-sensitive reads to the primary.
- What is WAL shipping and how is it used in Postgres replication? — WAL (Write-Ahead Log) segments are streamed to replicas which replay them — streaming replication uses this mechanism continuously.
- How would you achieve zero RPO and sub-minute RTO for a Postgres database? — Synchronous replication to a standby in the same AZ, Patroni for automated promotion, and connection pooler for transparent failover.
Related Questions
Can You Explain This Cold?
Reading the answer is step one. Explaining it unprompted — under interview pressure — is what actually matters. Get AI-graded feedback on your answer with follow-up probes on your weak points.
Get Graded — Free Assessment