↓ Skip to main content

Database Replication: Why the Replica Lost Your Order

·1750 words·9 mins
System Design System-Design Distributed-Systems Postgres Mysql
Table of Contents

You add a read replica to take load off the database. A user places an order, the page refreshes, and the order is gone. It isn’t lost: the write landed on the leader, and the refresh read a replica that has not copied the data yet. Four terms explain it:

  • Leader-follower replication: one server, the leader (also called primary), takes every write. Followers (replicas) copy the leader’s change log and replay it in order. Postgres calls the log the WAL (streaming replication); MySQL uses the binlog.
  • Asynchronous replication: the default in Postgres and MySQL. The leader commits and answers the user before any follower has the change.
  • Replication lag: the gap between a write committing on the leader and a follower having replayed it. Usually milliseconds, but heavy writes, or a slow query on the replica, can stretch it to seconds.
  • Synchronous replication: the leader waits for a follower to confirm each write before answering. Nothing acknowledged is lost on failover, but every write gets slower.

A read replica is not a live copy: with async replication it shows the database as it was a moment ago, so a user who writes and then reads from a replica can miss their own change. So route each read by how stale it may be. I’ll call that staleness routing. Product pages and feeds can be a second old, so they go to replicas. A user reading back what they just saved goes to the leader. Lagging replicas leave the read pool. Synchronous replication is for data you can’t lose, like payments, and every write pays for it.

The whiteboard next door
#

A teacher writes on the main whiteboard while helpers copy each line onto boards in other rooms. You add your name and walk next door. It isn’t there yet. If you take the main board as the leader, the boards in the other rooms are the followers. The helpers copying it line by line are those followers replaying the change log, in the same order the teacher wrote it. The time between writing your name on the main board and seeing it next door is the replication lag.

The Postgres manual describes the same gap. With streaming replication there is “a small delay between committing a transaction in the primary and the changes becoming visible in the standby”, “typically under one second assuming the standby is powerful enough to keep up with the load”. Under a second sounds harmless, but the redirect after a form post comes back in milliseconds.

Following one order
#

Here’s the order from the opening. The leader commits #1042 and says OK straight away. The change is still on its way to the replica.

sequenceDiagram
  participant A as App
  participant L as Leader
  participant R as Replica 1
  A->>L: INSERT order #1042
  L->>L: commit, write to WAL / binlog
  L-->>A: OK (async: no follower has it yet)
  L-)R: stream log record for #1042
  A->>R: SELECT order #1042
  R-->>A: not found (still replaying)
  R->>R: replay #1042
  A->>R: SELECT order #1042
  R-->>A: #1042

Figure 1: The OK goes back before the log record reaches the replica. Any read that lands in between misses the order.

In the animation, watch the number in each box: the latest order that copy holds.

Async replication: the replica runs behindwrite to the leader, read from Replica 1

Figure 2: The OK reaches the app long before the log reaches Replica 1, so the first read comes back empty. Nothing is lost. The second read, after replay, finds the order.

The lag counter is illustrative and the clip is slowed down.1 In a healthy setup that 0.9 s is usually a few milliseconds, but the order is the same: acknowledged first, copied second.

Routing reads by staleness
#

Staleness routing starts with one question for each read: how old may this answer be?

  • Can be a second or more old: product pages, feeds, search results, yesterday’s sales report. Send these to replicas; that’s usually where the read load is. The vertical vs horizontal scaling note calls read replicas the first step once the shared database becomes the limit.
  • Must include the user’s own change: the order page right after checkout, a profile right after an edit. This is read-your-writes. The simple fix: after a user saves, read that user’s data from the leader for a few seconds. A “last write at” timestamp in the session is enough to route on.
  • Must be current for everyone: an inventory check before payment, a balance before a withdrawal. Always the leader.

The leader-read window has to be longer than your worst normal lag, so measure lag first. A tighter Postgres option is to remember the WAL position of the user’s write and only use a replica whose pg_last_wal_replay_lsn() has passed it. That’s more code, so I’d start with the timer.

Failover: where async lag turns into data loss
#

Stale reads are annoying; failover is where async lag costs data. When the leader dies and a follower is promoted, writes the old leader acknowledged but hadn’t shipped aren’t on the new one. Postgres puts it plainly: “The amount of data loss is proportional to the replication delay at the time of failover.”

Async failover strands writesleader acknowledged #1043 and #1044, follower only has #1042

Figure 3: The orders were acknowledged, so the users think they’re saved. After promotion they exist only on the old leader, and someone has to merge them back by hand.

That’s what happened to GitHub on 21 October 2018. Connectivity between a network hub and their primary US East Coast data center was lost and “restored in 43 seconds”. In that window their failover tool, orchestrator, had started moving MySQL leadership, and the West Coast nodes began “failing over clusters to direct writes to the US West Coast data center”. The East Coast leaders held a short stretch of writes that “had not been replicated to the US West Coast facility”. GitHub chose to “prioritize data integrity over site usability and time to recovery”, and the incident “resulted in degraded service for 24 hours and 11 minutes.”

So 43 seconds of network trouble cost about a day. The lag was small; the cost was two leaders with different histories, reconciled by hand.

The case for sending every read to a replica
#

The strongest view against staleness routing: lag is usually milliseconds, and routing reads by type adds code paths, session state and a leader that can still get overloaded. So send every read to replicas and accept a rare stale page.

For most read traffic, that’s right. A feed a second old is fine, and one read pool is simpler to run. It breaks on the pages users check right after saving, like checkout and profile edits, because those reads arrive within milliseconds of the write. The fix is cheap too. In my experience those pages are a small share of traffic, but measure that share before you rely on it.

The opposite view, “make it all synchronous”, fixes data loss, not stale reads. By default Postgres’s synchronous mode waits for the standby to flush the change to disk, not replay it, so a read there can still miss it.2

Failure modes of staleness routing
#

Staleness routing assumes the lag stays small and that you’ll notice when it doesn’t. Here’s how each part fails:

  • Lag spikes past the leader-read window. A batch job or write burst puts replicas seconds behind, and a 2-second window starts handing out stale reads. A long query on a Postgres standby can pause replay too, up to max_standby_streaming_delay, after which Postgres cancels the query.
  • The lag metric lies. MySQL’s Seconds_Behind_Source compares the replica’s applier with its receiver, so on a slow network it “often shows a value of 0, even if the replication receiver thread is late”. The manual’s verdict: “this column is useful only for fast networks”. On Postgres, pg_stat_replication.replay_lag reverts to NULL on an idle, caught-up system, so treat NULL as “no data”, not as a fault.
  • Reads bounce between replicas. If a user’s requests hit replicas with different lag, a page can show the order and then lose it on refresh. Pinning a user to one replica helps.
  • The leader drowns in pinned reads. If most traffic writes and then reads, the window sends most reads back to the leader and the replicas stop helping.
  • Sync replication stalls writes. Commits “may never be completed if any one of the synchronous standbys should crash”, unless you list spare standbys. It suits a payments table better than a whole database.

What to measure
#

  • Replication lag per replica: replay_lag in pg_stat_replication (Postgres), Seconds_Behind_Source in SHOW REPLICA STATUS (MySQL). Alert on it, and pull a replica from the read pool when it passes your leader-read window.
  • The p99 of that lag, not the average. The window has to cover the bad minutes.
  • Leader read share: reads sent to the leader by the window. If it climbs, the replicas are doing less than you think.
  • Write latency before and after turning on synchronous replication, especially cross-region.

If queries are slow on the leader too, check the indexes first. A replica copies the slow query along with the data.

The takeaway
#

A replica shows the database as it was a moment ago, and staleness routing plans for that. Ask of each read how old the answer may be. Send the ones that can be a little old to replicas, and a user’s reads to the leader for a few seconds after they save. Watch p99 lag and pull a replica from the pool once it falls behind your window. Use synchronous replication only where losing an acknowledged write would really hurt, and expect each write there to wait one round trip.

References
#

Incident reports

Documentation

  1. The 0.9 s in the animation is illustrative, not measured. Use your own lag numbers. ↩︎

  2. Postgres has a stricter level, synchronous_commit = remote_apply, which waits for the standby to apply the change. The manual’s description of replay_lag mentions it as the delay that level adds to each commit. ↩︎

Amar Singh
Author
Amar Singh
I build stuff

Related