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

+Tomas

Tomas and I chatted a bit over the weekend and he just replied to a
different thread on this same topic

https://www.postgresql.org/message-id/b39398cc-fa51-45c6-b467-57537e4b5da1%40vondra.me

On the primary, even on v16 and older without io_uring and the read_stream
API, I suspect postgres is making calls to the OS that allow the linux
kernel to detect sequential reads and perform OS readahead.

I haven't spent much time with Linux kernel source code but AI is telling
me that POSIX_FADV_WILLNEED (used in fd.c FilePrefetch) explicitly does not
work with Linux OS level read-ahead.

Would be great to have input from someone with more linux experience. AI
suggests the do_page_cache_ra() internal kernel function is key. I see that
its third param is lookahead size. WILLNEED might always be
calling ondemand_readahead() and that function might always
call do_page_cache_ra with a lookahead of zero, explicitly disabling
lookahead on prefetch IO.

/*
* standalone, small random read
* Read as is, and do not pollute the readahead state.
*/
do_page_cache_ra(ractl, req_size, 0);

I dont think any of the current postgres prefetch patches quite address
this.

Skipping prefetch after an FPW mostly neuters the replica prefetch as long
as people are running with defaults of FPI enabled and 5-minute
checkpoint. While it's not a good idea to crank checkpoint up to large
values like 45+ minutes, that's the situation where replica prefetch would
begin to start impacting things again. But if replica prefetch disables OS
readahead, then Linux might actually be more performant with prefetch
turned off in cases with sequential IO - namely vacuum and bulk updates.

Vacuum is the more concerning case here, as large vacuums would
consistently cause the 16x IOPS amplification on replicas. Physical hard
disks are less of a problem - but cloud disks are often metered and
throttled on IOPS, so if replicas need 16x more IOPS than primaries then
it's easy to see lag being a common occurrence. I tested VACUUM with
pgbench --init-steps=dtGp to load unfrozen rows, then VACUUM (FREEZE)
pgbench_accounts instead of the UPDATE.

https://github.com/ardentperf-labs/walreceiver-outage-repro/blob/main/variations/README.md#vacuum-instead-of-5-update

Might be worth some testing to see if OS readahead is happening on replicas
when prefetch is disabled - I didnt check this yet.

-Jeremy

On Sun, Oct 25, 2026 at 8:56 AM shihao zhong <zhong950419(at)gmail(dot)com> wrote:

> Hi Bharath,
>
> Thanks for the numbers. Elapsed time was noisy on my laptop, so I
> counted instructions during redo on a release build.
>
> With indexes it works as you said. I replayed pgbench with three
> extra indexes. Master ran 12.1G instructions and v3 ran 11.7G. The
> hash table ran 8.5G, close to 8.2G with prefetch off.
>
> Without fpw, the hash table ran 3.5%(37.79G vs 36.50G) more instructions
> than v3. Most of the extra cost is hashing. On a miss the slot is
> computed twice, once in IsRecent and once in AddRecent. When I
> computed it once, the extra cost fell to 2.8%.
>
> Thanks,
> Shihao
>
>
>

--
http://about.me/jeremy_schneider