System Design Data
Database Replication
Checkout shows "Paid." A moment later the shopper refreshes. If a lagging answered, the order still looks open. The write succeeded on the ; the copy that the refresh read had not caught up yet.
That is the whole problem this page solves. keeps the same logical data on more than one node so a failure or a flood of reads cannot crush a single machine. The craft is choosing how tightly copies must agree before you tell the user "done," and routing the next read so still holds.
Checkout write journey
Primary
The primary commits the paid status and records the change log.
Ack policy
Sync, async, or semi-sync decides when the client is told the write is done.
Replicas
Replicas apply the log and may trail the primary by milliseconds or more.
Read-your-writes
Route the shopper's next read carefully, or the paid order looks unpaid again.
What actually happens
The checkout button feels like one action. Underneath, the writes first: it commits the new status, then streams a change-log record to followers. Those replay the log on their own clocks. If the next read lands on a copy that has not applied that record yet, the user sees the old status. That gap is (how far a follower trails the writer, in time, bytes, or log position).
When the client is told "done" is a separate decision. replication waits for a configured remote durability or replay condition before acknowledging. returns after the primary commits (faster; , how much data you can afford to lose, can be greater than zero). is an engine-specific policy whose receipt, replay, and timeout behavior must be checked. Mature fleets combine modes with monitoring and drills (promoting a healthy copy when the writer dies).
That is why interviews ask about , monotonic reads (never seeing time go backward across two reads), and math (enough overlapping copies that a read hits a recent write), not just whether a replica box exists on the diagram.
Try it: when is a replicated write really visible?
Follow one writer and one standby. Each write produces the next version of one record. Shipping stores its log record durably on the standby; replay applies that record to the queryable database. These are separate steps. Compare which step must finish before the client receives success, then choose where the client reads.
Commit record stored locally; remote acknowledgement may still be pending
Log survives restart, but may still need replay
New replica queries can see this version
- Last client success
- v0
- Client waiting for
- Nothing
- Acknowledged writes absent from standby log
- 0
Both databases start at v0. Write a new value, then move its log record through the replica.
Reproduce a disappearing edit. In asynchronous mode, write v1 and read the replica before shipping anything. The write succeeded, but the read returns v0. Shipping alone still leaves v0 visible; replay finally makes v1 readable. Reading from the primary avoids this replica lag. The session-aware option instead compares the replica’s applied position with the position recorded after your successful write and falls back when necessary.
Compare the two synchronous promises. Change to “replica durable flush,” write, then ship. Success arrives while replica queries can still see v0. Change to “replica applied” and repeat: the client waits until replay as well. The latter promise costs the network round trip, durable storage, and replay delay. Pausing the link makes that availability trade-off visible: the required remote step cannot finish, so acknowledgement waits.
Separate recovery from ordinary reads. If the primary is permanently lost, acknowledged writes missing from the standby’s log cannot be recovered from that standby. Records already stored but not yet applied are different: replay can recover them before promotion. A production failover must choose a suitable replica, replay required records, and fence the previous primary so two writers cannot accept conflicting changes. An acknowledgement policy alone does not perform any of those tasks.
Model scope: one required standby, ordered records, one outstanding client operation, fresh query snapshots, and reliable client acknowledgements. Reads start after the write result; visibility to other primary sessions during a pending commit is outside the model. A timed-out write may still have committed; a retry needs an operation identity. These modes illustrate PostgreSQL’s local commit, synchronous_commit=on with a configured synchronous standby, and remote_apply. Other replicas may lag even when the required standby has caught up. PostgreSQL documents the exact acknowledgement and visibility guarantees.
How to choose a replication mode
The checkout story above is a freshness bug. The same copies also decide what you lose in a crash. Three questions pick the mode before you draw a topology.
1. What is the ? Money and inventory usually need stronger durability. Feeds and analytics dashboards often tolerate seconds of .
2. Can users tolerate stale reads? After a write, route paths to the or a caught-up replica. Public history can use lag-aware followers.
3. What is the ? Automate , fence the old writer so it cannot become a second primary, and rehearse promotion before the outage teaches you.
Simple rule of thumb
Start with single-leader replication for OLTP (online transaction processing: the checkout-style writes). Tighten with or where finance demands it. Use multi-leader only when you truly need regional writes and can own conflicts.
In simple terms: replicas scale reads and survive failures. They do not multiply one primary's write capacity.
From problem to mechanism
Here is the paid-order refresh again, slowed down so you can see where "we have replicas" fails and what the ack policy actually changes.
Problem. The shopper writes paid and immediately reads. Copies trail the . Product also wants the acknowledged write to survive a crash.
Naive system. Run replication, ack after the primary commits its WAL (write-ahead log, the durable change record), and treat any replica as a safe read target. Nobody measures , and is unrehearsed.
Break it. The primary disk dies before the log ships. Failover promotes a follower that never saw paid. The user refreshes and support sees the order unpaid, even though the client already got an ack. Rollback cannot invent a copy that was never sent.
Mechanism. Pick the ack policy that matches the stated : or when loss of a recent ack is unacceptable; async when it is. Route away from a lagging replica. On promotion, choose the most caught-up copy and fence the old writer. The write-path diagram and Numbers to know below make each of those checks concrete.
What must never become false
Every acknowledged write must be recoverable under the stated RPO and failure model. The selected promotion target must contain the required durable history; a mode name alone is insufficient. Critical read-your-writes paths must enforce a minimum applied position or use the current primary. Fence the old writer before enabling writes on any replacement, including one whose replication was fully caught up.
Write path and replica lag
Track three distinct stages: a replica receives log bytes, flushes them durably, and applies them so queries can see the change. An acknowledgment may wait for one of these stages depending on configuration. A replica can therefore hold a durable log record while still returning an older query result. State which position a read requires and avoid treating “replicated” as a single unambiguous milestone.
The mechanism above names the ack and the read route. This sequence is that same checkout write, message by message, including the refresh that lands too early.
Read the diagram definition
sequenceDiagram
participant Client
participant Primary
participant ReplicaA as Replica A
participant ReplicaB as Replica B
Client->>Primary: Write order status = paid
Primary->>Primary: Commit to WAL (Write-Ahead Log) or change log
Primary-->>Client: Ack write
Primary-->>ReplicaA: Stream log record
Primary-->>ReplicaB: Stream log record
Client->>ReplicaB: Immediate read
ReplicaB-->>Client: Old status until replay catches up
ReplicaB->>ReplicaB: Apply log laterLag units
Track bytes behind, seconds behind, replay LSN/GTID gap (log sequence number or global transaction id), and apply queue age. "Replica exists" is not enough.
User impact
Read-after-write breaks, counters go backward, dashboards disagree, and failover can lose acknowledged async writes.
Common guardrails
Primary reads after writes, session stickiness, lag-aware load balancing, monotonic tokens, and alerting before replicas fall minutes behind.
Sync, async, and topologies
The sequence above acks as soon as the primary commits. That is one mode. The same log can wait for copies, and those copies can be arranged as a single leader, several leaders, a quorum, or a chain.
Synchronous
Write waits for all required replicas to acknowledge. Zero data loss (RPO=0) is possible inside the failure model, but write latency rises.
Asynchronous
Write returns immediately after primary commit. Replica catches up in background. Low latency. Risk: data loss on primary crash before replicas receive the log (RPO > 0).
Semi-Synchronous
Write waits for at least one replica to acknowledge before returning (exact guarantees depend on engine and sync mode). Lower loss risk than fire-and-forget async with extra latency.
Where the copies sit
Single-Leader (Primary-Replica)
Most common. Simple, strong consistency. Bottleneck: single write leader and failover discipline.
Multi-Leader (Active-Active)
Multiple writers for low latency globally. Conflict resolution is required: timestamps, merge functions, or convergent data types.
Leaderless (Quorum)
W + R > N makes read and write quorums overlap. Highly available, but clients must own conflict and repair behavior.
Chain Replication
Strong consistency with high read throughput. Reads usually come from the tail; variants allow reads from other nodes with version checks.
Numbers to know
Modes and topologies decide the shape. These figures are the budget you quote when someone says replicas will "just" absorb the load. Later sections point back here instead of restating the arithmetic.
Quorum formula
For three fixed replicas, writing to two and reading two guarantees that the chosen sets intersect. That intersection is useful only with rules for selecting versions and handling concurrent or incomplete writes. The inequality alone does not establish linearizability: sloppy quorums, changing membership, stale coordinators, or last-write-wins timestamps can weaken the result. Distinguish quorum overlap from a consensus protocol that orders committed operations.
Let N be replica count, W writes required for acknowledgment, R reads required per query. For reads to intersect fresh writes, aim for W + R > N: every read quorum overlaps every write quorum on at least one replica.
| Config | N | W | R | Properties |
|---|---|---|---|---|
| Strong consistency | 3 | 2 | 2 | W+R=4 > 3. Tolerates 1 failure. |
| Read-heavy | 3 | 3 | 1 | Fastest reads, slowest writes. |
| Write-heavy | 3 | 1 | 3 | Fastest writes, slowest reads. Eventual consistency possible. |
| High availability | 5 | 3 | 3 | Tolerates 2 failures. Higher storage cost. |
1K write QPS
Primary
Replicate log bytes
Replica 1
Read pool A
Replica 2
Read pool B
Replica lag budget
| Dimension | Example math | Decision |
|---|---|---|
| Storage | 500 GB primary x 3 replicas = 1.5 TB before backups and retained logs | Budget replicas as full copies, including indexes. |
| Write fan-out | 2 MB/s commit log x 2 followers = 4 MB/s replication stream | Lag appears when followers apply slower than the primary generates logs. |
| Read QPS | Primary 2K reads + 2 replicas at 2K reads each = ~6K total if queries are replica-safe | Only stale-tolerant reads should use replicas by default. |
| Consistency lag | If lag p99 is 2s, recent writes need primary reads for at least that window | Use session stickiness, version tokens, or primary routing after writes. |
| Budget item | Example calculation | Why it matters |
|---|---|---|
| Retained logs | 2 MB/s log x 24h = ~173 GB retained on primary; x 2 followers = 4 MB/s shipped | Retention must cover replica downtime and rebuild windows. |
| Apply throughput | Primary generates 80 MB/s, replica applies 60 MB/s during peak | Lag grows by 20 MB/s until traffic drops or replica capacity improves. |
| Rebuild time | 1.5 TB copy / 150 MB/s effective restore = ~2.8 hours before catch-up | A failed replica is not back just because a new node starts. |
| Backups | 500 GB primary + indexes, 3 replicas, 7 daily backups | Backups and logs often cost more than the visible primary dataset. |
| Read type | Freshness need | Route |
|---|---|---|
| Own profile after edit | Must show latest write | Primary or session token that guarantees the replica caught up |
| Payment or order state | Fresh and monotonic | Primary or quorum read path |
| Public feed / history | Seconds of lag acceptable | Lag-aware replicas |
| Permissions | Stale read can be a security bug | Primary or strongly consistent authorization cache |
| Guarantee | Approach | Latency Cost |
|---|---|---|
| Read-your-writes | Read from primary for N seconds after write | ~0ms (same DC) to ~50ms (cross-DC) |
| Monotonic reads | Session affinity / sticky routing | 0ms (routing only) |
| Linearizability | Consensus quorum | +100-300ms (cross-DC quorum) |
| Causal consistency | Vector clocks / logical timestamps | ~0ms (metadata overhead) |
Cross-datacenter round trips dominate interactive sync: US East ↔ US West ~60ms, US ↔ Europe ~80ms, US ↔ Asia ~150ms. That is why continental sync is rare for checkout writes.
stored_bytes = (table + indexes) x replica_countlog_MB/s x followers x retention_hoursfor bandwidth and retained log planning- Rebuild time after replica loss ≈ bytes to copy / safe restore bandwidth
| Read type | Route |
|---|---|
| Own profile after update | Primary or quorum |
| Public feed / dashboards | Lag-aware replica OK |
| Payment / inventory status | Primary unless proven safe |
Hot rows
Replicas do not increase write throughput for one hot counter. Split counters, buffer increments, or move to append events.
Write amplification
Every indexed write becomes table work, index work, log work, network replication, and follower apply work.
Failover
Choose the most caught-up replica, fence the old writer, and verify clients do not read divergent histories.
Lag symptoms, engines, and failover
Numbers to know above size the copies. Users notice the same budget as three consistency bugs, then as engine knobs, then as a promotion you have to get right.
Lag and consistency symptoms
Read-your-writes is a session promise: after saving, the same user should not immediately see the old state. One approach returns a commit position and sends later reads only to a replica that has applied at least that position; another routes those reads to the leader. A fixed “read primary for two seconds” window is a heuristic unless lag is strictly bounded. Measure lag in meaningful positions as well as elapsed time.
Engine-specific mechanisms
Physical and logical replication
Physical replication reproduces storage-level changes and is closely tied to the engine’s format. Logical replication describes row or logical changes and can feed a different schema, version, or derived store, but consumers must handle ordering, schema evolution, and replay. Neither replaces backups: an accidental delete can replicate successfully to every copy. Backups and point-in-time recovery protect against unwanted changes that replicas faithfully repeat.
- WAL shipping
- Physical replication sends storage-level recovery records to a compatible standby. The standby replays those records to reproduce the database state, so receipt of the log and visibility of the resulting rows remain separate positions.
- Logical replication
- A publication describes selected logical changes for a subscriber to apply. This can support selected-table replication and version migrations, but schema compatibility, unsupported objects, and cutover correctness still need an explicit plan.
- Synchronous commit
- With synchronous standbys configured, PostgreSQL's
synchronous_commitsetting chooses an acknowledgement boundary:onwaits for the required remote durability,remote_applyalso waits for replay, andremote_writewaits for the remote operating system to receive the write without requiring its durable flush. Those boundaries imply different read visibility and failure guarantees. - Distributed SQL
- A distributed SQL system can combine partitioned data with replicated partitions and transaction coordination. Replication protects each partition; queries and transactions that cross partitions add separate coordination and latency costs, which the SQL interface does not remove.
Event-log replication
Cross-datacenter replication
Round-trip times are in Numbers to know above. Those delays are why the three layouts below are the usual answers, not a single global sync commit.
Failover and conflicts
Promotion must prevent the former leader from continuing to accept authoritative writes. A network-isolated leader can still be alive and reachable by some clients, so merely declaring a new leader creates a split-brain risk. Use the storage system’s supported election and fencing mechanism, route clients to the current term or owner, and ensure the selected replica meets the durability policy. Rejoining replicas must reconcile with the new committed history.
Automatic Failover
View Failover Steps
- Health checks detect primary failure (for example several missed heartbeats).
- Remaining nodes reach consensus that the old primary is unreachable.
- Choose the replica with the smallest replication lag / highest GTID.
- Promote it and repoint DNS, proxies, or discovery metadata.
- Fence the old primary (STONITH or revoke write routes) so it cannot resume as a second writer.
Tools: use a failover controller, topology manager, or cache sentinel suited to your engine.
Conflict Resolution (Multi-Leader)
Failure timeline, and what still breaks after the fix
Putting the pieces together, here is the async ack then primary crash from the mechanism, minute by minute, then the ways an improved design can still go wrong.
Timeline: async ack then primary crash
t=0 Client writes order=paid; primary commits WAL; async replicas not yet applied t=1 Primary acks client (RPO > 0 by design) t=2 Primary disk dies before ship to replicas t=3 Failover promotes replica; order still shows unpaid t=4 User refreshes → stale; support sees "paid" missing t=5 With semi-sync/sync or quorum ack: promote only after durable copy exists Outcome "We have replicas" ≠ "acked writes survived" under async replication
Still fails when…
- A lagging replica is promoted and drops acks the old primary already accepted.
- A hot counter stays hot because replicas do not raise one writer's capacity.
- Skipping the fence leaves two writers and a split-brain history.
Trade-offs
- Async keeps write latency low; a primary crash can lose recent acknowledgements.
- Sync or semi-sync aims at near-zero RPO and pays in commit latency.
- More replicas add read capacity and storage cost, plus apply lag to watch.
Choosing and explaining read replicas and consistency lag
A complete explanation connects the mechanism to a requirement. Describe what improves when you introduce it, which new failure cases become possible, and how you would know that the trade-off is worthwhile.
Explain the core idea
Replicas scale reads and improve availability, but the primary still owns write throughput in single-leader designs.
I will route read-after-write and money-grade reads to the primary or quorum path, then use replicas for stale-tolerant reads.
I will monitor lag per replica and shard, not just average lag.
When it belongs
The workload has many profile, feed, dashboard, or history reads.
The design needs failover, disaster recovery, or lower read latency.
The interviewer asks how a million-user database handles read QPS growth.
How it fails
Users write then immediately read stale data from a follower.
A lagging replica is promoted and loses acknowledged async writes.
A hot row remains hot because replicas do not multiply primary write capacity.
Trade-offs to weigh
Async replication gives low write latency with lag risk; sync replication gives stronger durability with higher commit latency.
More replicas add read capacity and storage cost, but also more apply lag and operational surface.
Replica reads are cheap only when the product can tolerate staleness or route around it.
Common misunderstandings
Do not say replicas solve write scaling for a single primary.
Do not ignore index storage; every replica stores the table and indexes.
Do not promote a replica without checking lag and fencing the old primary.
Wrap-up
You have seen the failure, the mechanism, and the operational signals. Use these questions and exercises to check the ideas stuck, then follow the references for the primary sources.
How to explain this in an interview
Questions worth asking
- What are the and targets for this dataset?
- Can users tolerate stale reads, and for how many seconds?
- Do we need read-your-writes, monotonic reads, or full linearizability?
- Are writes local to one region or accepted in many regions?
Metrics to monitor
- Replication lag by replica and shard, not just a fleet average.
- Replica apply throughput, log shipping errors, and failover duration after a promote.
- Stale-read rate from app traces and post-write read routing.
- Conflict count on multi-leader or leaderless systems, because silent merges hide loss.
Strong answer: “I'll route critical reads to the primary or quorum path, let dashboards and feeds use lag-aware replicas, and alert on lag before failover turns into data loss.”
Exercises
- Product needs RPO ≈ 0 for payments and can tolerate 2s lag on feeds. Choose sync vs async per path and how you route read-your-writes.
- Lag p99 is 2s. A user updates a profile then immediately reads. Design routing so they never see the old bio. What metric pages before failover?
- Primary dies with 30s unreplicated async log. Walk promotion, fencing, and what you tell users about possibly missing acks.
Topic-specific references
- Martin Kleppmann: Designing Data-Intensive Applications, Ch. 5 (Replication).
- PostgreSQL: synchronous_commit and the acknowledgement guarantees of each mode.
- MySQL: semisynchronous receipt acknowledgements, timeout fallback, and failover implications.
Key takeaway
Single-leader replication is the common starting point for OLTP. Tighten durability with or sync replicas where finance or compliance demands it. Multi-leader pays off only when you truly need local writes in several regions and can own conflicts. For leaderless stores, reason about W + R > N and monitor ; automate and rehearse it.
Practice and further reading
Use these exercises to connect the lesson to a design decision. State your assumptions, trace what happens to one request, and explain the rule that must remain true even during a failure. That rule is the design's correctness invariant.
Focus for this lesson: Operate primary/replica or multi-leader data with explicit lag contracts, read-your-writes, promotion fencing, recovery, and CDC boundaries.
Explain the decision
How do you give a user read-your-writes with asynchronous replicas?
Read the reasoning
Route to the primary for a bounded session, carry a commit position and wait/select a caught-up replica, or update the client optimistically.
Apply it to a production scenario
The old primary returns after failover and accepts writes. What prevents split brain?
Read one approach
A new epoch/fencing token, storage or orchestration enforcement, revoked credentials/routes, and reconciliation before rejoining.
Work through the numbers
At 30,000 writes/s averaging 2 KiB WAL each, estimate raw replication bandwidth to two replicas.
Read the worked calculation
About 117 MiB/s total before compression, protocol, indexes, and retransmission.
Reason through a failure
What must a failover drill measure?
Read the recovery reasoning
Actual RPO, RTO, promotion safety, client reconnection, replica divergence, failback steps, and proof that no acknowledged write vanished.
Sources and further study
Continue with Database Sharding.