Read Replicas: Reading From a Copy

The job had been failing once or twice a year for three years, always the same way, always at two in the morning. In June it failed differently.

Marlow Books is a four person online bookshop that exists only in this course. On the first Sunday of every month at 02:00, a script works out what each publisher is owed: pull every order line for the year, group by publisher, render a CSV, email it. Lesson 001 used that job as the shop's clearest case of shared fate, because it loads the whole year into memory, twenty one gigabytes of it, and on some Sundays the Linux out of memory killer took Postgres instead of the job and the shop was dead until somebody woke up.

Two weeks before that June Sunday, the founder had added a second database machine: a replica of box A, which has been Marlow's only database since lesson 005. Moving the payout job onto it was the first thing they did, because getting the big monthly job off the primary is what you do with a replica.

At 02:31, thirty one minutes in, the job stopped with a sentence the founder had never seen.

ERROR:  canceling statement due to conflict with recovery
DETAIL:  User query might have needed to see row versions
         that must be removed.

Nothing had run out of memory. Nothing had crashed. Both machines were up, both were healthy, and the job had been cancelled by the database it was running on, on behalf of the database it was copying, over rows it had never asked about.

They reran it at 07:40 and it finished in twenty six minutes. July's run failed three times before it went through.

A replica is a server that never finishes recovering

Postgres, like every database you would trust with money, writes down what it is about to do before doing it. The change goes into the write-ahead log, the WAL, as a compact record saying which bytes of which page are becoming what, and the page itself gets written later. Pull the power at the wrong instant and the database comes back up, finds the last point it knows was consistent, and replays the log forwards until the files agree with it again.

That machinery has to exist anyway. Streaming replication is what you get when somebody notices the log can be sent somewhere else.

  writes                                    reads you chose
    |                                             |
    v                                             v
 [ PRIMARY ] --- WAL records, continuously --> [ REPLICA ]
   box A                                       applied by one
   16 cores                                    startup process

The replica opens a connection to the primary, the machine taking the writes, asks for the WAL as it is produced, and applies it. Then it keeps doing that, forever. It is a server permanently in the middle of crash recovery, and the only unusual thing about it is that it never finishes. Postgres calls a replica that will also answer queries while it replays a hot standby, which is the only kind anybody wants.

Several things fall out of that, and each one catches somebody.

It copies pages, not statements. The replica's files are byte for byte the primary's files, which kills the most popular idea anybody has about replicas: you cannot put the heavy reporting indexes on the replica and leave the primary lean. The replica has exactly the indexes the primary has, and you cannot give it another one, because giving it one would be a write.

It is read only more strictly than you would guess. A standby will not let you create a temporary table, and a surprising number of reporting queries are written with one.

Replay is a single process. The primary writes with sixteen cores; the replica applies with one. That one process is doing far less work per change than the original write did, since there is nothing to parse, nothing to plan and no index to search, just bytes onto a page. It is still one process working through records in order, which means there is a write rate above which no replica keeps up, and you find that rate by raising the writes, never the reads. How far behind a replica gets, and what that does to your users, is lesson 012.

And nobody waits. By default the primary commits, tells the client yes, and moves on; the bytes reach the replica when they reach it. That default is what makes the failover story at the end of this lesson cost a day.

There is a second kind worth knowing by name. Logical replication decodes the WAL back into row changes and applies them as ordinary inserts and updates on the far side, so that target is a real writable database: it can carry extra indexes, hold only the tables you asked for, even run a different major version. You pay for it with a slower path, a requirement that every replicated table has a way to identify its rows, and the fact that it does not carry your schema changes across. When people say read replica they almost always mean the physical kind, and so does the rest of this lesson.

What a replica actually offloads

The received wisdom is that the database is the bottleneck, so you split the reads off and buy yourself room. Price it on Marlow before believing it.

Lesson 010 measured the shop's Christmas peak: 384 book page requests a second, each one primary key read of about 0.2 milliseconds since lesson 009's split, plus sixteen full text searches at about sixty milliseconds each. That is 384 times 0.0002 plus 16 times 0.060, which came to 1.04 seconds of database work every second. Box A has sixteen cores.

One and a bit seconds of work a second, on sixteen cores, is six and a half percent of the machine.

Marlow does not need a read replica. Not for reads, not at the busiest hour of its year. The entire shop's read load fits in a fifteenth of the box it already owns, and it fits because 009 split the cached page from the row underneath it and 010 added one index to a page the robots were hammering.

The number that says whether a replica would help at all is the read to write ratio, which lesson 009 used for a different question and which is the whole answer here. Lesson 009 clocked Marlow packing about one order every five seconds at the Christmas peak, and orders cannot arrive much faster than they are packed for long without a backlog somebody would have mentioned, so call it a couple of writes a second once you have counted the order lines, the stock decrements and the basket 007 left sitting in the sessions table. Two writes against four hundred reads. Two hundred to one.

That ratio hides something, though.

A replica does not take half the database's work. It takes whatever share of the reads you send it, and all of the writes, because applying every write is the entire job of being a replica. Stand five replicas behind a busy primary and each one is carrying the full write load plus a fifth of the reads. Reads divide. Writes copy.

So there is a ceiling on this technique. Replicas buy read capacity right up until replay alone fills a machine, and past that they buy nothing at all, however many more you sign for. Galewatch, which collects readings from nine hundred wind turbines, is built in the shape that gets there: 450 writes a second, every one a new row, against dashboards a handful of engineers open. Adding replicas there hands each new machine all 450 writes a second for the privilege of splitting a trickle of reads. When the writes are the problem, the only move left is to stop having one primary, and that is lesson 013.

So why did Marlow buy one in June?

Not for throughput. Since lesson 005 put the second application box in, Postgres has never left box A, and 005 said plainly what that means: if box A refuses to come back one evening, box B is an application server with nothing to talk to, and the shop is exactly as gone as it was in August. Lesson 003 called that the shared cause, the thing a second box does not fix. A live second copy of the data, on a second machine, is the first honest answer Marlow has had to it. Five hundred dollars a month, which is 005's price for sixteen vCPUs and sixty four gigabytes, and it has to be that size: the replica pays for the writes whichever reads you give it, and it needs the pages those writes touch in memory to keep up, plus the pages of whatever you point at it. Marlow pointed the full text searches at it, and searching descriptions and reviews is the workload that pushed box A's working set past forty gigabytes in the first place. A replica smaller than its primary is not a cheaper replica. It is one that falls behind.

Nobody warns you about the first morning, either. The searches on the new replica were slower than they had been on box A, and nothing was broken. Lesson 010's two caches are both cold on a machine that has never served a query, so the first hour of every read you move is paid at disk prices.

The error nobody has seen

Now the June Sunday.

Lesson 010 established that Postgres never modifies a row in place. An update writes a new version and leaves the old one sitting there, dead but present, until vacuum comes along and removes it. That removal changes a page, so it is a WAL record, so the replica has to apply it.

Which puts two true statements on the replica at the same moment. The startup process holds a record saying the dead versions on page 14,208 are gone. The payout job holds a snapshot from twenty minutes ago that might still need them. Only one of them can win.

Postgres does not split the difference. It waits, for max_standby_streaming_delay, which is thirty seconds as shipped, and if the query is still running at the end of that it cancels the query. That is the sentence in the founder's log.

Two details make it sharper than it first reads. The standby does not check whether your query touches that table; it checks whether your snapshot is older than the rows being removed, so a vacuum of any table in the database can take out any long query in it. And those thirty seconds are not a grace period handed to your query; they are the total replay delay the standby will tolerate, so a replica already a few seconds behind has spent part of yours before you started.

The intermittency has arithmetic under it. Autovacuum's defaults fire on a table when its dead versions pass fifty plus a fifth of the row count, and books holds 1.2 million rows, so the line sits at 240,050. The 06:00 distributor import updates stock on about eight thousand rows a day, which lesson 010 showed takes Postgres's cheap path, and eight thousand a day crosses 240,050 in about a month. A monthly payout job against a roughly monthly vacuum, each landing wherever it lands. The founder's three July retries were not diagnosis. They were a coin toss weighted on the other machine. The real rate is a bit higher and less tidy than once a month, because a page carrying dead versions also gets pruned opportunistically the next time somebody reads it, and that pruning is a WAL record too.

There are three ways out, and they are one trade seen from three sides.

Raise max_standby_streaming_delay and the replica pauses replay rather than cancelling you. Your report finishes. For as long as it runs, the replica is a copy of half an hour ago, and every other read pointed at that machine is that stale.

Turn on hot_standby_feedback and the replica tells the primary which row versions its queries still need, and the primary stops vacuuming them. Your report finishes and the replica stays current. The bill moves to the primary, which now holds dead rows it is not allowed to clean up, and a table that cannot be vacuumed grows. Twenty six minutes of that is nothing. A psql window somebody left open with a transaction in it over a long weekend is how people find out what table bloat is.

Or take the cancellation and run the long work on a machine with nothing to lose. Which, after you have had this argument twice, is the real reason shops with serious reporting own more than one replica, and read throughput is nowhere in it. A dashboard that has to be current and a report that has to finish want opposite settings on the same machine, and there is no value of max_standby_streaming_delay that serves both.

Marlow has one replica, one monthly job, and nobody reading anything at two in the morning, so the answer was an hour of max_standby_streaming_delay and a line in the runbook. It cost nothing and took two months to find.

The memory problem the founder was moving away from had quietly gone in August, when 005 took box A from thirty two gigabytes to sixty four. Twenty one gigabytes of report on a sixty four gigabyte box is not an out of memory killer story any more. Nobody had connected the resize to the job, so the job got moved for a reason that had expired ten months earlier, onto a machine that introduced a failure it had never had.

Which reads are allowed to move

The obvious rule is that SELECT goes to the replica and everything else goes to the primary. It is wrong in three separate ways, and the two that cost you money do not raise an error.

Some reads write. Lesson 007 considered giving Marlow's sessions a sliding expiry, stamping last_seen on the row every time an authenticated request came in, and turned it down. Good thing too, because that stamp turns every read of a session into a write, and a write can never go to a replica. A view counter, a "mark as read", a login that records the time: from the outside every one of them is a read, and not one of them can leave the primary. Before routing anything by verb, go and look at what the handler does after it reads.

Some reads decide. Lesson 008 drew the line between a read that is a suggestion and a read that is a decision, and lesson 009 paid the bill for crossing it, when Marlow sold forty copies of a book it had twelve of because the checkout's in-stock guard trusted a cached number. A replica is a copy, and a copy is behind. How far behind is 012's subject; that it is behind at all is all today's argument needs. The stock figure on a book page is a suggestion and can come from anywhere. The stock check that decides whether to take somebody's money reads the row on the primary, inside the transaction that writes the order.

Some reads are the first half of a write. If the request is going to update what it just read, that read belongs in the same transaction on the primary. Split it across two machines and you have built a race with a network hop in the middle of it.

Marlow's audit, done properly in June:

The fuse is the sign-in. Postgres writes the new session row on box A, the browser follows the redirect, and the request that lands a fraction of a second later asks a copy that may not have the row yet, so the shop meets a customer who just signed in with a sign-in page. That does not disqualify sessions from the replica. It means the read straight after a write goes to the primary, which is the problem lesson 012 spends most of its length on.

Stagefront, the ticketing service where two hundred thousand people press the same button at 10:00, is the case where a replica is beside the point. The seat map is already cached with a ten second TTL from lesson 008, the holds and the purchases are the writes, and lesson 009 counted the seat rows changing well over a hundred times a second during an on-sale. Reads divide and writes copy, so a replica brings that minute nothing it is short of. What Stagefront should point at a replica is the admin reporting service lesson 003 established is not on the purchase path: the people asking how last Tuesday's show sold, who do not mind an answer a few seconds old and who must not be running a forty second aggregate against the database taking money.

Where the routing decision lives

You can make the decision in the application, by holding two connection pools and asking for the replica by name. It is visible in code review, it survives a framework upgrade, and when somebody forgets, their query runs on the primary: slower, correct, boring. That is the right direction to fail in, and it is the argument for making the primary the default and the replica the thing you have to type.

You can make it in a proxy that reads the SQL and sends SELECT one way and everything else the other. This is popular because it is nearly free, and it is precisely wrong on the first two failures above, since no proxy can see that this particular SELECT is the one deciding whether to charge a card. It does handle the third, by pinning a connection to the primary the moment it sees a write inside a transaction, and that is worth having. Take it as a safety net rather than a policy.

A managed database will usually hand you a reader endpoint too, one name that spreads connections across every replica you own. Convenient, and it quietly means two reads in one request can land on two replicas sitting at two different points in the log, so a page renders its list from one and its detail from the other and shows a customer a book that is not in the list above it. Lesson 012 owns how big that gap gets.

What I would do is route per request rather than per statement. Mark the endpoint read only and the whole handler gets the replica pool; leave it unmarked and it gets the primary. The developer makes one decision, at the moment they know what the page is for, instead of thirty decisions at moments when they do not.

There is a bill attached that lesson 010 set up and never paid. Marlow's fleet already reserves 96 of Postgres's 100 connections on box A: two boxes, four application processes each, twelve per pool since lesson 006 cut them. Add a replica and every one of those eight processes wants a second pool, so either the totals go up or the primary's share comes down. Two pools, two limits, two ways to run out, and still no connection pooler anywhere in the picture. Lesson 010 called the pooler the place where you choose where the queue forms. With a replica there are two queues to place, and Marlow has placed neither.

A copy is not a failover

Everybody assumes this part arrives free with the second machine. All the data is already on it. Box A dies, so you promote the replica, point everything at it and carry on, and Postgres will do the promotion itself in a few seconds.

Four things have to happen, and the database does one of them. Something has to decide the primary is dead and be right, which is the whole of lesson 032. Something has to promote the replica. Something has to repoint every client, every pool and every scheduled job. And something has to guarantee the old primary never takes another write, because if it comes back and accepts one you have two databases that both believe they are in charge, and a reconciliation nobody has ever rehearsed.

On 21 October 2018, GitHub was replacing failing optical network equipment, and the work briefly cut the link between their US East Coast network hub and their primary US East Coast data centre. Connectivity came back in forty three seconds. In that window their automated failover, unable to reach the primary, promoted a database cluster on the US West Coast, and the East Coast primary had taken writes that never made it west. GitHub chose the data over the uptime and ran degraded for twenty four hours and eleven minutes while they worked out what to merge back together. The failover took seconds. The recovery took a day.

That is the price of "nobody waits". Asynchronous replication means every commit the primary has acknowledged but not yet shipped is a write you lose on promotion, and you learn the count afterwards.

Postgres will make the primary wait, if you ask it to. Name a standby as synchronous and a commit does not return until that standby has the bytes, and no acknowledged write can go missing. Two prices come with it. Every commit now pays a network round trip: half a millisecond inside one data centre on lesson 002's ladder, two hundred milliseconds from Mumbai to Virginia. Marlow's couple of writes a second would not notice either one. Stagefront would not notice the first and could not run an on-sale on the second, and the second is the only version that also survives losing a data centre. And if that synchronous standby goes down, commits on the primary stop, which is a remarkable thing to have built in the name of staying up. The way out is to require any one of two standbys, which means owning three machines before you have the property you thought you were buying with two.

Marlow's honest position at the end of June: box A's disk is no longer the end of the shop, which is worth five hundred dollars a month by itself. Nothing promotes anything automatically, the replica is asynchronous, and the founder has never once practised the switch. What they bought is a warm copy and a shorter bad day. In lesson 003's terms it moves MTTR, not MTBF, and 003 already said which of those two you can change this afternoon.

Recap

A replica is a server that never finishes recovering. Streaming replication ships the write-ahead log the database was writing anyway, and the replica applies it forever. Everything odd about replicas follows from that: byte identical files, so no extra indexes over there; read only to the point of refusing a temporary table; one process applying what many cores produced.

Reads divide, writes copy. Every replica you add carries the whole write load and a share of the reads, so replicas buy read capacity until replay alone fills a machine and then buy nothing at all. Sharding is what is left after that.

Six and a half percent of one box. Marlow's entire Christmas read load is 1.04 seconds of database work a second on sixteen cores, so the shop's replica is not a throughput purchase. Do this arithmetic before buying the machine, because the answer changes what you should buy.

Your long query on the replica is holding up the primary's history. A vacuum on the primary produces cleanup records the replica must apply, your snapshot still needs the rows they remove, and after thirty seconds one of you loses. Pausing replay, feeding your snapshot back to the primary, or moving the job to its own replica are the same trade seen from three sides.

The routing decision belongs to the use case, not the verb. Some reads write, some reads decide, and some are the first half of a write, and a proxy reading SELECT can catch at most the last of those. Mark the request, not the statement.

A copy is not a failover. Getting there needs a correct death certificate, the promotion itself, a repoint and a fence, and the database supplies one of those. Forty three seconds of network trouble at GitHub in October 2018 became twenty four hours and eleven minutes of degraded service, because the copy that got promoted was behind.

Check your understanding

1. A service does 20,000 reads a second and 4,000 writes a second against a single primary running at 70% CPU. Product wants a design that survives ten times today's traffic. Say how far read replicas take you, where exactly they stop, and what number you would measure to find that point before you hit it.

2. Galewatch's engineers want a replica so their year-long reports stop competing with ingestion. Ingestion is 450 writes a second and a report takes about twenty minutes. Say what you would set on that replica, what you are giving up by setting it, and what you would check a week later to find out whether you were right.

3. A team puts a proxy in front of their database that routes every SELECT to the replica. Name three queries in a shop like Marlow's that this breaks, and for each one say whether the breakage arrives as an error or as a wrong answer that nobody notices.

4. A team runs one replica with both their live dashboards and their nightly export pointed at it. The export kept getting cancelled, so they turned on hot_standby_feedback, and now the export finishes but the primary's largest table has grown by forty percent in three weeks. Explain the chain, and say what you would change and in what order.

5. Marlow's founder wants the replica promoted automatically whenever box A stops answering for thirty seconds. Give two distinct ways that ends badly, and say what you would require to be true before turning it on.

Next lesson

012 Replication Lag: When Your Own Write Disappears. Today's replica is behind by an amount this lesson never had to put a number on; next lesson measures it, works out what makes it grow, and deals with the customer who writes a review and then cannot find it.