Multi-Region Reads and Failover

Scenario

Example company: a project-management SaaS with its primary database in us-east. A fast-growing customer base in Europe and Asia sees 250–400 ms extra latency on every page. The team adds read replicas in eu-west and ap-south and plans automatic cross-region failover.

What you see in production

• European page loads improve — except pages that write, then read (still 2 round trips across the Atlantic).
• A game day test of failover reveals that the new primary lacked the last few seconds of writes: orders confirmed to customers were missing after the switch.

The naive code

Treat the remote region like a local replica and fail over automatically on any primary outage:

us-east: primary  ── async ──>  eu-west: replica (also failover target)
                  ── async ──>  ap-south: replica
failover policy: promote the healthiest replica as soon as the primary is unreachable for 10 s

Reproduce it

Writes acknowledged by the primary but not yet shipped are lost if a replica is promoted:

primary_log = []
replica_log = []
SHIP_DELAY = 3                          # entries in flight across the ocean

for order_id in range(1, 11):
    primary_log.append(order_id)        # COMMIT on the primary, customer sees "confirmed"
    if len(primary_log) > SHIP_DELAY:
        replica_log = primary_log[:-SHIP_DELAY]

print("primary committed:", primary_log)
print("replica has:      ", replica_log)
print("primary region goes dark -> promote replica")
lost = [o for o in primary_log if o not in replica_log]
print("acknowledged but lost after failover:", lost)

primary committed: [1, 2, 3, 4, 5, 6, 7, 8, 9, 10]
replica has:       [1, 2, 3, 4, 5, 6, 7]
primary region goes dark -> promote replica
acknowledged but lost after failover: [8, 9, 10]

Root cause

Physics sets the floor: a round trip between continents is 70–250 ms. With asynchronous replication, a remote replica serves fast reads but is behind, and promoting it discards whatever wasn't shipped. With synchronous replication, every commit waits for the remote ack, adding that round trip to every write.

GitHub's October 2018 incident shows the operational side: a 43-second loss of connectivity between its US East Coast network hub and primary data center triggered automated failover that moved MySQL primaries to the US West Coast. Writes in both locations then had to be reconciled, and GitHub chose to degrade service for 24 hours and 11 minutes to protect data consistency rather than serve inconsistent data (GitHub incident analysis).

The fix

1. Follower reads for data that tolerates staleness (catalogs, reference data, other users' content), with read-your-writes routing for the user's own data.
2. Same-region synchronous standby for failover without data loss; cross-region replicas for reads and disaster recovery.
3. Cross-region failover is a decision, not a reflex: require a longer outage, verify how far behind the target is, and fence the old primary before promoting.
4. If many regions must write with low latency, partition data by home region (each tenant's data lives in one region) instead of multi-primary on the same rows.

-- primary
ALTER SYSTEM SET synchronous_standby_names = 'FIRST 1 (standby_a, standby_b)';   -- local AZ standbys
SELECT pg_reload_conf();
-- cross-region replica stays asynchronous; check how far behind it is before promoting:
SELECT application_name, sync_state, replay_lag FROM pg_stat_replication;

def engine_for(request, *, needs_fresh=False):
    if needs_fresh or recently_wrote(request.user):
        return primary_engine               # cross-region, consistent
    return local_replica_engine[request.region]   # fast, possibly seconds behind

Trade-offs

• Synchronous cross-region replication: no data loss on failover, +1 inter-region RTT per commit.
• Asynchronous: fast writes, a recovery point objective (RPO) of "whatever was in flight".
• Multi-primary on shared rows needs conflict resolution (last-write-wins loses data; CRDTs fit only some data types) — see Distributed Systems.
• Home-region partitioning keeps writes local but makes cross-region queries and user migration harder.

Detect it in production

• Replication lag per region and the amount of WAL/binlog not yet shipped (your effective RPO at any moment).
• Game days: practise failover and measure data loss and recovery time.
• Alert on unexpected topology changes (who is primary?) and fence old primaries.

Key points

• Remote replicas speed up reads but are behind; remote failover can lose acknowledged writes.
• Use local sync standbys for HA, remote async replicas for reads/DR.
• Treat cross-region failover as a deliberate, verified operation.