Re: Skip prefetch for block references that follow a FPW or WILL_INIT of the same block

Hi,

I'd like to add a production data point in support of this patch. We hit
exactly this pattern on PostgreSQL 18, and on a synchronous standby the
effect was user-visible.

Setup: physical streaming replica in synchronous mode; the application
sets synchronous_commit = remote_apply inside its transactions.
recovery_prefetch = try, maintenance_io_concurrency = 16, data checksums
on, wal_log_hints = on, wal_compression = on, checkpoint_timeout = 30min,
shared_buffers = 96GB. The standby data directory is on a cloud block
volume provisioned at 13,000 IOPS.

What happened: autovacuum of a large TOAST table (1.7 TB) reached its
second heap pass. For about 20 seconds of primary time it wrote roughly
1.5 GB of WAL that consisted almost entirely of the following pair, once
per heap page:

rmgr: Heap2 ... desc: PRUNE_VACUUM_CLEANUP , isCatalogRel: F, blkref
#0: rel 1663/13642/18355 blk 198108 FPW
rmgr: Heap2 ... desc: VISIBLE snapshotConflictHorizon: 0, flags:
0x03, blkref #0: rel 1663/13642/18355 fork vm blk 6, blkref #1: rel
1663/13642/18355 blk 198108

pg_waldump over that range (97 segments):

Heap2/PRUNE_VACUUM_CLEANUP 1,090,244 records, 1,036,493 of them with FPW
Heap2/VISIBLE 1,090,244 records, heap block never with FPW
distinct heap blocks referenced without an image: 1,090,293

The TOAST pages were cold, so each of those VISIBLE references produced a
real 8 kB read from posix_fadvise for a page that the preceding FPI then
overwrote. On the standby we observed:

- data volume reads jumped from ~90 to 9,500-9,900 IOPS (8 kB, ~80 MB/s);
reads + background writes sat at the volume's IOPS limit;
- r_await went from 0.33 ms to 2.3 ms, queue depth ~31;
- the startup process spent ~90% of its time in the DataFilePrefetch wait
event (sampled with pg_wait_sampling);
- replay lag grew to 3.5 GB / 43 s, while flush lag stayed under 300 kB;
- about 1.1M reads in total, matching the 1.09M blocks above.

Those 20 seconds of WAL took about two minutes to replay. Because the
application commits with remote_apply, commits on the primary waited
behind replay. During those 80 seconds we logged ~780 client-cancelled
"canceling wait for synchronous replication" warnings and a pile-up of
row-lock waiters.

A second, independent standby that replays the same WAL from the archive
via restore_command slowed down on exactly the same segment range: from
about 6 segments/s to about 0.6 segments/s. That rules out anything
specific to the first replica's cache or host. Other autovacuum runs that
day, whose cleanup records did not carry FPIs, replayed without delay.

As far as I can tell from REL_18_STABLE, the has_image branch in
XLogPrefetcherNextBlock() returns before recent_block[] is updated, which
is exactly what this patch changes. Michael Zhilin reported the same
behaviour in March 2025 ("Avoid useless prefetches in case of recent FPI
WAL records").

We can work around it by throttling autovacuum on that table or by
turning recovery_prefetch off. The latter costs real read-ahead elsewhere,
for example index vacuum records without FPIs on a cold cache. So the fix
itself would be very welcome.

Given that the effect on a remote_apply standby is commit stalls on the
primary rather than just slower recovery, would back-patching be
considered? Either way, +1 for committing it.

We still have the archived WAL for the affected window and may be able to
replay it with a patched build if that would help.

Thanks,
Valerii Degtiarev