improving handling of FPIs during recovery prefetching

Hi,

A couple days back I happened to notice a blog post [1] by Jeremy
Schneider, describing how recovery_prefetch can trigger an outage. I
happened to be involved in getting that feature into PG 15, so I was
curious how it could trigger such failures.

After discussing the details with Jeremy over the weekend, I believe
it's really a combination of two issues.

1) recovery_prefetch fails to skip prefetches after an FPI, which may
significantly increase the number of IO operations, to the point that
the replica runs into disk limits etc. This inflates the replay lag.

2) replication behavior after walreceiver exit - In streaming mode,
syncrep waits only for confirmation the WAL was flushed, so it works
(mostly) OK even if the replay can't keep up. But if the walreceiver
disconnects, the replication startup starts to apply WAL - which may be
much slower due to the FPI amplification described in (1). Only when
that apply of all the received WAL completes will it start streaming
again, and confirming commits on the primary.

AFAICS the real issue is (2), but it's not great the prefetching can
regress the replay like this. That seems unfortunate even ignoring the
syncrep aspect, as it may increase replay lag on read replicas etc.

As pointed out in the blog post, Stephen mentioned FPIs [2] back in
2021, but we've all been concerned mostly about syscall overhead and
buffer lookups. And the conclusion was that it's fine for v1, negligible
relative to the benefits, and that if it turns out to be measurable
we'll improve it.

But as Jeremy demonstrates in his blog post, the actual issue is in
issuing unnecessary I/O, saturating the device IOPS limit. In the
reproducer, this is done by

UPDATE pgbench_accounts SET abalance = abalance + 1 WHERE aid % 20 = 0

which effectively updates all pages of the table - multiple tuples on
each. The first modification generates an FPI, and then there's a bunch
of "non-FPI" changes. The prefetcher skips the FPI, but then still
issues prefetches for that block due to the non-FPI changes. In reality
no reads are necessary - replaying the FPI will install the buffer.

The blog claims 16x IOPS amplification. I believe that's simply due to
the UPDATE benefiting from read-ahead on the primary (reading data in
128K chunks), but standby dealing with 8K pages (because that's what we
get in WAL). That's the same volume, but 16x more IOPS.

There's a tiny cache of recently prefetched blocks, but it can't really
help in this case - FPIs are not added to it, and even if they were it's
way too small (just 4 entries). I kinda suspect it's not large enough in
general, but that's a separate question.

We actually got a report [3] describing this issue about 18 months ago,
including a patch leveraging the XLOGPREFETCHER_SEQ_WINDOW_SIZE bit I
mentioned above. That probably works for the reported example, but it's
not really a proper fix - the default size "4" is way too low for FPIs.

But I used it as a starting point, and after a bit of Sunday hacking, I
got what I think is a much more complete fix. I'm attaching the whole
series, as it's easier to understand the changes like that, but in the
end it'd be squashed into a single commit IO think.

I tried to explain the changes in commit messages, so only a very brief
overview here:

0001-Avoid-useless-prefetches-in-case-of-recent-FPI-WA.patch
---------------------------------------------------------------
Pretty much Michael's patch [3] from 2025, no other changes.

0002-Separate-the-FPIs-from-recently-prefetched-blocks.patch
---------------------------------------------------------------
Separates the FPI tracking from recent_block, into a separate array with
more entries (but still small, with linear search).

0003-Rework-the-FPI-cache-as-a-set-associative-cache.patch
---------------------------------------------------------------
Replace the array / linear search with a set-associative cache, inspired
by the cache used in fast-path locking. It seems perfect, with minimal
overhead, and it's OK to handle overflows by just forgetting old FPIs.

0004-Move-FPI-tracking-into-separate-functions.patch
---------------------------------------------------------------
Refactoring, moving code into functions to improve readability. Should
squash into 0003.

0005-Use-a-proper-hash-function-for-FPI-history.patch
---------------------------------------------------------------
Replace a stupid "hash function" mapping blocks to the set-associative
cache with a much better one. Should squash into 0003.

0006-Ignore-replayed-FPIs-in-prefetch-FPI-history.patch
---------------------------------------------------------------
At some point I realized the FPI entries become irrelevant once we
replay the record. We have the LSN already, and we can just treat the
entry as empty if the LSN is before replaing_lsn. It works more or less
as an eviction. This way we won't have "stale entries", causing
synchronous reads.

0007-Avoid-duplicate-entries-in-FPI-history.patch
---------------------------------------------------------------
There's no point in tracking multiple FPIs for the same block, it's
enough to track the new one. It's not very common, of course. But it can
happen, and it's not more expensive.

0008-Size-recovery-FPI-history-by-the-look-ahead-dista.patch
---------------------------------------------------------------
Until now the size of the FPI history was hard-coded, but we can size it
based on the prefetch distance, to minimize "premature eviction".

The cache is larger (up to ~400kB for maintenance_io_concurrency=1000),
but there can only be one such cache in the walreceiver (and instance).
Realistic values are a fraction of that.

0009-Skip-FPI-lookups-when-no-FPIs-are-pending.patch
---------------------------------------------------------------
An optimization - a quick exit when there are no outstanding FPIs (which
can happen close to the end of a checkpoint, etc).

regards

[1]
https://ardentperf.com/2026/10/20/postgres-recovery_prefetch-considered-harmful-and-causing-outages/

[2]
https://www.postgresql.org/message-id/20210214231815.GO27507@tamriel.snowman.net

[3]
https://www.postgresql.org/message-id/9bdb428a-fc8e-4801-8fff-1ad0bc6fa76b%40postgrespro.ru

--
Tomas Vondra