Re: Missing FSM Update when Updating VM On-access

On Fri, Jul 10, 2026 at 6:35 PM Melanie Plageman
<melanieplageman(at)gmail(dot)com> wrote:
>
> On Fri, Jul 10, 2026 at 10:49 AM Andres Freund <andres(at)anarazel(dot)de> wrote:
> >
> > On 2026-06-29 18:32:47 -0400, Melanie Plageman wrote:
> > > I tried to think of some heuristics so that we could limit when we did
> > > the FSM pinning and locking, but none seemed very good. We could check
> > > if the new amount of free space is bigger than an FSM category step
> > > (32), but that doesn't help us if we are correcting an FSM
> > > overestimation. This could happen because inserts don't update the FSM
> > > until the inserting tuple doesn't fit on the target page.
> >
> > One potential heuristic would be to skip trying to update the FSM if the page
> > is considered full and we didn't remove any tuples (i.e. just marked it as
> > all-visible). Skipping the FSM update in that case would, at worst, lead to
> > unnecessarily visiting the page during a future insert (finding it to be full
> > when doing so). We could additionally make it depend on whether PD_PAGE_FULL
> > is set (in which case we presumably already updated the FSM).
> >
> > The case where that would potentially help is when doing the first scan
> > through a freshly bulk loaded table.
> >
> > However, compared to the other costs in that case, I have a hard time
> > believing the FSM access is that bad. And given the point in the release
> > cycle, I think going for simpler is the way to go.
>
> Yea, that's a good heuristic. I pushed the fix without the heuristic
> to keep it simple. But then I started thinking about it more, and I'm
> second-guessing that decision. I'll create a repro for the case you
> mentioned and see if there is a performance difference before
> deciding.

I did some performance testing of bulkloading data and then doing an
on-access query that sets the VM after. When the on-access query was
purposefully the most low-overhead possible (SELECT 1 FROM t OFFSET
1000000000), I did see about a 2% impact of accessing the FSM on every
page. However, as soon as I turned that query into a count(*), the
impact disappeared.

This would be the diff

diff --git a/src/backend/access/heap/pruneheap.c
b/src/backend/access/heap/pruneheap.c
index 6f3ba9113b5..bd45281af39 100644
--- a/src/backend/access/heap/pruneheap.c
+++ b/src/backend/access/heap/pruneheap.c
@@ -388,10 +388,11 @@ heap_page_prune_opt(Relation relation, Buffer
buffer, Buffer *vmbuffer,
* want to update the freespace map otherwise, to reserve
* freespace on this page for HOT updates.
*/
- if (presult.newly_all_visible)
+ if (presult.newly_all_visible )
{
- record_free_space = true;
freespace = PageGetHeapFreeSpace(page);
+ if (presult.ndeleted > 0 || freespace >= minfree)
+ record_free_space = true;
}
}

I can't decide if it is worth doing for that small percentage
performance difference. I suppose you could have cases where there is
some contention for the FSM map page and then the perf difference
would be worse than it is here. I'm interested if there are cases
folks can think of where this heuristic would cause more negative
consequences than just one needless insert visit.
Either way, I won't push this today since there is a freeze on 19 stable branch.

- Melanie