Replication (DB)
Copying data to other nodes: statement vs row, sync vs async, failover.
Level: advanced
What is it?
Database replication copies data or change records from a source node to one or more replicas. Systems differ in whether they replicate statements, row changes, physical log records, or logical changes, and whether acknowledgements wait for replicas synchronously or proceed asynchronously.
Why does it matter?
Replication can improve read capacity, availability, recovery options, and geographic locality, but adds lag, failover complexity, conflict semantics, and operational cost. A replica is not automatically a backup or a consistency guarantee.
Mental model
Think of replication as distributing the ordered change history. A primary accepts writes and publishes changes; replicas apply them later or as part of a synchronous commit contract, depending on configuration.
How it works
- Synchronous replication waits for a defined replica acknowledgement before the primary reports commit, under the configured durability contract.
- Asynchronous replication lets the primary acknowledge before all replicas apply the change, creating a lag and possible loss window on failover.
- Read replicas may return stale data unless the application enforces a read-your-writes or consistency strategy.
- Failover requires deciding which node is authoritative and fencing the old leader to prevent split-brain.
- Backups protect against some deletion/corruption scenarios that replication may faithfully copy to every node.
Steps
- A transaction commits on the primary and produces log/change records.
- The replica receives the stream and applies the changes in order or according to its replication model.
- Monitoring measures lag between source progress and replica replay.
- During failover, the system promotes a sufficiently current node and prevents the former primary from accepting conflicting writes.
Real-world usage
- Scaling read-heavy applications.
- Designing disaster recovery and RPO/RTO targets.
- Diagnosing replication lag and stale reads.
Common mistakes
- Calling a replica a backup.
- Failing over without fencing the previous primary.
- Assuming a read replica always includes a transaction that the same user just committed.
Interview questions
What is the main trade-off of asynchronous replication?
The primary can acknowledge writes with lower latency, but a failover before replicas catch up can lose recently acknowledged changes.
Why can replication spread an accidental DELETE?
Replication faithfully propagates committed changes, so backups, retention, and recovery procedures are needed for accidental or malicious changes.