Synchronous vs Asynchronous vs Semi-Sync Replication

When configuring database replication, systems engineers must make a fundamental architectural choice balancing Recovery Point Objective (RPO) against Write Latency:

• Recovery Point Objective (RPO): The maximum acceptable amount of data loss measured in time (e.g., RPO = 0 means zero data loss; RPO = 5s means up to 5 seconds of data can be lost during a disaster).
• Recovery Time Objective (RTO): The maximum acceptable duration of system downtime required to restore service.

1. Asynchronous Replication

• The Primary writes to its local WAL, commits the transaction, and immediately returns 200 OK to the client.
• The WAL stream is transmitted to replicas asynchronously in the background.
• Pros: Maximum write throughput and minimal write latency (~1-2ms). The Primary continues operating smoothly even if all replicas crash or become unreachable.
• Cons: If the Primary host suffers an unrecoverable hardware failure, un-replicated WAL bytes sitting in flight are permanently lost (RPO > 0).

2. Fully Synchronous Replication

• The Primary writes the WAL, transmits it to the replica, and blocks, waiting for the replica to flush the WAL to physical disk and reply with an ACK before acknowledging the client.
• Pros: Zero Data Loss Guarantee (RPO = 0). If the primary explodes, the replica is mathematically guaranteed to have 100% of all committed transactions.
• Cons: Write latency increases by the full network Round-Trip Time (RTT) to the replica. Availability Risk: If a single synchronous replica freezes or experiences a network hiccup, all database writes on the Primary are completely blocked!

3. Semi-Synchronous Replication (The Cloud Production Standard)

• Used heavily by MySQL (rpl_semi_sync), PostgreSQL (synchronous_commit = remote_write), and AWS Aurora.
• The Primary commits the write as soon as at least ONE replica acknowledges that it received the WAL record in memory (Relay Log), without waiting for the replica to execute the slower disk fsync().
• Configurable Fallback: If the replica does not ACK within a timeout (e.g., 500ms), the Primary temporarily degrades to Asynchronous mode to protect system availability.

Modern cloud distributed databases (AWS Aurora, Google Spanner, CockroachDB) use Quorum-Based Replication:

• Writes are replicated to N Availability Zones (e.g., N = 6 storage nodes across 3 AZs).
• The write is acknowledged as soon as a Write Quorum (W = 4 of 6 nodes) confirms storage.
• A single lagging or crashed node in one data center does not delay write latencies or block transactions, combining RPO=0 durability with high availability.

-- PostgreSQL postgresql.conf
# Require at least 1 replica out of 3 to ACK WAL receipt synchronously:
synchronous_commit = on
synchronous_standby_names = 'FIRST 1 (replica_node_1, replica_node_2, replica_node_3)'