RDBMS replication from the operator's chair
Managed Postgres/MySQL exposes primary-replica replication: one writable leader, N read replicas applying a stream of changes. This topic is the product lens — how to configure, route, and fail over — not the abstract quorum theory in Distributed Systems → Replication.
Client write → Primary → WAL/binlog stream → Replica applies → readable (maybe stale)
Physical vs logical replication
| Mode | What ships | Use case |
|---|---|---|
| Physical (streaming) | WAL pages / InnoDB redo | Hot standby, byte-identical replica |
| Logical (decoding) | Row-level change events | Partial replication, upgrades, CDC to Kafka |
Physical is lower overhead; logical is flexible but CPU-heavy.
Sync vs async replicas
| Setting | Durability | Failover data loss |
|---|---|---|
synchronous_commit = on (1+ sync standbys) | Commit waits for replica WAL flush | Minimal |
| Async (default in many clouds) | Primary acks after local WAL | Last seconds of writes if primary dies before stream ships |
Semi-sync (MySQL): wait for one replica ack, async to others.
Read routing and replication lag
Replicas lag under write load. Symptoms:
- User posts, refresh shows old data
- Unique constraint "flickers" across replicas
- Read-your-writes violated
Mitigations: sticky sessions to primary, lag-aware routing (ProxySQL, PgBouncer + application token), or read from leader for critical paths.
Failover mechanics
On primary death:
- Detect failure (health checks, Patroni/etcd, RDS automation)
- Promote most caught-up replica
- Fence old primary (STONITH, revoke VIP, disable writes)
- Repoint DNS/connection pool
Split brain — two nodes accepting writes — causes divergent data requiring painful merge.
Managed vs DIY
RDS Multi-AZ (sync standby, automatic failover) vs read replicas (async, manual promotion). Know what your cloud actually guarantees — marketing "HA" ≠ zero RPO.
Cross-references
- Quorum math, leaderless replication → Distributed Systems → Quorums
- Consensus-based leader election → Distributed Systems → Consensus
- Linearizability definitions → Distributed Systems → Consistency Models
Senior-level signal
Designing "read from replica for scale" without naming acceptable staleness and failover RPO is incomplete. State numbers: "5s lag OK for analytics, not for checkout."
Where this goes next
Horizontal Sharding — when replication of the full dataset isn't enough and you must partition rows across primaries.
Further Reading
Hands-On Tasks (Optional)
Low-setup exercises — schema drills, paper walkthroughs, or optional local installs. No autograding; the goal is interview fluency on how data is stored.
- Failover scenario with async replica15m
Leader accepts 100 writes/sec, async replica lags 5s, leader crashes. How many writes may be lost? What symptom does a user see if traffic still hits the dead leader? What is fencing? Five sentences.