Opscraft
Database operations · Lesson 3 of 6 · 4 min read

Replication and high availability

Synchronous and asynchronous replication

  • Synchronous: the primary waits until a replica confirms it has the change before acknowledging the commit. No data loss on failover (RPO 0), but every write pays the network round trip, and if the replica stalls, writes stall.
  • Asynchronous: the primary acknowledges immediately and ships changes afterwards. Fast, but replicas lag behind, and a failover can lose the last few transactions.
  • Replication lag is how far behind a replica is (seconds, or bytes of WAL). Reads from a lagging replica return old data, so "I saved it but the page doesn't show it" bugs appear when reads go to replicas.
Enlarge diagramSynchronousAsynchronouscommit waits for the replicano data lost on failover (RPO 0)every write pays the round tripcommit returns at oncereplica lags behindfailover may lose the last writes
Figure 1. Synchronous replication waits for the replica before confirming; asynchronous confirms first and ships later.
Swipe sideways to see all of it, or tap to open it full screen.

PostgreSQL uses streaming replication (physical, the whole cluster) and logical replication (selected tables, across versions, useful for migrations and upgrades). MySQL replicates using the binary log, with GTIDs to track position.

RDS and Aurora options

Option How it works Failover Readable?
RDS Multi-AZ (instance) Synchronous standby in another AZ Automatic, typically 1–2 minutes No, the standby only waits
RDS Multi-AZ DB cluster One writer plus two readable standbys in different AZs Typically under 35 seconds Yes
RDS read replicas Asynchronous copies, same or another region Manual promotion Yes
Aurora Shared storage keeping 6 copies across 3 AZs; up to 15 replicas Usually under 30 seconds, to a replica Yes
Aurora Global Database Storage-level replication to other regions, typically under a second behind Managed switchover or failover to another region Yes, in secondary regions

Read replicas are for scaling reads and some DR; Multi-AZ is for availability. They solve different problems and are often used together.

Enlarge diagramprimary (AZ a)reads and writesstandby (AZ b)synchronous, takes overread replicaasynchronous, readsreplica (other region)reads and DRsync
Figure 2. Multi-AZ keeps a standby for failover; read replicas spread reads. They solve different problems.
Swipe sideways to see all of it, or tap to open it full screen.

Surviving a failover

During failover, the database endpoint's DNS record is moved to the new primary. Applications must cope:

Enlarge diagramPrimary failsconnections breakEndpoint movesDNS → new primaryApp reconnectspool drops dead connectionsRetry with back-offtransient errors onlyShort DNS cacheJVM TTL ≈ 5 sRDS Proxyhides most of this123456
Figure 3. During a failover the endpoint moves to the new primary. Apps must reconnect and retry.
Swipe sideways to see all of it, or tap to open it full screen.
  • Reconnect and retry: connections to the old primary break, so the app's connection pool must detect dead connections and retry with back-off.
  • Don't cache DNS forever: the JVM in particular can cache DNS; set a short TTL (networkaddress.cache.ttl=5 or similar) so the new address is picked up.
  • Use the right endpoint: the cluster (writer) endpoint for writes, the reader endpoint for reads, never an instance endpoint for the primary.
  • RDS Proxy keeps client connections open and reconnects to the new primary itself, cutting the visible failover time considerably.
  • Test it: aws rds reboot-db-instance --force-failover or Aurora's failover action in a staging environment, while running load, and watch what the app does.