Would you enable fully automatic failover for a production OLTP relational database, and how would you set detection thresholds, promotion criteria, and failback policy? Justify the tradeoffs.

## The decision The question is what the system does in the ninety seconds after a primary stops responding at 03:00. Manual failover means a page, a wake-up, context, verification, and a runbook: realistically five to twenty minutes. Automatic means tens of seconds. For an OLTP system with a tight RTO, automation wins on the common case (a host, disk, or process failure) as long as it cannot cause the uncommon catastrophe (two primaries or an avoidable data loss). So I frame it as: automate the decision, but constrain it so the worst outcome it can produce is bounded unavailability, never divergence. ## Preconditions I require before enabling it 1. **Quorum-gated leadership.** Promotion requires a majority of a fixed member set, held in a consensus store, so a minority partition can never elect. 2. **Confirmed fencing.** The old primary is provably unable to write, and if that cannot be confirmed, the automation aborts and pages rather than promoting. 3. **Candidate selection by position**, with a maximum-lag rule: if the best candidate is behind by more than the configured threshold, escalate instead of promoting. 4. **Automated rejoin.** The returning old primary is checked for divergence and rewound or reinitialised automatically, never allowed to accept writes first. 5. **A routing layer that follows leadership**, so promotion actually redirects clients and stale routes cannot keep writing to the old node. 6. **Application-side retry** on connection loss with idempotent operations, because every failover aborts in-flight transactions. Without the first three I would not enable automation; the failure mode is worse than the outage it prevents. ## Setting the thresholds Detection is a tradeoff between false positives and RTO. Set it from data, not folklore: look at the p99 and p99.9 of health-check latency, network round trip, checkpoint and GC pause duration, and the consensus store's own latency. The detection budget must exceed the worst benign stall you actually observe, with margin. In lease-based systems this is the TTL, commonly around 30 seconds, with an agent loop several times faster. I require multiple consecutive failures and, where the tooling supports it, corroboration from more than one observer, so a single flaky monitor cannot trigger a promotion. I also prefer checks that exercise the real path (a trivial query on a real connection) over a TCP-port ping, which stays healthy while the database is unusable. Cost of getting it wrong in each direction: too aggressive means spurious failovers, and each one costs aborted transactions, a client reconnect storm, cache warm-up on the new primary, and a rewind of the demoted node. Too conservative means paying extra seconds on every real failure. In most OLTP shops the spurious-failover cost is higher than people expect, so I start conservative and tighten with evidence. ## Anti-flapping and operator control - Rate-limit: no more than one automatic failover per cluster per configured window; beyond it, freeze and page. - Cooldown on a recently demoted node before it is eligible again. - Maintenance/pause mode that suspends automation during patching, restores, or bulk loads, with an expiry so nobody leaves it off. - Loud, structured alerting on every automated action, including refusals, since a refusal is often the more important signal. ## Failback Failback should be manual and planned. After the incident the old primary rejoins as a replica; whether it should be primary again is a question of hardware, site, and load, and it is another write interruption. Doing it automatically doubles the disruption from one incident and can ping-pong if the underlying fault is intermittent. The right shape is a scheduled switchover during a low-traffic window, once the node has proven stable, and only if there is a real reason to prefer it. ## Proving it works Automation that has never been exercised is a hypothesis. I schedule regular switchovers in production (they are lossless) and periodic true failover drills in a production-like environment, measure the actual RTO and RPO, and treat every drill finding as a defect. I also verify the boring parts that break silently: fencing device credentials, replication slots on the new primary, backup jobs following the writer, and monitoring that survives the role change. ## Interview framing Give a clear answer (yes, with conditions), list the preconditions that make it safe, derive thresholds from measured distributions rather than defaults, cover flapping controls, state that failback is planned rather than automatic, and end on rehearsal and application retry behaviour.