On Mon, Apr 6, 2026 at 11:32 AM Alexey Makhmutov <[email protected]> wrote: > > 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/[email protected], > 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/[email protected] > (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.
The scenario causing inaccurate freespace maps after promotion is technically possible though improbable. Moreover, I don't see a downside to changing it. Patch I plan to commit is attached. I don't quite understand why heap_xlog_insert() considers whether the heap page was an FPI before updating the FSM though. I know we need some heuristic to avoid doing it for every record, but the FPI consideration doesn't make sense to me. - Melanie
From eb9403676d61c32f45139ef6559366182afd608f Mon Sep 17 00:00:00 2001 From: Melanie Plageman <[email protected]> Date: Mon, 13 Apr 2026 11:50:32 -0400 Subject: [PATCH] Update FSM when updating VM even if freespace is zero add323da40a started updating the visibility map in the same WAL record as pruning and freezing. This included updating the freespace map during replay, which we've done since ab7dbd681. add323da40a, however, conditioned doing so on there being > 0 freespace, which differed from the previous state. The FSM is not WAL-logged and is instead updated heuristically on standbys. In rare cases, if the FSM is not updated while replaying inserts, there is 0 freespace, and vacuum replay doesn't update the FSM when setting the page all-visible/all-frozen, after the standby is promoted and runs vacuum, it may skip those pages and then propagate overly optimistic numbers up the FSM, causing slowness when searching for freespace for new tuples. Fix it by always updating the FSM when replaying setting the VM. Author: Melanie Plageman <[email protected]> Reported-by: Alexey Makhmutov <[email protected]> Discussion: https://postgr.es/m/ead2f110-c736-48f5-99e1-023dc9acbf0b%40postgrespro.ru --- src/backend/access/heap/heapam_xlog.c | 6 +++++- 1 file changed, 5 insertions(+), 1 deletion(-) diff --git a/src/backend/access/heap/heapam_xlog.c b/src/backend/access/heap/heapam_xlog.c index f3f419d3dc1..9ed7024e814 100644 --- a/src/backend/access/heap/heapam_xlog.c +++ b/src/backend/access/heap/heapam_xlog.c @@ -38,6 +38,7 @@ heap_xlog_prune_freeze(XLogReaderState *record) Buffer vmbuffer = InvalidBuffer; uint8 vmflags = 0; Size freespace = 0; + bool do_update_fsm = false; XLogRecGetBlockTag(record, 0, &rlocator, NULL, &blkno); memcpy(&xlrec, maindataptr, SizeOfHeapPrune); @@ -211,7 +212,10 @@ heap_xlog_prune_freeze(XLogReaderState *record) XLHP_HAS_DEAD_ITEMS | XLHP_HAS_NOW_UNUSED_ITEMS)) || (vmflags & VISIBILITYMAP_VALID_BITS)) + { freespace = PageGetHeapFreeSpace(BufferGetPage(buffer)); + do_update_fsm = true; + } /* * We want to avoid holding an exclusive lock on the heap buffer while @@ -248,7 +252,7 @@ heap_xlog_prune_freeze(XLogReaderState *record) if (BufferIsValid(vmbuffer)) UnlockReleaseBuffer(vmbuffer); - if (freespace > 0) + if (do_update_fsm) XLogRecordPageWithFreeSpace(rlocator, blkno, freespace); } -- 2.43.0
