How database failover works without losing data

The Problem Statement

Interviewer: "Your primary database just crashed. Your application is getting connection errors. Walk me through what happens next. How does the system detect the failure, promote a replica, and get the application reconnected, and how much data could you lose?"

This question tests four things: your understanding of failure detection mechanisms, your knowledge of replica promotion and leader election, your grasp of the data loss tradeoffs between sync and async replication, and whether you can reason about the full failover timeline from crash to recovery.

Most candidates say "the replica becomes the new primary." Strong candidates walk through the detection delay, the election protocol, the connection rerouting, and the RPO/RTO implications of different replication modes.

Clarifying the Scenario

You: "Before I walk through this, I want to scope the setup."

You: "Are we talking about a managed database like RDS Multi-AZ, or a self-managed cluster like PostgreSQL with streaming replication? The mechanics differ significantly."

Interviewer: "Start with the general concepts, then go deep on a specific example."

You: "Got it. And when you say 'how much data could you lose,' that tells me you want me to address RPO (Recovery Point Objective). Should I also cover RTO (Recovery Time Objective), meaning how long until we are fully back online?"

Interviewer: "Yes, both. And explain what controls each one."

You: "I will structure my answer in four parts. First, failure detection: how the system knows the primary is down. Second, replica promotion: how a new leader gets elected. Third, connection rerouting: how clients find the new primary. Fourth, data loss analysis: what determines whether you lose zero transactions or several seconds worth."

My Approach

I break database failover into five layers:

1. Failure detection: Heartbeat timeouts, phi accrual failure detectors, and the tradeoff between fast detection and false positives
2. Replica promotion: Synchronous vs asynchronous replication impact on data loss, and how the "most caught up" replica gets selected
3. Leader election: Consensus-based election with Raft or Paxos, and why you need a quorum
4. Connection rerouting: DNS failover, VIP (virtual IP) switching, proxy-based routing, and application-level retry
5. Split-brain prevention: Fencing, STONITH, and why two nodes both thinking they are primary is the worst possible outcome

The mental model I use: think of database failover like succession in an organization. When the CEO (primary) becomes incapacitated, someone needs to (1) notice they are gone, (2) decide who takes over, (3) notify all employees of the new leader, and (4) make sure the old CEO does not wake up and start giving contradictory orders. Each step has a time cost, and the total downtime is the sum of all four.

The Architecture

Here is what a healthy database cluster looks like:

1. The primary handles all writes and optionally reads. It streams its write-ahead log (WAL) to replicas.
2. Replica 1 uses synchronous replication: every write to the primary is confirmed only after Replica 1 acknowledges it. This means zero data loss if the primary crashes, but adds latency to every write.
3. Replica 2 uses asynchronous replication: the primary streams the WAL without waiting for acknowledgment. This is faster but means Replica 2 might be 100ms behind. If the primary crashes, those 100ms of writes are lost.
4. The monitor (Sentinel in Redis, pg_auto_failover, or an external tool) pings each node on a heartbeat interval and detects failures.
5. The connection proxy routes traffic: writes always go to the primary, reads can go to any replica.

When the primary crashes, the failover timeline looks like this: detection (1-10 seconds) + election (1-5 seconds) + connection rerouting (1-10 seconds) = total downtime of 3-25 seconds depending on configuration.

Deep Dive 1: Failure Detection

The first question in any failover is: how does the system know the primary is dead? This is harder than it sounds, because "dead" and "unreachable" are different things.

Simple heartbeat timeout is the most common approach. The monitor pings the primary every N seconds. If M consecutive pings fail, the primary is declared dead. Typical values: N=1 second, M=3, giving a detection time of 3 seconds.

The problem: what if the primary is not dead, just slow? A garbage collection pause, a disk I/O spike, or a temporary network blip can cause missed heartbeats. If the monitor promotes a replica while the primary is still alive, you get split-brain: two nodes accepting writes simultaneously.

Deep Dive 2: Replica Promotion and Data Loss

Once the monitor declares the primary dead, a replica must be promoted. The critical question is: which replica, and how much data is lost?

The answer depends entirely on the replication mode.

Synchronous replication: The primary waits for the replica to confirm every write before telling the client "commit successful." If the primary crashes, the synchronous replica has every committed transaction. Data loss: zero.

Asynchronous replication: The primary sends the WAL but does not wait for acknowledgment. The replica might be milliseconds or seconds behind. If the primary crashes, any transactions that were committed on the primary but not yet replicated are lost. Data loss: potentially several seconds of transactions.

Semi-synchronous replication (MySQL): The primary waits for at least one replica to acknowledge, but not all of them. This guarantees at least one replica has zero lag. The tradeoff: write latency increases by the network round-trip to the nearest replica.

Deep Dive 3: Connection Rerouting and Split-Brain Prevention

After the new primary is promoted, clients need to find it. This is the "last mile" of failover and often the slowest part.

DNS failover is the simplest approach. Update the DNS A record for db-primary.example.com to point to the new primary's IP. The problem: DNS caching. Even with a TTL of 5 seconds, some clients cache DNS results for minutes. During that window, they keep connecting to the old (dead) primary.

Virtual IP (VIP) failover uses a floating IP address that moves between nodes. The new primary claims the VIP, sends a gratuitous ARP to update the network, and clients connect to the same IP address. Switchover is near-instant (under 1 second). This is how AWS RDS Multi-AZ works: the DNS name resolves to a CNAME that points to a VIP that moves during failover.

Proxy-based routing uses a connection proxy (PgBouncer for PostgreSQL, ProxySQL for MySQL) that knows which node is the primary. When failover happens, the proxy reconfigures its upstream target. Applications never change their connection string. The proxy handles the rerouting transparently.

Split-Brain Prevention

The most dangerous failure mode in database failover is split-brain: the old primary comes back online and starts accepting writes while the new primary is also accepting writes. Now you have two divergent copies of the data, and reconciling them is extremely painful (often impossible automatically).

Prevention strategies:

Fencing: Before promoting the replica, ensure the old primary cannot accept writes. This might mean shutting it down (STONITH: Shoot The Other Node In The Head), revoking its access to shared storage, or setting it to read-only mode. The new primary should not accept writes until it confirms the old primary is fenced.

Fencing tokens: Each promotion event is assigned a monotonically increasing token number. Any write operation includes the current token. Storage systems reject writes with an old token. Even if the old primary tries to write, its token is outdated, and the write is rejected.

Epoch numbers: Similar to fencing tokens but used in consensus protocols. Each new leader gets a higher epoch. Messages from old epochs are ignored.

The Tricky Parts

1. The failover timeline adds up fast: Detection (3-10 seconds) + election (1-5 seconds) + fencing (1-3 seconds) + connection rerouting (1-30 seconds depending on method). The total can be 6-48 seconds. Most teams underestimate this because they only think about the election step.

2. Application connection pools hold stale connections: After failover, existing TCP connections in the app's connection pool point to the old primary. The pool does not know the primary changed. Applications need connection validation (test-on-borrow) or must handle "connection reset" errors and reconnect automatically.

3. Read-after-write consistency breaks during failover: A write hits the new primary, but a subsequent read gets routed to a replica that has not caught up yet. The application sees stale data. This is especially dangerous for operations like "create user, then redirect to user profile page."

4. Cascading failures from reconnection storms: When all application servers detect the connection failure simultaneously, they all try to reconnect to the new primary at once. If you have 50 app servers each with a 20-connection pool, that is 1,000 simultaneous connection attempts, which can overwhelm the new primary before it has stabilized. Add jittered backoff to your connection pool's reconnection logic.

5. Long-running transactions during failover: A transaction that was in progress on the old primary when it crashed is lost. If that transaction held locks (e.g., a batch update locking 10,000 rows), those locks are gone on the new primary, and another transaction might have already acquired them. Applications with long-running transactions need idempotent retry logic.

6. Monitoring the monitor: If your failover depends on a sentinel or monitoring process, what happens when the monitor itself crashes? You need the monitor to be highly available too. Redis Sentinel solves this by requiring a quorum of sentinels (typically 3) to agree before triggering failover. Patroni uses etcd or ZooKeeper as the consensus store. A single-point-of-failure monitor turns your HA database into a not-HA system.

7. Failover during peak load: Failover is most likely to happen during peak load (because that is when the primary is under the most stress). But peak load is also the worst time for failover because the new primary immediately gets hit with the full production workload without warmup. Its buffer pool is cold, its query plan cache is empty, and its connection count jumps from zero to maximum in seconds. Some teams do periodic "failover drills" during low-traffic periods to validate the process.

8. Replication lag spikes after promotion: When a new primary is promoted, the async replicas need to catch up. But the new primary is also handling the full write workload now. If the write rate is high, the replicas might fall further behind after failover instead of catching up. Monitor replication lag closely in the minutes after failover, not just during steady state.

What Most People Get Wrong

How I Would Communicate This in an Interview

Here is how I would actually say this:

"Database failover has four phases, and I will walk through each one.

First, failure detection. A monitoring process pings the primary on a heartbeat, typically every 1-2 seconds. After 3-5 missed heartbeats, the primary is declared dead. This takes 3-10 seconds and is often the longest phase.

Second, replica promotion. The monitor checks all replicas for their replication position and promotes the one with the least lag. If using synchronous replication, the designated standby has every committed transaction, so there is zero data loss. If using async replication, you might lose the last few hundred milliseconds of commits.

Third, connection rerouting. The fastest method is a virtual IP that floats to the new primary, under 1 second. DNS-based failover is simpler but slower because of TTL caching, typically 5-30 seconds. A proxy like PgBouncer can reroute transparently.

Fourth, and most importantly, split-brain prevention. Before the new primary starts accepting writes, the old primary must be fenced: shut down, set to read-only, or have its storage access revoked. Two nodes accepting writes simultaneously is the one failure mode you absolutely cannot recover from automatically.

The total failover time is the sum of all four phases: typically 10-30 seconds for managed services like RDS, faster with Aurora at about 15 seconds."

If the interviewer asks a follow-up about managed services specifically, I would add:

"For AWS RDS Multi-AZ, failover is automatic. Amazon uses synchronous replication to a standby in a different Availability Zone. When the primary fails, RDS updates the DNS CNAME record to point to the standby. Downtime is typically 60-120 seconds, which includes detection, promotion, DNS propagation, and connection draining. The application sees connection errors during this window and needs retry logic.

Aurora is architecturally different. It uses a shared storage layer across all nodes, so replicas do not need to replay WAL logs during promotion. Failover is typically 15-30 seconds. Aurora also supports reader endpoints that automatically exclude the failed instance.

For self-managed PostgreSQL, I would use Patroni with etcd as the consensus store. Patroni handles heartbeats, leader election, and automatic failover. It uses etcd's distributed locking to prevent split-brain. Combined with a connection proxy like PgBouncer, you get transparent failover with minimal application changes."

Interview Cheat Sheet

• When asked about database failover: "Four phases: detection, promotion, rerouting, fencing. Total time is the sum of all four."
• On failure detection: "Heartbeat every 1-2 seconds, 3-5 missed beats to declare failure. 3-10 seconds total."
• On data loss (RPO): "Synchronous replication = zero data loss. Async = you lose whatever had not been replicated yet."
• On RDS Multi-AZ: "Automatic failover, 60-120 seconds downtime. Synchronous replication to standby in different AZ."
• On Aurora: "Faster failover at 15-30 seconds. Shared storage means replicas do not need to catch up on WAL."
• On split-brain: "The worst possible outcome. Prevent it with fencing: STONITH, revoking storage access, or fencing tokens."
• On connection rerouting: "VIP is fastest (under 1 second). DNS is slowest (5-30 seconds due to caching). Proxy is the most transparent."
• On reconnection storms: "After failover, all app servers reconnect simultaneously. Add jittered backoff to connection pools to prevent overwhelming the new primary."
• On testing failover: "Run failover drills in production during low-traffic windows. Staging does not replicate production connection counts and data volumes."
• On post-failover recovery: "After failover, rebuild replication, reconfigure the old primary as a replica, and validate data consistency. You are running without redundancy until recovery completes."
• On application-side handling: "Connection pools need validation. Applications need retry logic. Route writes through a proxy."
• On RPO vs RTO: "RPO = how much data you lose. RTO = how long until you are back online. They trade off against each other."
• On managed vs self-managed: "Managed (RDS, Cloud SQL) handles detection, promotion, and rerouting. Self-managed (Patroni, pg_auto_failover) gives you control but requires operational investment."

Test Your Understanding

Quick Recap

1. Database failover has four phases: failure detection, replica promotion, connection rerouting, and split-brain prevention. Total time is the sum of all four, typically 10-30 seconds.
2. Failure detection uses heartbeat timeouts (3-10 seconds) or adaptive detectors like phi accrual. Faster detection increases false positive risk.
3. Synchronous replication guarantees zero data loss (RPO=0) but adds write latency. Async replication is faster but can lose recent transactions.
4. Always promote the replica with the least replication lag. With synchronous replication, this is automatic.
5. Connection rerouting options: VIP (fastest, under 1 second), proxy (transparent), DNS (slowest, 5-30 seconds due to caching).
6. Split-brain (two primaries accepting writes) is the worst failure mode. Prevent it with fencing, STONITH, or fencing tokens.
7. Applications need retry logic, connection validation, and awareness that the primary might change. Connection pools hold stale connections.
8. RPO (data loss tolerance) and RTO (downtime tolerance) are independent axes that drive replication and failover strategy choices.

Related Concepts

• Consensus protocols (Raft, Paxos) are the foundation of leader election in distributed databases like CockroachDB, etcd, and TiKV.
• Replication strategies (synchronous, asynchronous, semi-synchronous) directly determine your RPO during failover.
• Connection pooling (PgBouncer, ProxySQL, HikariCP) is the application-side component that must handle primary changes gracefully.
• CAP theorem explains why you must choose between consistency and availability during a network partition, which is exactly the tradeoff you face during failover.
• Service discovery mechanisms (DNS, Consul, ZooKeeper) provide the routing layer that clients use to find the current primary.
• Chaos engineering (running controlled failover drills in production) is how teams validate that their failover actually works under realistic conditions, not just in staging.
• Circuit breakers in the application layer complement database failover by preventing cascading failures when the database is mid-failover. They stop retrying during the failover window and resume once the new primary is stable.