Database Replication: Sync vs Async, Lag, Failover & Topologies

Database Replication

Replication keeps copies of the same data on multiple database servers. It's how databases survive machine and zone failures, scale read traffic beyond one server, keep a warm copy in another region for disaster recovery, and feed analytics or search systems without loading the primary.

Replication also introduces the hardest problems in data systems: copies that lag behind, failovers that lose recent writes, and two servers both believing they're the primary. Understanding the trade-offs between consistency, latency, and availability lets you choose the right setup — and write application code that behaves correctly on top of it.

TL;DR

Primary–replica (single leader) is the most common topology: one node accepts writes, replicas copy them.
Synchronous replication avoids data loss on failover but adds write latency; asynchronous is fast but can lose recent writes.
Physical replication copies storage-level changes (whole cluster); logical replication copies row changes (selective, cross-version).
Replicas lag; route read-your-writes traffic to the primary or wait for a replica to catch up.
Replication is not a backup — deletes and corruption replicate too.
Automate failover with fencing to avoid split brain.

Quick Example

PostgreSQL streaming replication with one synchronous standby and one asynchronous read replica:

Check lag from the primary:

In application code, send reads that must see the user's own writes to the primary:

Core Concepts

Topologies

Cassandra and DynamoDB are leaderless; CockroachDB, Spanner, and etcd use consensus; PostgreSQL and MySQL are primarily single-leader.

Synchronous vs Asynchronous

Many production setups use one synchronous standby in another zone for zero data loss and additional asynchronous replicas for reads and remote regions.

Physical vs Logical Replication

Physical (PostgreSQL streaming, MySQL InnoDB cluster storage-level copies, Aurora storage) ships low-level changes; replicas are byte-for-byte identical, same major version, whole cluster.
Logical (PostgreSQL publications/subscriptions, MySQL row-based binlog) ships row-level changes; you can replicate selected tables, across versions, or into other systems. It underpins change data capture and near-zero-downtime upgrades.

Replication Lag and Consistency Anomalies

Asynchronous replicas trail the primary by milliseconds to minutes. Anomalies include:

Read-your-writes violations — a user updates their profile, then sees the old version from a replica.
Monotonic read violations — two reads hit different replicas and data appears to go backward.
Causality violations — a reply is visible before the message it replies to.

Mitigations: read from the primary after writes (for a time window or session), pin a user's session to one replica, or wait for a replica to reach a known log position.

Failover and Split Brain

When the primary fails, a replica is promoted. Automated tools (Patroni, MySQL Group Replication, orchestrator, managed cloud services) detect failure, choose the most up-to-date replica, and redirect clients. Split brain — two primaries accepting writes — happens if the old primary isn't truly stopped; prevent it with fencing (STONITH, revoked credentials, network isolation) and consensus-based leader election.

Best Practices

Match Replication Mode to RPO

Define acceptable data loss per system, then choose synchronous, semi-synchronous, or asynchronous replication to meet it. See Disaster Recovery Planning.

Monitor Lag and Alert on It

Track replication lag in bytes and seconds and alert well before it affects users or failover safety.

Keep Backups Separate From Replication

A DELETE without WHERE replicates instantly. Keep point-in-time backups independent of replicas. See Database Backups.

Test Failover Regularly

Perform planned switchovers in staging and production maintenance windows to measure downtime and verify clients reconnect.

Use Replication Slots Carefully

PostgreSQL replication slots guarantee a replica won't miss WAL, but an abandoned slot retains WAL until the disk fills. Monitor and drop unused slots.

Design the Application for Lag

Decide explicitly which reads can be stale and route the rest to the primary.

Common Mistakes

Treating Replicas as Backups

Replication protects against hardware failure, not human error, bugs, or ransomware.

Sending All Reads to Replicas Blindly

Users see stale data immediately after writes, which looks like lost updates.

Synchronous Replication With a Single Standby

If the only synchronous standby goes down, writes on the primary may block. Use quorum settings or fall back policies deliberately.

Manual Failover Without Fencing

Promoting a replica while the old primary still accepts writes creates divergent data that's very hard to reconcile.

Cross-Region Synchronous Replication for Every Write

Adding 50–100 ms of round-trip latency to every commit often costs more than it's worth; use it only where zero data loss across regions is required.

FAQ

What is database replication?

Keeping copies of a database on multiple servers so that data remains available if one fails, reads can be spread across servers, and a copy exists in another location for disaster recovery.

What's the difference between synchronous and asynchronous replication?

Synchronous replication waits for a replica to confirm each commit, preventing data loss on failover at the cost of latency. Asynchronous replication commits immediately and ships changes afterward, which is faster but can lose recent writes.

Is replication the same as backup?

No. Replication copies every change, including accidental deletions and corruption. You still need independent, point-in-time backups.

How do I handle replication lag in my app?

Route reads that must reflect recent writes to the primary, keep sessions sticky to one replica for monotonic reads, or wait until a replica has applied a specific log position.

What is split brain?

A failure scenario in which two nodes both act as primary and accept writes, causing divergent data. Fencing and consensus-based leader election prevent it.