Replication is the process of copying data from one database server (the primary) to one or more other servers (replicas) in near-real time. It is the foundation of high availability, disaster recovery, and read scaling in almost every production database architecture. Understanding the tradeoffs — sync vs async, physical vs logical — determines whether your system survives a primary failure with zero data loss or with a gut-wrenching recovery.
What changed in 2026
- Managed replication is the default. AWS RDS, Google Cloud SQL, PlanetScale, and Neon all provision read replicas with one click, with automated failover. Self-managed replication is now mainly for teams with specific constraints.
- Logical replication matured in PostgreSQL. Postgres 16+ logical replication supports DDL replication (table creation/alteration), closing a major usability gap and making CDC pipelines simpler.
- Change Data Capture (CDC) with Debezium is mainstream. Streaming database changes to Kafka via Debezium is a standard data engineering pattern, replacing brittle polling jobs.
- Neon's copy-on-write branching showed that replication concepts extend beyond high availability into developer experience — branch a production database like a Git branch.
How replication works
PRIMARY DB ──write──▶ WAL / binlog ──stream──▶ REPLICA DB
◀──read queries ◀──read queries
Every write to the primary is recorded in the write-ahead log (Postgres WAL) or binary log (MySQL binlog). Replicas consume this log stream to apply the same changes in order.
Synchronous vs asynchronous replication
| Mode |
How it works |
Durability |
Write latency |
| Synchronous |
Primary waits for replica ACK before confirming write |
No data loss on failover |
Higher (~5–20 ms extra per write) |
| Asynchronous |
Primary confirms immediately; replica catches up |
Up to N seconds of data loss |
No overhead on primary |
| Semi-synchronous |
Primary waits for 1-of-N replicas; others async |
Bounded data loss |
Moderate overhead |
PostgreSQL uses synchronous_commit = on (sync) or off (async). MySQL supports semisynchronous replication natively.
For most OLTP apps, semi-synchronous on at least one replica is the right default: strong durability guarantee without blocking all replicas.
Physical vs logical replication
Physical replication (streaming replication in Postgres) copies the raw byte-level changes in the WAL. The replica is an exact byte-for-byte copy of the primary.
# postgresql.conf (primary)
wal_level = replica
max_wal_senders = 5
Logical replication decodes the WAL into row-level changes (INSERT/UPDATE/DELETE) and streams them. It can target a subset of tables, filter rows, and replicate across major Postgres versions.
-- Primary: create a publication
CREATE PUBLICATION app_pub FOR TABLE users, orders;
-- Replica: subscribe to it
CREATE SUBSCRIPTION app_sub
CONNECTION 'host=primary dbname=app user=replicator'
PUBLICATION app_pub;
Logical replication enables CDC pipelines, cross-version migrations, and selective replication that physical replication cannot.
Common replication architectures
| Pattern |
Use case |
| Primary + 1 async replica |
Basic HA and read offload |
| Primary + 1 sync + N async replicas |
Durability guarantee + read scale |
| Multi-primary (Galera, CockroachDB) |
Multi-region writes, higher complexity |
| Logical CDC to Kafka |
Real-time data pipelines, event sourcing |
| Replication to read-optimized store |
Replicate Postgres to Elasticsearch or ClickHouse |
How to pick
- Is durability your top concern? Use synchronous replication to at least one replica.
- Is write latency your top concern? Use asynchronous replication and accept the small failover risk.
- Do you need to stream changes to other systems? Logical replication + Debezium → Kafka.
- Cross-version migration? Logical replication between Postgres 15 and 16 is the standard zero-downtime upgrade path.
- Multi-region writes? Consider CockroachDB or Spanner rather than custom multi-primary MySQL.
Common mistakes
Ignoring replication lag. Async replicas can be seconds or minutes behind under write load. Reading from a lagging replica in a flow that just wrote data returns stale results.
# Bad: write to primary, immediately read from replica
user = db_replica.query("SELECT * FROM users WHERE id = ?", new_id)
# May not exist yet if replica is lagging
# Good: read-your-writes — route post-write reads to primary briefly
No monitoring on replica lag. pg_stat_replication.write_lag and replica_lag CloudWatch metric must be on your alerts dashboard.
Relying on replicas for backups. A replica propagates corruption and destructive queries from the primary. Backups must be independent snapshots (pg_dump, WAL-G point-in-time).
Forgetting sequences and identity columns. Sequences are not replicated by default in Postgres logical replication. Use FOR ALL TABLES or explicitly include sequences in your publication.
What to skip
- Manual primary-replica failover scripts — use Patroni (Postgres), Orchestrator (MySQL), or your cloud provider's automated failover.
- Multi-primary setups on relational databases unless you have a strong reason — the conflict resolution complexity is substantial.
- Replicating to reduce backup costs — replicas are not backups; run proper PITR backups separately.
FAQ
How much read traffic can a replica handle?
A replica handles roughly the same read throughput as the primary hardware allows, minus the overhead of applying the replication stream. Benchmarks on a primary-equivalent instance show ~95 % of standalone read capacity.
Can I promote a replica to primary without data loss?
With synchronous replication: yes. With asynchronous replication: only if the replica is fully caught up at the moment of failover — automated tools like Patroni check this.
What is replication slots?
A replication slot is a Postgres mechanism that ensures the primary retains WAL segments until a subscriber has consumed them. Useful for logical replication, but dangerous if a subscriber goes offline — the primary disk fills.
Does replication work across cloud regions?
Yes, but round-trip latency between regions makes synchronous replication costly (50–150 ms per write for cross-region sync). Most multi-region setups use async replication with manual or automated failover.
Where to go next