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

Hi,

On Tue, Mar 24, 2026 at 9:18 AM SATYANARAYANA NARLAPURAM
<satyanarlapuram(at)gmail(dot)com> wrote:
>
> When the WAL prefetcher encounters a block reference that carries a full page image (FPW) or has BKPBLOCK_WILL_INIT set, it correctly skips issuing a prefetch for that block because the old on-disk content is irrelevant since replay will overwrite or zero the page entirely. However, if a later WAL record within the look-ahead window references the same block without an FPW, the prefetcher would still issue a fadvise64 syscall for it, because the block was never recorded in the duplicate-detection window.

I have been looking at this thread for a while, thanks Satya for
pointing me to it.

> Fixed this by making these blocks as recently seen in the FPW and WILL_INIT skip paths.
> This is particularly effective for workloads that produce many sequential writes to the same page (e.g., bulk inserts into heap-only tables), where each page's first post-checkpoint touch generates an FPW and subsequent inserts to the same page follow shortly after in WAL.

I can reproduce the issue, and it does look like a performance
regression. Since recovery_prefetch is effectively on by default, IMHO
this needs to be back-patched.

> In order to further improve the wasted prefetch calls, we can try to increase the window size by changing XLOGPREFETCHER_SEQ_WINDOW_SIZE according to max blocks that can be prefetched or maintain a hash table. I did not attempt to do this in this patch because that can impact the redo performance (more cpu cycles). Worst case, the current fix may fail in scenarios where the table has more than four indexes, for example.
>
> However, I still believe it is an improvement over the baseline. If we decide to spend more cycles on optimizing the window sizes, it can be in a different patch.

I think remembering more blocks than 4 without making the lookup
slower helps in most cases. With indexes, the heap pages push the
index page out of the look-ahead window before its next change, so it
still gets prefetched. I found that [1] discussed the hash table (HT)
approach and it might be worth considering. I played with the HT idea
on a few cases, including the one reported in the recent blog [2]. As
expected, with indexes the v3 patch doesn't fix the regression fully
(case 2 and case 5) , though it works well for the other cases. I
attached the test script and the patch I used for reference.

I think I'm okay with taking the patch proposed here for HEAD and the
back-branches, and discussing the HT approach separately for HEAD,
since there could be cases where it can make lookups slower.

I will spend some more time looking at the v3 patch soon.

Thoughts?

[1] https://www.postgresql.org/message-id/20210214231815.GO27507@tamriel.snowman.net
[2] https://ardentperf.com/2026/10/20/postgres-recovery_prefetch-considered-harmful-and-causing-outages
[3]
network-attached EBS 500 IOPS, read latency 1.9 ms

redo, s prefetches
case off on v3 HT off on v3 HT
1 insert, no index 1.31 52.53 1.34 1.31 0 26667
0 0
2 pgbench, 5% update 0.60 97.05 12.87 0.63 0 54648
6833 69
3 vacuum, dead rows 0.26 51.47 0.26 0.26 0 25864
1 1
4 fillfactor 10 update 0.32 100.96 0.33 0.33 0 50000
0 0
5 4 indexes, 5% update 0.75 97.41 96.72 0.80 0 172165
136442 139
6 pgbench TPC-B 0.30 30.86 0.30 0.30 0 17825
2189 1904
7 FPW off, cold cache 43.24 44.10 44.06 44.22 0 24243
24278 24162
8 FPW off, warm cache 43.55 44.16 45.13 44.98 0 24313
24338 24243

network-attached EBS 3000 IOPS, read latency 0.5 ms

redo, s prefetches
case off on v3 HT off on v3 HT
1 insert, no index 5.25 25.71 5.24 5.24 0 80000
0 0
2 pgbench, 5% update 1.43 37.37 4.47 1.49 0 131150
16395 213
3 vacuum, dead rows 0.69 21.99 0.70 0.71 0 68967
1 1
4 fillfactor 10 update 0.76 39.07 0.78 0.78 0 120000
0 0
5 4 indexes, 5% update 1.81 37.39 37.00 1.89 0 413219
328070 455
6 pgbench TPC-B 0.65 9.93 0.68 0.68 0 45823
13473 13005
7 FPW off, cold cache 25.10 15.03 15.04 15.02 0 61149
61202 61027
8 FPW off, warm cache 25.16 15.01 15.01 15.01 0 61133
61189 60997

local NVMe, read latency 0.1 ms

redo, s prefetches
case off on v3 HT off on v3 HT
1 insert, no index 6.69 7.81 6.69 6.85 0 213334
0 0
2 pgbench, 5% update 4.16 5.88 4.62 4.32 0 371588
46450 665
3 vacuum, dead rows 2.47 3.45 2.52 2.52 0 241381
1 1
4 fillfactor 10 update 2.59 4.02 2.61 2.63 0 400000
0 0
5 4 indexes, 5% update 5.25 7.44 7.24 5.51 0 1163019
926575 1343
6 pgbench TPC-B 1.82 2.37 1.89 1.91 0 131817
48753 47642
7 FPW off, cold cache 20.57 3.59 3.52 4.01 0 176650
176805 176333
8 FPW off, warm cache 20.08 2.98 2.56 3.38 0 176639
176801 176321

--
Bharath Rupireddy
Amazon Web Services: https://aws.amazon.com