Why architecture matters here
Replication choice determines what you lose in a failure. Sync gives zero data loss at the cost of write latency and availability. Async gives low latency and high availability at the cost of potential data loss during failover. Multi-master gives write locality at the cost of conflict handling complexity.
The architecture matters because operational incidents (failover, promotion, split brain) are where these guarantees are tested. A misconfigured failover promotes a lagging replica and loses committed writes. A missing fencing step allows a zombie primary to accept writes.
Knowing the pieces means you plan for the failure, not hope for the best.
The architecture: every piece explained
The top strip is the ship path. Primary accepts writes and produces WAL / redo — byte-level changes describing each modification. Physical replica replays the WAL byte-for-byte (Postgres streaming, MySQL row-based binlog with GTID). Logical replica replays row-level operations (INSERT/UPDATE/DELETE), allowing cross-version or cross-schema replication.
The middle row is the modes. Sync mode waits for quorum acknowledgment from replicas before committing to the client; zero data loss but higher latency. Async mode commits and ships later; low latency but non-zero RPO. Multi-master allows multiple primaries to accept writes concurrently. Conflict resolution picks a winner — Last-Write-Wins (LWW), CRDTs for certain data types, or custom application logic.
The lower rows are operations. Failover promotes a replica to primary; fencing must isolate the old primary to prevent split brain. Read scaling routes reads to replicas; be aware of replica lag and stale reads. Monitoring tracks replication lag (bytes and time), apply lag (behind on replay), conflict rate for multi-master, and drills exercise RPO/RTO regularly.
End-to-end flow
End-to-end: a Postgres cluster with one primary and three streaming replicas serves an application. The primary uses sync replication with quorum = 2, so a commit returns only after two replicas ack. Reads route to a load-balanced pool of replicas with a stale-read tolerance of 1 second. When the primary fails, the orchestrator promotes the most caught-up replica; fencing isolates the failed primary by revoking its role in the load balancer and shutting off its replication feed. Application reconnects; writes resume within 30 seconds. Because sync was quorum, no committed data is lost. RPO drill quarterly validates the runbook.