Design a high-availability setup for a critical PostgreSQL database with RPO near zero and RTO under one minute.
What the interviewer wants to hear
- Primary with replicas in different availability zones
- Synchronous replication to at least one standby for near-zero RPO
- Automatic failover with a consensus store, e.g. Patroni with etcd
- Fencing to avoid split-brain
- Stable endpoint for apps, e.g. virtual IP, proxy or DNS
- Backups and PITR still required; test failovers
Model answer
I would run three nodes across three availability zones: one primary and two standbys. For near-zero RPO I use synchronous replication with synchronous_standby_names set to ANY 1 of the two standbys, so a commit waits for one copy, but losing one standby does not stop writes. Patroni manages failover, using etcd or another consensus store with three members, so only the node holding the leader key can be primary. That quorum plus fencing, where the old primary demotes itself if it loses the lock, prevents split-brain. Applications connect through HAProxy or PgBouncer that follows the leader, so failover takes seconds. Sync replication adds latency to every commit, which we accept. HA does not protect against a bad DELETE, so pgBackRest backups with WAL archiving and PITR remain essential, and we test failover regularly.
Can you answer it under time pressure?
Take a timed hard round and get scored on every answer.
More hard Database Administrator questions
- Your single database is reaching its limits. How do you decide whether to shard, and how would you do it?
- How would you upgrade a large production PostgreSQL database to a new major version with minimal downtime?
- The application is timing out and you suspect lock contention in production. Walk through how you diagnose and fix it.
- Someone ran DELETE without a WHERE clause on an important production table 20 minutes ago. What do you do?
- Design a disaster recovery plan for a critical database that must survive the loss of a whole cloud region.
- How would you migrate a 5 TB on-premises PostgreSQL database to a managed cloud service with less than 15 minutes of downtime?
- A critical 1 TB PostgreSQL table is 60 percent bloat and queries are slowing. How do you fix it without downtime and stop it coming back?
- The business expects traffic to grow ten times over the next 18 months. How do you plan database capacity?
- Your company must meet compliance rules (such as PCI DSS or GDPR) for a database holding personal and card data. What controls do you put in place?