Designing an HA/DR strategy for an OLTP database

WHAT THIS TESTS The interviewer checks whether you can map RPO (how much data you can lose) and RTO (how fast you must recover) onto a real replication and failover design, and whether you understand the latency and cost tradeoffs each choice imposes.

A GOOD ANSWER COVERS Distinguish HA (surviving a node failure within a region) from DR (surviving a regional outage). For HA, run a synchronous standby in a second availability zone so every committed write exists on two nodes, giving an RPO near zero; accept that commits now wait for the standby acknowledgement, adding latency. For DR, add an asynchronous replica in another region so cross-region network lag never blocks the primary, accepting a small RPO equal to replication lag. Drive RTO with automated failover: a managed service or an orchestrator like Patroni using a consensus store promotes a healthy replica, redirects traffic via a virtual IP or DNS, and fences the old primary to prevent split-brain. Layer in regular backups with point-in-time recovery to handle logical corruption that replication would faithfully copy.

COMMON WRONG ANSWERS Claiming synchronous replication has no cost; it trades write latency and availability if the standby is unreachable. Treating replicas as a backup; a dropped table replicates instantly, so you still need PITR backups. Ignoring split-brain and never rehearsing failover.

LIKELY FOLLOW-UPS What happens to writes if the synchronous standby goes down? How do you tune the tradeoff with semi-synchronous modes? How do you test DR without endangering production?

ONE CONCRETE EXAMPLE A payments database uses a synchronous standby in AZ-b for an RPO near zero, an async replica in another region with about five seconds of lag for DR, Patroni with etcd for automatic promotion targeting an RTO under thirty seconds, and nightly base backups with continuous WAL archiving for point-in-time recovery.