PostgreSQL's recovery_prefetch prefetches pages the WAL already holds

Originally published on ImportStatic.

A post on ardentperf from October 20, which reached the Hacker News front page, describes a synchronous standby on a 500 IOPS cloud disk that fell behind after a write burst and stalled writes on the primary for over 30 minutes. With recovery_prefetch = off the long stall went away, and the author leans towards turning it off everywhere. We don't have a standby on metered storage, so we couldn't reproduce the outage. We did count what the prefetcher spends its reads on during replay on PostgreSQL 18.6, and the same counters are available on your own standby.

Some of those reads are for pages that replay is about to rebuild from a full-page image in the WAL. The prefetcher leaves a block alone when the record that touches it carries an image, but it doesn't keep track of that block afterwards. When the next record touches the same page, it asks the operating system to read the page from disk. One heap page and two updates are enough to reproduce it, and the cause is a dozen lines of xlogprefetcher.c.

What the prefetcher does

recovery_prefetch arrived in PostgreSQL 15. The docs describe it as trying "to prefetch blocks that are referenced in the WAL that are not yet in the buffer pool, during recovery". The default is try, which turns it on wherever the OS can take read-ahead advice. On this Mac the PostgreSQL 18.6 build accepted on, which a build without read-ahead support rejects.

The prefetcher decodes WAL ahead of replay. In the REL_18_STABLE source, it allows maintenance_io_concurrency reads in flight (16 by default) and looks up to four times that many block references ahead, within the wal_decode_buffer_size window (512 kB). It only considers the main fork. For each block reference, the checks run in this order, and each outcome increments a column of pg_stat_recovery_prefetch:

The "last four" window is updated after the full-page-image check. A block skipped for its image returns early and never enters the window, so the window can't catch the next reference to it. Shared buffers won't help either, because when the prefetcher reaches that later record, replay often hasn't restored the image yet.

Reproducing it with one page

The test table has three 1 kB rows per 8 kB page (fillfactor = 50, no indexes), so every update is a HOT update on the same page. After a CHECKPOINT, a Go driver ran these statements, each as its own transaction, then stopped the server with pg_ctl -m immediate and started it again so crash recovery replayed them:

update t set v = v + 1 where ctid = '(0,1)'; update t set v = v + 1 where ctid = '(1,1)'; -- pages 1 .. N, one row each update t set v = v + 1 where ctid = '(2,1)'; update t set v = v + 1 where ctid = '(0,2)'; -- page 0 again 

pg_get_wal_block_info from pg_walinspect lists the block references for t in WAL order. With 20 pages between the two visits to page 0, it printed:

0+fpi 1+fpi 2+fpi 3+fpi 4+fpi 5+fpi 6+fpi 7+fpi 8+fpi 9+fpi 10+fpi 11+fpi 12+fpi 13+fpi 14+fpi 15+fpi 16+fpi 17+fpi 18+fpi 19+fpi 20+fpi 0 

The first touch of each page after the checkpoint carries a full-page image, as full_page_writes promises. The second touch of page 0 doesn't. After the crash, the counters were:

 prefetch | hit | skip_init | skip_new | skip_fpw | skip_rep ----------+-----+-----------+----------+----------+---------- 1 | 0 | 0 | 0 | 22 | 0 

The single prefetch was for page 0. Every other block was skipped for its image; the 22 are the 21 pages of t plus one pg_attribute page that a hint-bit write had imaged. Replay restores page 0 from the image in the first record, so the second record finds it in shared buffers and never uses the read the prefetcher asked for.

With other gaps, and with full_page_writes off:

With images on, page 0 is prefetched a second time even when the two records are back to back. With images off, the four-entry window catches the repeat at a gap of 2 (skip_rep = 1) but not at 20. The short runs count a few more images than t has because replay also covered catalog hint-bit images written after the test; the prefetch column is the one to read.

Stephen Frost raised this case during review in 2021, estimating thousands of unnecessary prefetch calls in a bad case with the default 512 kB decode buffer. He judged that the benefit outweighed the cost and that the redundancy could be fixed later. As of October 22, 2026, the REL_18_STABLE code still does the image check before it updates the window.

pgbench runs

We ran the same crash-and-count loop on pgbench data (scale 50, pgbench_accounts at 91,513 pages, 128 MB of shared buffers). "Bulk" updates 5% of the accounts in physical order in one statement. "Random" is 160,000 standard pgbench transactions from 8 clients. Each line is one run:

In the random workload the prefetcher skipped 79,855 blocks for their images and still issued only 1,466 fewer prefetches than with images off. The random runs touched different rows, so the two lines aren't an exact pair, but they agree with the single-page test, where skipping a page's first reference mostly moves the prefetch to its next reference. In the bulk case images cut prefetches by about a fifth.

Crash recovery took between 0.35 and 1.66 seconds in every run, on a laptop SSD with the files still in the OS page cache. Those times say nothing about a standby on a 500 IOPS volume.

Checking your own standby

The counters are cumulative from the last reset, so reset them before a write burst and read them after:

select pg_stat_reset_shared('recovery_prefetch'); -- ... later select prefetch, hit, skip_fpw, skip_rep, io_depth from pg_stat_recovery_prefetch; 

Every prefetch is read-ahead advice for a single 8 kB block. On local NVMe that's cheap, and the default is fine. On a volume with an IOPS cap, a standby whose prefetch count runs close to skip_fpw while replay lag grows is the case to test. We'd turn it off there and watch replay lag through the next burst.

The setting has sighup context: ALTER SYSTEM SET recovery_prefetch = off and SELECT pg_reload_conf() apply it without a restart, which we confirmed in the server log. Changing wal_decode_buffer_size needs a restart; maintenance_io_concurrency doesn't, and 0 disables prefetching too.

If you're planning the move to PostgreSQL 19, check this along with the rest of the upgrade checks: we only read the REL_18_STABLE prefetcher for this article.

Originally published on ImportStatic. This article was drafted with AI assistance; every claim was checked against the sources linked in it and the code samples were compiled and run.