All Your GUCs in a Row: recovery_prefetch

WAL replay is one process doing one thing at a time. The startup process reads a record, finds the page it changes, applies the change, and reads the next record; when the page isn’t in shared_buffers, it goes to the disk for it and waits. Before PostgreSQL 15 that was the whole story, and on storage with a millisecond of read latency, which is to say every network-attached cloud volume, it was a grim one: a standby that needs a million pages it doesn’t have will spend the next seventeen minutes asking for them one at a time while the volume underneath it idles. recovery_prefetch lets the startup process ask for pages before it needs them.

The default is try, the other values are on and off, and the context is sighup: you can change it on a running standby with a reload and it takes effect in the middle of replay. try means “if the operating system has a way to ask for read-ahead,” which Linux does (posix_fadvise(POSIX_FADV_WILLNEED)) and, from 18, macOS does (fcntl(F_RDADVISE)); on Windows try silently does nothing, and on refuses to start with “not supported on platforms that lack support for issuing read-ahead advice.” There is no reason to ever write on. The parameter has two partners. wal_decode_buffer_size (default 512kB, postmaster) is how far ahead in the WAL the startup process may decode. maintenance_io_concurrency is how many prefetches it may have outstanding at once: 16 on 18 and later, 10 on 15 through 17, and 0 turns prefetching off without saying so.

What it looks ahead at

The prefetcher is a wrapper around the WAL reader. Each time replay asks for the next record, the prefetcher first decodes records beyond it, walks their block references, and decides what to do with each. A main-fork block that isn’t in shared_buffers gets a posix_fadvise(WILLNEED), which tells the kernel to start reading it into the page cache now, in the background, so that when replay reaches the record the read() is a copy from memory; the view counts that as prefetch. A block that is already in shared_buffers counts as hit, and the prefetcher remembers which buffer it was in so that replay can try that buffer first and skip the mapping-table lookup, a small speedup on every hit that you also lose if you turn it off. Everything else is a skip. A block that arrives with a full-page image is skip_fpw: the image replaces the page, so there is nothing to read. A block the record is going to zero-fill is skip_init. A block in a relation that hasn’t been created yet, or past the relation’s current end, or inside a range about to be truncated, is skip_new; the prefetcher keeps a filter for those until the record that creates, extends or truncates the relation has been replayed, because a relfilenumber can be reused and reading the previous tenant’s blocks would be wrong as well as useless. A block that matches one of the last four it looked up is skip_rep, which is what keeps a hundred consecutive inserts into the same heap page from costing a hundred system calls. Blocks in the free-space and visibility forks are not considered at all.

Each prefetch counts as in flight until the record that referenced it has been replayed, since the prefetcher has no other way to learn that the kernel is done. maintenance_io_concurrency caps the in-flight count, and the lookahead stops at four times that many block references or at wal_decode_buffer_size bytes of WAL, whichever comes first. On my standby, below, the reference limit came first by a wide margin: at the default 16, the prefetcher never looked more than about 4kB ahead in the WAL, and 508kB of decode buffer went unused.

What it can’t see

The page cache, for one. The prefetcher knows what is in shared_buffers and nothing about what the kernel already has, so on a system whose working set fits in RAM, most of its advice is for pages the kernel could have returned instantly. Each of those is a wasted system call, and worse, each occupies one of the 16 in-flight slots until its record is replayed, so a well-cached standby spends its concurrency budget on no-ops. The prefetch counter counts advice issued, not disk reads.

Full-page images, for another. With full_page_writes on, which it is, the first touch of each page after a checkpoint ships the whole page and gets a skip_fpw. The prefetcher only helps with the second and later touches within a checkpoint interval, and only when those pages have since left both shared_buffers and the page cache. That is a real population on a standby whose working set is bigger than memory, and an empty one on a standby that isn’t. The counters also hide a wrinkle. When two consecutive records touch the same page and the first one carries the image, the prefetcher skips the first and then advises the page for the second, because the first hasn’t been replayed yet when it looks at the second; the image lands in shared_buffers a moment later and the advice was for nothing. A cross-page UPDATE does exactly this, writing a LOCK record for the old tuple’s page and then the UPDATE record for the same page, and in a pgbench run with full-page writes 63% of first touches came in that shape. The prefetch counter for that run was 951,000 against 643,000 reads that happened.

And direct I/O. With debug_io_direct = data, read-ahead advice has no page cache to read into, so the prefetcher quietly does nothing, without even incrementing a skip counter. The asynchronous I/O subsystem that arrived in 18 doesn’t change any of this: replay still reads one block at a time, the prefetcher still works by advice, and io_method only decides how the eventual read is issued. The difference between 18.6’s xlogprefetcher.c and 19 RC1’s is a copyright year, some shared-memory plumbing and cosmetic changes.

Where it came from

In 2018 Sean Chittenden at Joyent presented pg_prefaulter at PGCon, a Go sidecar that ran pg_waldump on the standby’s incoming WAL and read the referenced pages into the page cache from a pool of threads, because a 16-disk array under a lagging standby was running at about six percent of its random-read capacity. Konstantin Knizhnik started the pgsql-hackers thread on doing it inside PostgreSQL that June, and Thomas Munro posted a different approach, prefetching from inside the recovery loop with posix_fadvise(), at the start of 2020, reporting a contrived crash-recovery benchmark up to 20 times faster. It was committed for 14 in April 2021, with the default off, reverted a month later because it had known bugs and the cycle had no room left to fix them properly, and committed again in April 2022 for 15 with the default set to try.

How much it buys you

My test box is two vCPUs and 8GB, and its “disk” answers a random 8kB read in about 50 microseconds, which means it is the host’s RAM. Even there, prefetching shows: crash recovery of 309MB of WAL from a pgbench -N run against a 3GB table with shared_buffers = 256MB took between 34 and 54 seconds across five runs with recovery_prefetch = off, and 27 to 31 seconds across five with try, with io_method making no difference I could separate from the noise.

To see what the parameter is for, I made the disk slow. An LD_PRELOAD shim in the startup process (with io_method = sync, so that the startup process issues its own reads and the shim can see them) charges one millisecond, roughly the going rate for a cloud block volume, to every data-file read that misses the kernel page cache, and treats a WILLNEED for a block the kernel doesn’t have as a read that completes one millisecond later; the real advice and the real reads still happen, so the kernel’s page cache behaves as it would anyway. Same crashed cluster, full_page_writes = off so that every block reference is a candidate, page cache dropped before each run:

Prefetching at the default depth made recovery 4.6 times faster; at a depth of 64 it made the disk disappear, and replay went back to being CPU-bound at the same 30 seconds as the fast-storage run. Those 266 seconds of waiting are 266,000 pages, each read once, one at a time. (19 RC1 on a cluster of its own: 385 seconds and 74.)

That run was flattering in one respect: about 70% of what the prefetcher advised the kernel already had, so only 376,000 of its 1.26 million advisories cost anything. Pretend instead that the working set is bigger than memory, so that nothing survives in the page cache between touches, and compare the same run with full_page_writes on, which turned 309MB of WAL into 2.9GB and got through a quarter fewer transactions in the same two minutes:

Twenty-five minutes of replay, or thirteen, down to a minute and a half at the default depth and under a minute at 64. The FPI run is “only” thirteen minutes without prefetching because the first touch of each page is a write, not a read, so there are fewer reads to stall on (643,000 against 1.25 million, for three quarters as many transactions); the prefetcher takes those 643,000 to the point where replaying nine and a half times as much WAL is once again a CPU problem.

The other end of the scale is a standby whose working set fits in RAM, where the prefetcher has nothing to do and does it anyway. On a 1GB pgbench cluster with full-page writes, every page replay needed was already in the kernel’s cache by the time it was needed, because the image that preceded it had been written there; 959MB of WAL replayed in 11.0 seconds with prefetching off and 13.6 with it on (three runs each, one try run thrown out because I was running something else on the box at the time). That is 783,000 advisories, not one of which saved a read, at a cost of about a quarter of the replay time. (The 2.9GB FPI-heavy run, with fewer block references per byte of WAL, paid between 5% and 25% depending on the run.) That only matters when replay is short of CPU, which a standby that is keeping up is not; but it is not free, and there is one place it matters, below.

Watching it

pg_stat_recovery_prefetch is one row, cumulative counters for the six outcomes above plus three instantaneous values, and it is the only instrument you need. This is a 19 RC1 standby, started cold with about 80MB of backlog, sampled every half second while it caught up:

1
   time    | prefetch |  hit   | skip_fpw | skip_rep | wal_distance | block_distance | io_depth
2
-----------+----------+--------+----------+----------+--------------+----------------+----------
3
09:39:45.8 |    34969 |  37001 |    13751 |    19471 |         3976 |             63 |       16
4
09:39:46.4 |    56963 |  61539 |    13751 |    35029 |         4120 |             62 |       16
5
09:39:46.9 |    81957 |  87476 |    13751 |    55164 |         3696 |             60 |       16
6
09:39:47.5 |   107989 | 113330 |    13751 |    77689 |         4096 |             59 |       16
7
09:39:48.2 |   135281 | 141155 |    13751 |   102099 |         4344 |             59 |       16
8
...
9
09:39:51.7 |   298148 | 295082 |    13751 |   281437 |         4504 |             53 |       16
10
09:39:52.3 |   313313 | 308398 |    13751 |   300709 |            0 |              0 |        0

io_depth sat at 16, the configured ceiling, for six of the seven seconds it took to catch up, with block_distance between 50 and 63 against the 64-reference lookahead limit and wal_distance at 4kB of a 512kB window. That pattern, io_depth pinned at maintenance_io_concurrency while pg_last_wal_replay_lsn() trails pg_last_wal_receive_lsn(), is the signal to raise maintenance_io_concurrency; if the standby is behind and io_depth is well below the ceiling, replay is waiting on something other than reads, and prefetching won’t help. The last row is the standby caught up, at which point all three instantaneous columns go to zero. The prefetcher can only look ahead into WAL that has already arrived, so a standby in lockstep with its primary has little to look ahead into; this feature helps the standby that is behind, which is the only one you were worried about.

Leave recovery_prefetch at try. The decision that matters is next door: on a standby on network-attached storage, maintenance_io_concurrency at 16 is a start, 32 to 64 is where the stalls go away, and pg_stat_recovery_prefetch.io_depth will tell you which. The exception is the standby that is behind with its startup process pegged at 100% CPU. It is not waiting for reads, whatever io_depth says; replay is single-threaded, that core is the only one it will ever get, and if the database is in RAM the prefetcher is spending up to a quarter of it on advice the kernel doesn’t need. Turn it off on that standby. It’s sighup; you’ll know within a minute whether it helped.