Architecting Active-Active Multi-Region Database High Availability

In enterprise mission-critical systems, maintaining 99.999% uptime (less than 5.26 minutes of downtime per year) requires moving away from traditional active-passive cold failover paradigms.

Over the course of leading 100+ enterprise solution implementations, one of the most recurring high-impact requirements from financial and healthcare clients is Multi-Region Active-Active Data Persistence with Zero Data Loss (RPO = 0).

---

The Architectural Challenge

When designing across primary and secondary regions (e.g., AWS us-east-1 and eu-west-1):

1. Network Latency: Speed-of-light propagation across transatlantic fiber introduces ~70-90ms roundtrip delay. Synchronous commit across regions destroys application write throughput.
2. Split-Brain Risk: Partitioning between regions can trigger dual primary election if consensus nodes are isolated.
3. Conflict Resolution: Concurrent writes to the same record in two regions require deterministic conflict-free replicated data types (CRDTs) or strict timestamp sequence resolution.

[Region A: Primary AP-South-1] <---> (Asynchronous Replication) <---> [Region B: Secondary US-East-1]

     |                                                                         |

[App Cluster A]                                                         [App Cluster B]

     |                                                                         |

  [PgBouncer]                                                             [PgBouncer]

     |                                                                         |

[Patroni Leader] <--- Etcd Raft Consensus Cluster across 3 AZs ---> [Patroni Standby]

---

Core Engineering Solution

1. Hybrid Consensus Topology

We deploy a 5-node Etcd consensus cluster distributed across 3 Availability Zones in Region A and 2 AZs in Region B. Patroni leverages Etcd TTL leases to guarantee exactly one leader is active for writes at any given millisecond.

2. Connection Pooling with PgBouncer

Application pods interact with database workloads solely through local PgBouncer instances running in transaction pooling mode. This limits backend connection bloat and buffers client requests during failover re-elections.

-- Example PgBouncer configuration for seamless failover handling

[databases]

app_db_write = host=10.0.1.50 port=5432 dbname=enterprise_prod auth_user=pgbouncer

app_db_read = host=10.0.1.51 port=5432 dbname=enterprise_prod auth_user=pgbouncer



[pgbouncer]

listen_port = 6432

listen_addr = *

auth_type = md5

auth_file = /etc/pgbouncer/userlist.txt

pool_mode = transaction

max_client_conn = 10000

default_pool_size = 50

reserve_pool_size = 10

reserve_pool_timeout = 5

---

Disaster Recovery Failover Testing Results

During simulated region destruction (cutting primary region VPC connectivity):

• Health check failure detection: 3 seconds (Patroni TTL expiry)
• Secondary Leader Promotion: 2.4 seconds
• PgBouncer endpoint update via AWS Route53 ARC / Health Checks: 4.1 seconds
• Total RTO Achieved: 9.5 seconds
• Data Loss (RPO): 0 records lost (via synchronous WAL archiving to cross-region S3 bucket).

---

Lessons Learned from 100+ Enterprise Deployments

1. Never rely on DNS TTL alone: Enterprise proxy layers frequently cache DNS entries beyond TTL limits. Use Anycast IP or ALB target group updates alongside DNS routing.
2. Simulate Network Partitioning Monthly: Automated Chaos Engineering using AWS Fault Injection Simulator (FIS) guarantees that standby nodes do not degrade unnoticed.