Replication means keeping copies of the same data on more than one database server, usually by shipping a stream of changes from one node (the primary) to others (replicas). Interviewers probe it because it is the first step every real system takes past a single box, and because it hides the classic trap: the moment you serve reads from a copy, you have signed up for stale reads. For a Backend or AI engineer it decides your read capacity, your failover story, how much data you can lose when a machine dies, and why "I just wrote it but it is not showing up" bugs happen.
writes reads (may be stale)
App ─────────────────▶ PRIMARY ──┐ ┌──▶ REPLICA 1 ◀── App (reports)
(accepts │ change │
all ├─ stream ┼──▶ REPLICA 2 ◀── App (read API)
writes) │ (log) │
└─────────┴──▶ REPLICA 3 (standby for failover)
Primary appends every change to a log:
PostgreSQL : WAL (write-ahead log), shipped by streaming replication
MySQL : binary log (binlog), replicas keep a relay log and apply it
Replica replays the log in the same order the primary committed it.The database already writes a log before touching data pages, for crash recovery. Replication reuses that log: if a replica applies the same changes in the same order, it ends up in the same state. That is why replication is cheap to add but is fundamentally log shipping, not magic. It also explains the limits: every replica must still apply every write, so replicas add read capacity but not write capacity, and a replica's apply speed (historically often single-threaded, depending on engine and version) can fall behind a busy primary.
Physical replication (PostgreSQL streaming replication) ships low-level page changes and produces an identical copy. Logical replication (PostgreSQL logical replication, MySQL row-based binlog) ships row-level changes, which allows selective tables, cross-version replicas, and feeding other systems, at the cost of more moving parts.
ASYNC SYNC (wait for replica)
client primary replica client primary replica
|-COMMIT->| | |-COMMIT->| |
| |-flush log| | |-flush log|
|<--OK----| | | |--send--->|
| |--send--->|(later) | | |-flush/apply
| | |<---ack---|
primary crashes here: |<--OK----| |
replica may miss the last primary crashes here: replica has it
acknowledged commits. (RPO = 0), but every commit pays an
extra network round trip.The choice is about when the primary tells the client "committed". Async acknowledges after the primary's own durable write, so latency is unchanged and a slow or dead replica cannot block writers; the price is a window in which acknowledged commits exist only on the primary. Synchronous waits for at least one replica, so a primary crash cannot lose acknowledged data, but write latency now includes the network round trip, and if the synchronous replica goes down the primary can stall unless another candidate takes its place.
Real engines offer points in between. PostgreSQL's synchronous_commit ranges from off through local, remote_write, on (replica flushed the WAL) to remote_apply (replica has replayed it and reads will see it), configured with synchronous_standby_names. MySQL offers semi-synchronous replication, where the primary waits until a replica has received the change (written to its relay log) but not necessarily applied it, and in the default configuration it falls back to async after a timeout. Semi-sync narrows the data-loss window; it does not make replicas instantly readable.
t=0 t=2 t=5
Primary: bio=old bio=NEW ............... bio=NEW
Replica: bio=old bio=old (still applying) bio=NEW
^ user refreshes profile page here,
read is routed to the replica -> sees OLD bioReplication lag is the delay between a change committing on the primary and becoming visible on a replica. Causes include network latency, a replica whose CPU or disk cannot keep up, one huge transaction that must be replayed as a unit, long-running queries on the replica conflicting with replay (PostgreSQL hot standby can delay or cancel them, see max_standby_streaming_delay), and bursts of writes. Lag is not constant; it spikes precisely when the system is busiest.
Lag only matters because of how the application reads. It produces three concrete anomalies: read-your-writes violations (you save and don't see your own change), monotonic-read violations (one request hits a fresh replica, the next hits a staler one, and data appears to go back in time), and consistent-prefix violations (with multiple partitions replicated independently, a reply can be seen before the question). The standard mitigations: route a user's reads to the primary for a short window after their own write, or pin a session to one replica, or carry a "minimum log position" token and only read from a replica that has passed it, or simply send only staleness-tolerant queries (dashboards, search pages, analytics) to replicas.
1. Primary dies.
2. Detect it (heartbeat timeout; too short = false alarms, too long = downtime)
3. Choose the replica with the most log applied (least data loss).
4. Promote it to accept writes; repoint clients / VIP / DNS.
5. Old primary returns thinking it is still primary -> SPLIT BRAIN unless fenced
(kill it, block its disk/network, or demote it and rewind/resync).
Async caveat: the new primary may lack the last few acknowledged commits;
those writes are lost, or conflict when the old primary rejoins.Failover is where async replication's loss window becomes a business decision. Managed services and tools (Patroni for PostgreSQL, orchestrators for MySQL, cloud-provider failover) automate the steps, but the logic is the same: pick the most caught-up node, promote it, and make sure the old primary cannot accept writes any more. Getting the fencing wrong yields two primaries that both accept writes, a much worse outcome than downtime.
Replication is not a backup. A bad DELETE, a dropped table, or a buggy deploy that corrupts rows is replicated to every replica within moments. Backups plus point-in-time recovery (base backup plus archived WAL or binlog) protect against human and software errors; replication protects against machine failure and adds read capacity. You need both, and neither replaces the other.
# Python: simulate async replication with a 3-tick apply delay
import heapq
DELAY = 3 # ticks between primary commit and replica apply
primary, replica, pending = {}, {}, []
def write(t, k, v):
primary[k] = v
heapq.heappush(pending, (t + DELAY, k, v))
def tick(t):
while pending and pending[0][0] <= t:
_, k, v = heapq.heappop(pending)
replica[k] = v
write(-10, "profile", "old-bio")
tick(0)
write(2, "profile", "new-bio") # user edits profile at t=2
for t in range(2, 7):
tick(t)
print(f"t={t} primary={primary['profile']!r:10} replica={replica.get('profile')!r:10}"
f" {'STALE READ' if primary['profile'] != replica.get('profile') else 'in sync'}")
# Output (real, python3):
# t=2 primary='new-bio' replica='old-bio' STALE READ
# t=3 primary='new-bio' replica='old-bio' STALE READ
# t=4 primary='new-bio' replica='old-bio' STALE READ
# t=5 primary='new-bio' replica='new-bio' in sync
# t=6 primary='new-bio' replica='new-bio' in sync-- SQLite (runs as-is): the heartbeat idea used by tools like pt-heartbeat,
-- simulated with two tables. In real life a job updates the primary row every
-- second; the replica's copy of that row arrives via replication, and the
-- difference is the lag.
CREATE TABLE primary_heartbeat (id INTEGER PRIMARY KEY, written_at TEXT);
CREATE TABLE replica_heartbeat (id INTEGER PRIMARY KEY, written_at TEXT);
INSERT INTO primary_heartbeat VALUES (1, '2026-01-01 12:00:10');
INSERT INTO replica_heartbeat VALUES (1, '2026-01-01 12:00:07');
SELECT ROUND((julianday(p.written_at) - julianday(r.written_at)) * 86400) AS lag_seconds
FROM primary_heartbeat p JOIN replica_heartbeat r ON r.id = p.id;
-- lag_seconds = 3.0-- PostgreSQL (needs a real primary + standby; outputs not shown, they depend on your cluster)
-- On the primary: per-standby state and lag
SELECT application_name, state, sync_state, write_lag, flush_lag, replay_lag
FROM pg_stat_replication;
-- sync_state is 'async', 'sync' or 'potential' depending on synchronous_standby_names.
-- On a standby: is this node a replica, and how old is the last replayed transaction?
SELECT pg_is_in_recovery();
SELECT now() - pg_last_xact_replay_timestamp() AS approx_lag;
-- (this overstates lag when the primary is idle, since no new transactions arrive)
-- MySQL 8.0.22+: on a replica
-- SHOW REPLICA STATUS\G -- look at Seconds_Behind_Source and the IO/SQL thread stateQ: Replication vs sharding?
A: Replication puts full copies of the same data on several nodes, which buys read scaling, availability and failover but does not scale writes or total data size, because every node holds and applies everything. Sharding splits the dataset so each node owns a subset, which scales writes and storage but not by itself availability, and it complicates cross-shard queries and transactions. They solve different problems and are combined in practice: each shard is itself a replicated primary plus replicas.
Q: Synchronous vs asynchronous replication?
A: In synchronous replication the primary waits for a replica's acknowledgement before confirming commit, so acknowledged data survives a primary crash, at the cost of extra write latency and stalled writes if the synchronous replica is unreachable. In asynchronous replication the primary confirms after its own durable write, which is fast and isolated from replica problems, but replicas lag and a failover can lose the most recent commits. Many systems choose a middle path: one synchronous or semi-synchronous replica for durability and the rest asynchronous for read scaling.
Q: What is replication lag?
A: It is the time (or log distance) between a change committing on the primary and becoming visible on a replica. It arises from network delay, an overloaded or slow replica, large transactions and replay conflicts, and it grows under write bursts. The consequence is stale reads, so the application must either tolerate them, read from the primary for data it just wrote, or use session pinning or log-position tokens.
Q: What is a read replica?
A: A read-only copy of the primary that stays current by applying its change log and serves SELECT traffic, typically for read-heavy APIs, reporting and analytics. It offloads the primary and is a natural failover candidate, but it is generally slightly stale and adds no write capacity. Query routing must be lag-aware, and queries that feed a write decision should run on the primary.
Follow-up: A user updates their profile and immediately sees the old value. How do you fix it without sending every read to the primary?
A: This is a read-your-writes violation from lag. Options: for a short window after a user's own write (a few seconds, or until the replica passes the write's log position) route that user's reads to the primary; or return a position token (PostgreSQL LSN, MySQL GTID) and only read from a replica that has reached it; or update the client's local state optimistically. The trade-off is added routing complexity and extra primary load in exchange for a consistent user experience.
Follow-up: With async replication, the primary dies. What can go wrong when you promote a replica?
A: The replica may be missing commits the primary had already acknowledged, so those are lost, and clients may have seen data that no longer exists. If the old primary comes back still believing it is primary, you have split brain with two writers, so it must be fenced or demoted and resynced. Semi-sync or sync replication shrinks or removes the loss window but adds latency and can reduce availability.
Follow-up: Can I treat a replica as my backup?
A: No. Replication faithfully copies mistakes: an accidental DROP TABLE or a bad bulk UPDATE reaches the replicas almost immediately. A deliberately delayed replica gives a limited recovery window, but real protection is periodic base backups plus archived WAL/binlog for point-in-time recovery, tested by actually restoring them.
In almost any read-heavy design (feeds, product catalogs, profile services) the first scaling move is one primary plus N read replicas behind a lag-aware router, with a cache in front. The design questions to volunteer are: which reads tolerate staleness (a feed, a product listing) and which do not (checking stock or a balance before writing), how the app avoids read-your-writes surprises, what the recovery point objective is (which decides sync, semi-sync or async), and how failover is automated and fenced. Multi-region deployments push the same trade-off to extremes: cross-region synchronous replication costs tens to hundreds of milliseconds per commit, so most systems replicate across regions asynchronously and accept a small loss window, or use consensus-based replicated stores (such as Raft-based databases) that pay a quorum round trip per write. When write volume, not read volume, is the bottleneck, replication is the wrong tool and partitioning (sharding) is the next step.
Replication trades consistency and operational simplicity for read throughput and availability. Async gives low latency and independence from replica health but accepts stale reads and a data-loss window on failover; synchronous closes the loss window but makes every commit pay a network round trip and couples primary availability to replica health. Adding replicas raises read capacity linearly only while the primary can still stream to all of them, and it does nothing for write throughput or dataset size. It is the wrong choice when the problem is write scaling, when the workload cannot tolerate any stale read and cannot be pinned to the primary (then you need stronger, more expensive coordination), or when someone expects it to replace backups. Keep it simple until you must: a single well-tuned primary with good backups serves a surprising range of workloads.