Re: eliminate xl_heap_visible to reduce WAL (and eventually set VM on-access)

Hi Andres,

On 4/6/26 17:44, Andres Freund wrote:
> I don't have a strong opinion on this, but I think it's pretty defensible to
> record only when there's free space. The whole goal of updating the FSM during
> recovery is to make sure that free space can be found fairly quickly after
> promotion (it's also beneficial in some crash recovery cases, but not that
> much).
> ...
> Obviously the FSM is not crashsafe, so updating it with 0 during replay could
> avoid some unnecessary page reads after a promotion. But I'm not sure that
> that's particularly worth optimizing for.

This makes sense. I'd like just to put some context here: I was checking
the FSM update case in scope of the thread
https://www.postgresql.org/message-id/flat/596c4f1c-f966-4512-b9c9-dd8fbcaf0928(at)postgrespro(dot)ru,
in which I was specifically looking at the case with outdated FSM data
(showing lots of free space) on standby causing a significant
performance hit after switchover. As example this include case with
table having fillfactor<=80 which has prior bulk rows deletes +
insertion. In this case (mostly) empty FSM block may be delivered to
standby via FPI, but subsequent inserts may be lost due to the 20%
heuristic. Moreover, updates to FSM may be lost even for blocks filled
for more than 80% due to missing dirty flag as described in that thread.

In my understanding the FSM update during processing of the
'heap_xlog_visible' function on standby was kind of 'last line of
defense' for any corner case scenario with FSM update (as block would
not be visited by the vacuum process once it's marked as 'all visible')
and it was introduced in
https://www.postgresql.org/message-id/20180802172857.5skoexsilnjvgruk@alvherre.pgsql
(ab7dbd681) specifically for this purpose. Now, as logic of
'heap_xlog_visible' is merged into 'heap_xlog_prune_freeze', so this
task is carried by this function.

I fully agree that having exactly zero-space seems to be a very uncommon
situation (and probably not reproducible with tables having
fillfactor<=80). I've just noted that such case was processed by the old
logic in the 'heap_xlog_visible', while current implementation in
'heap_xlog_prune_freeze' skips it.

Thanks,
Alexey