Replication Lag: When Your Own Write Disappears

Nikhil read three novels over a long weekend in September and wrote a review of each one on the Sunday evening. Two of them he wrote twice.

Marlow Books is a four person online bookshop that exists only in this course. Posting a review there works the way it works nearly everywhere. The form posts to the server, the server writes a row, the server sends back a redirect, the browser fetches the book page again, and your review is sitting at the top of it. His first review went up. The second one posted, the page came back, and the review was not on it, so he wrote it again. The third did exactly the same thing.

The email arrived on Monday morning: your site ate my review twice and then printed both copies of it.

The founder opened the replication lag graph, which the monitoring had been drawing since June, when a second database machine went in behind box A, the one the shop's Postgres has always run on. The graph was a flat line at six milliseconds, all through Sunday evening.

Six milliseconds, said the graph. Somewhere inside six thousandths of a second, a customer had watched his own writing vanish, twice in one evening.

The graph was right. Nikhil was right. Everything worth knowing about replication lag lives in the space between those two facts.

The race nobody tells you you are running

The promise Nikhil thought he had is one with a name. Read-your-own-write means that once a write of yours has been accepted, every later read by you sees it. Not by everybody. By you. It is a small promise as these things go, and the one people miss hardest when it is gone, because somebody who has just typed four hundred words about a novel has a very exact expectation about the next screen.

Lesson 011 kept saying a replica is behind and kept handing the number to today. The reason the question is harder than it sounds is that "how much" is only half of it.

Here is what happened to Nikhil's second review, in real times, on a fibre connection in Mumbai about twelve milliseconds from the shop.

20:41:07.000  POST commits on box A, the primary
20:41:07.002  the redirect leaves the server
20:41:07.008  the redirect reaches the browser
20:41:07.009  the browser sends the GET
20:41:07.015  the GET reaches the app, which reads the replica
20:41:07.023  the review lands on the replica

The review arrived eight milliseconds after the page that should have shown it had already been built. The write took twenty three milliseconds to reach the copy; the customer came back in fifteen.

Call that second number the gap: the time between a write committing and the same person's next read arriving. It is the redirect's trip out plus the request's trip back, so roughly one round trip to the customer, plus whatever the browser and the server spend thinking. Marlow measured it across every review post: a median of 45 milliseconds, and a tenth percentile of 15, which is customers on fibre in the same city as box A.

One thing that timeline assumes needs saying out loud, because it is the whole reason Nikhil has a problem. The review list cannot sit in the day long cached shell lesson 009 built, for 009's own reason: an entry lives at the rate of its fastest changing ingredient. So it is read fresh on every book page, and in June it went to the replica along with everything else on that page. It is not one of the rows in lesson 011's routing audit. Nobody argued about it, which is generally true of the routing decisions that go on to hurt you.

Now put the two distributions side by side. Lag, sampled every ten seconds for a week:

The failure rate is not the size of the lag. It is the share of the lag distribution sitting above your gap. Against a 45 millisecond gap, about three review posts in a hundred came back without the review on the page. Against Nikhil's fifteen, closer to one in five.

He wasn't unlucky. He was on fibre, in the same city as the shop, which made him the worst served customer Marlow had.

Sit with that for a moment, because it inverts everything else you will ever chase. Every other performance problem punishes the customer on the bad connection. This one punishes the customer on the good one, because the only thing rescuing the rest of them is a network slow enough for the copy to catch up. Make your site faster and this bug gets worse.

And the median cannot see any of it. A graph drawn from a ten second sample, plotted as a line at six milliseconds, is a true statement about a distribution that reaches a second every morning and twenty four minutes once a month. You cannot find a read-your-own-write failure with an average. There is no value of the median that means the race is won.

In November, wondering how long it had been going on, the founder counted. Reviews come in at about four hundred a week and the replica went in during June, so the fifteen weeks up to Nikhil's Monday hold about six thousand posts. Three percent of that is a hundred and eighty customers who watched their review disappear, and a hundred and eighty is the floor, because it prices every one of them at the median gap and the ones on fibre were losing a post in five. Most of them shrugged. Fifty three typed it again, and the table still has both copies.

Where the delay actually comes from

Four things make a copy late, and they have completely different shapes.

The shipping. Bytes have to cross a wire. Inside one data centre that is a fraction of a millisecond, since lesson 002 priced the full round trip at 0.5. Put the replica in another region and it is the whole story: 200 milliseconds Mumbai to Virginia on the same ladder, a floor no setting removes, and a permanent guarantee that a customer near your primary can beat your own replication. For Marlow this term is the smallest one. For anyone reading this who has a replica in another continent, it is the floor under all of them.

The replay. Lesson 011 established that a replica applies the log with one startup process while the primary writes with sixteen cores. There is a sharper version of that. Replay is not only single threaded, it waits on disk one page at a time. The primary makes its changes with many backends reading at once, each free to fetch pages in whatever order suits its query. The replica walks the records in log order, and every record whose page is not already in the replica's memory is a random read the whole replay stops for.

Marlow's 06:00 distributor import is the clean example. Lesson 010 established that it updates stock across eight thousand rows in a single statement, and takes Postgres's cheap path because stock is in no index. Lesson 010 also established the physical shape of that table: about forty rows to an 8 kilobyte page, 1.2 million rows, thirty thousand pages. Eight thousand darts over thirty thousand boxes, so 010's model gives thirty thousand times one minus e to the minus 0.27: about seven thousand distinct pages.

Seven thousand pages, one at a time. At the hundred microseconds lesson 002 gives a random SSD read, that is seven tenths of a second in which the replay process does nothing but wait for disk. The primary never paid it: eight thousand rows landing on seven thousand pages is exactly the case lesson 010 said the planner stops using an index for, so box A read the whole 240 megabyte table in order, in a hundred and twenty milliseconds. Same work, one machine doing it in page order and the other in log order. That is most of the 1.1 second spike the graph draws at six every morning. Newer Postgres reads pages ahead of the replay position to soften exactly this case, which helps and does not make replay parallel.

There is a wrinkle in how the result arrives, too. A transaction's log records are shipped as they are produced, so the replica can have applied most of a long transaction already, but none of its rows are visible until the commit record is replayed. A ninety second transaction on the primary shows up on the replica as ninety seconds of nothing followed by everything at once. The lag metric stays quiet throughout: other transactions are committing all the while, their commit records replay as they arrive, and "how old is the newest transaction I applied" sits near zero the entire time. Nothing is behind. One large thing is simply not there yet.

The pause. This is the one that catches people who read lesson 011 and did what it said. Marlow's fix for the cancelled payout job was an hour of max_standby_streaming_delay, which means exactly what it says: on the first Sunday of the month, the replica is permitted to stop replaying for up to an hour so the report can finish. That is not a bug and the founder chose it on purpose. The hour is only the ceiling; what the replica actually reaches is the payout job's own length, because replay restarts the moment the job's snapshot is released. Lesson 011 timed that job at twenty six minutes.

Replay does not stop at 02:00 when the job starts, though. It stops when the first cleanup record that would break the job's snapshot arrives, a couple of minutes in, which is why the graph's worst reading of the year is twenty four minutes and not twenty six, at 02:26, when the job lets go and replay catches up in a rush.

Nobody has noticed, because it happens at two in the morning against forty requests a second. Move that job to Tuesday lunchtime and the shop spends twenty odd minutes selling from a catalogue that gets steadily older while customers watch.

That is the shape to carry out of lesson 011: of its three ways around a recovery conflict, the one Marlow took is a lag setting wearing a different name. hot_standby_feedback is not one, which is exactly why it sends the bill to the primary instead.

Postgres will also let you ask for lag as a feature. Set recovery_min_apply_delay to four hours and you have a replica held deliberately four hours behind, which is the cheapest protection anybody has ever built against a DELETE with a missing WHERE clause. Lag is only a defect when you didn't ask for it.

The replica itself. Lesson 011 covered this and it needs one sentence: a machine smaller than its primary, or with a colder buffer pool, falls behind rather than costing less, and the first morning of a new replica is the worst one it will ever have.

Two numbers, and the one that lies at four in the morning

Everything above is measured against a position in the log. Postgres calls it an LSN, a log sequence number, and it is a byte offset into the write-ahead log written like 3/A21C8F40. Two ways to turn it into a lag figure, and they disagree in a way worth understanding.

-- on the primary: how far behind is each standby, in bytes
SELECT client_addr,
       pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS bytes_behind
FROM pg_stat_replication;

-- on the replica: how old is the newest transaction it applied
SELECT now() - pg_last_xact_replay_timestamp() AS behind_by;

Byte lag is exact and means nothing by itself. Four megabytes behind is one amount of time at Marlow's couple of writes a second and a completely different one at Galewatch, which collects a reading from each of nine hundred wind turbines every two seconds and so writes 450 rows a second all day. Same number, two different systems, and a byte count cannot tell you which one it is describing. You always have to divide it by a write rate, and the write rate moves.

Time lag means something and lies. On an idle primary nothing commits, so pg_last_xact_replay_timestamp() stops moving, and the number climbs by one second every second while the replica is perfectly caught up. Marlow takes almost no orders at four in the morning, so that graph draws a beautiful straight diagonal from about three until half past five, every single night.

Two and a half hours of it: the largest number on the graph all week, and the only one that means nothing at all. The table at the top of this lesson has those hours thrown out, which is a terrible way to own a metric. The first time it fired, the founder spent forty minutes at four in the morning hunting a fault that did not exist.

The fix is a heartbeat. Write one row to a one row table every ten seconds on the primary, read it on the replica, and compare the timestamp in the row against the clock. That number is honest on an idle system because the system is never idle any more. It costs six writes a minute, which every replica also carries, because reads divide and writes copy. Cheapest monitoring you will ever install.

Now the part that changes what you thought lesson 011 sold you. The bytes make four separate promises on their way to the standby, which is Postgres's word for a replica, and only the last one is about your customer.

primary writes WAL
  |  sent_lsn     the bytes have left
  |  write_lsn    the standby has them in memory
  |  flush_lsn    the standby has them on disk    <- durability
  v  replay_lsn   the standby has applied them    <- visibility

Lesson 011 offered synchronous replication as the cure for losing acknowledged writes on a failover, and it is. It is not the cure for Nikhil. Naming a standby synchronous makes the primary wait for flush_lsn: the bytes are safe on the standby's disk, so nothing acknowledged can be lost. It does not wait for replay_lsn. Nikhil's review can be durably on the replica's disk and still invisible to a query running a millisecond later, and you would have paid a network round trip per commit for the privilege.

Durability and visibility are different promises. Almost everybody who turns this on believes they bought both.

Postgres will sell you the second one. Set synchronous_commit to remote_apply and a commit does not return until the standby has applied it, which gives read-your-own-write on that standby, for every read, with no application change at all. The price is that every commit now waits for the slowest replay on the far side, and replay is the thing that stalls for most of a second every morning at six. You have not removed the 06:00 import's lag. You have moved it off your readers and onto your writers.

Four ways to win the race

Send the read to the primary. The read that follows a write goes to box A and nowhere else. Lesson 011's routing audit already does this for the customer's own orders straight after checkout, and that row is right. It is also the fix for the fuse 011 left on the session row: sign-in writes to box A, and the redirect that follows has to read it there rather than off a copy that has not got it yet. It is cheap at Marlow, where lesson 010's arithmetic put the shop's entire read load at 1.04 seconds of database work a second on sixteen cores. It has two problems everywhere else. The endpoints that write are the interactive ones, so this hands the primary your busiest traffic rather than your quietest. And "after a write" has no end: the review page is also read by strangers, and by Nikhil again tomorrow.

Stick the writer to the primary for a window. Remember that this customer wrote at 20:41:07, and send all of their reads to box A until 20:41:37. You are now choosing a number, and that number is a bet on the tail of the lag distribution. Thirty seconds covers Marlow's six o'clock import and does nothing for its first Sunday. An hour covers the first Sunday and hands box A every customer who has ever bought anything.

There is a trap inside this one that is worth more than the technique. The note saying "this person just wrote" cannot live anywhere subject to the lag it exists to work around. Put it in the sessions table and you have stored the fact that the replica cannot be trusted in a row you are about to read off the replica. It goes in a cookie, or on the primary, or nowhere.

Carry the log position. The precise fix. The write returns the LSN it committed at, the response carries it back to the client, and the next read checks whether this replica has passed it.

-- after the write, on the primary
SELECT pg_current_wal_lsn();                            -- 3/A21C8F40

-- before the read, on the replica
SELECT pg_last_wal_replay_lsn() >= '3/A21C8F40'::pg_lsn;

True, read the replica. False, wait a few milliseconds and ask again, or give up and read the primary. You pay for the guarantee exactly when you need it and nothing when you don't, which for Marlow's fibre customers is one request in five and for everyone else is almost never. The cost is plumbing: a token that survives the response, the browser and the next request, which means a cookie or a header and middleware that knows about both ends. It is also a database wide position, so a stranger buying a paperback moves your token forward and makes your read stricter than it had to be. Every big system that offers causal reads ships this shape under some other name.

Do not read at all. The cheapest of the four, and the one people skip because it isn't a database change. The POST already has the review; it arrived in the request body. Render the page from what you were handed instead of redirecting to a read, or return the created row in the response and let the client put it on screen. The race isn't won, it's deleted.

Its limits are real. It covers what the customer typed and not what the write implies, so the review appears and the book's average rating over two hundred reviews is still yesterday's. And it is a small act of faith: you are showing somebody a row on the assumption that nothing else about it changed in the same second.

That's the one the founder shipped, before lunch on the Monday. The review form's handler renders the book page itself, new review at the top, and never sends the redirect. The duplicates stopped that afternoon. The replica's median lag is still six milliseconds, which was never the problem.

When the same reader watches time run backwards

Read-your-own-write is one promise. There is a second one you can lose without writing anything at all.

Monotonic reads means the world never gets younger. Two reads by the same person, one after the other, must not go forwards and then back. Lesson 011 priced the reader endpoint that managed databases hand you, one name that spreads connections across every replica you own, and this is the promise it breaks.

Say the shop had two replicas rather than one. Replica one is six milliseconds behind; replica two is nine hundred milliseconds behind, working through a bulk import. A book page loads against replica one and shows twelve reviews. The customer refreshes, lands on replica two, and there are eleven. Nothing errored. Nothing will appear in any log. Somebody will file a bug that says "the review count sometimes goes down" and it will sit in the backlog for a year, because it does not reproduce.

Marlow cannot hit this yet, having exactly one replica, which is close to the only good thing about having one.

Point Galewatch's dashboards at a pair of replicas, which lesson 011 argued the company should not buy and somebody eventually buys anyway, and the shape gets nastier. The panel refreshes every five seconds and alternates between two points in the log. A turbine's output reads 2.31 megawatts, then 2.28, then 2.31, then 2.28. An engineer spends an afternoon chasing an oscillation invented by a load balancer.

Two promises, two prices. Pinning each reader to one replica, by hashing their customer id, buys monotonic reads for almost nothing, until a replica dies and everybody reshuffles onto the survivors, which is lesson 031's problem. It buys read-your-own-write not at all. Keeping the highest LSN you have seen for that reader, and applying the third fix above to every read rather than only the ones after a write, buys both. A reader endpoint on its own buys neither, and it is the default.

Put the number on the glass

Lesson 003 left an engineer looking at a healthy green tile for a turbine that had been feathered since eleven that morning. The link had dropped, the dashboard showed the last reading it held, and nothing on the screen mentioned its age.

Put that dashboard on a replica and there are now two ways for the tile to be old. The turbine's link went down in the weather, or the copy is behind. To the engineer standing under the turbine those mean the same thing, and neither one has a colour.

So show the age. Not a green tile: 2.31 megawatts, as of 10:41:58, with the seconds ticking up when nothing new arrives. One number, measured at the end a human is actually looking at, folding together the radio link, the ingest queue, the replica and every hop nobody has thought of yet.

The alternative, which is what shops build, is three accurate staleness numbers on three different dashboards, one per hop, none of them in front of the person making the decision. That is how you get the green tile back with more monitoring than you had before.

Recap

The race is lag against the gap, not lag against zero. Your write and your next read are separated by roughly one round trip to the customer, and the failure rate is whatever share of the lag distribution sits above that. Both numbers are distributions, and neither is the line on your dashboard.

The customers closest to you lose. A short round trip means a small gap, and a small gap means the copy has less time to catch up. It is the only bug in this course that gets worse when your network gets better.

Two numbers, and one of them lies at four in the morning. Byte lag is exact and needs a write rate to mean anything; time lag means something and climbs forever on an idle primary. A heartbeat row every ten seconds makes the second one honest.

Durability is not visibility. A synchronous standby waits for the bytes to reach disk, not for them to be applied, so the write can be safe on the replica and invisible on it in the same instant. Only remote_apply buys visibility, and it pays for it out of your write latency.

The cheapest fix is not reading. Three of the four ways to give a customer their own write back are ways to route around the copy. The fourth is to notice the write already knew the answer and hand it straight back.

One staleness number, measured at the glass. Every hop between the event and the eye adds age, and the person looking at the screen needs the total, not a colour.

Check your understanding

1. A service commits a write and then, in the same request, reads it back from a replica four milliseconds later on the same rack. The lag graph shows a steady two milliseconds. Say whether this is safe, what you would measure before answering, and what changes if the read moves into the customer's next request instead.

2. A team's replica lag alert fires every night between 02:00 and 05:00 and never during the day. Nobody has found a cause in six months and the alert is now routed to a folder. Explain what is most likely happening, and describe the change that would make the alert worth reading again.

3. Galewatch has 450 writes a second arriving from its turbines and wants a replica for its year long reports. Give two distinct ways that replica ends up minutes behind, say which one you would see in a byte lag graph and which you would not, and say what you would put on the engineers' dashboard as a result.

4. Somebody proposes fixing read-your-own-write across a whole application by setting synchronous_commit to remote_apply. Say what it does fix, what it costs at Marlow's write rate of a couple of writes a second, and what it would cost at Stagefront, the ticketing service where two hundred thousand people press the same button at 10:00.

5. You inherit a service that pins each signed-in customer to the primary for sixty seconds after any write, and the primary is running hot. You cannot change the sixty seconds this quarter. Name two things you would measure to find out how much of the primary's load that rule is responsible for, and say what you would replace it with.

Next lesson

013 Sharding: Cutting the Database So It Fits. Lesson 011 showed that replicas stop helping once replay alone fills a machine, and today showed that even the ones that help hand your customers a copy that is behind; next lesson takes the only move left, which is to stop having one primary at all.