Missing FSM Update when Updating VM On-access
A few days ago, during a conversation about another patch, Andres and
I realized that my patch to set the VM on-access could lead to
out-of-date freespace maps. Pages set all-visible in the VM can be
skipped by vacuum, which would then not update the FSM for that page.
The solution to this is to update the FSM in on-access pruning if we
updated the VM.
There is, of course, the possibility of additional overhead here. The
needed freespace map page itself will often be cached, but there is
overhead to pinning it, and, if the figure has changed, to dirtying
it.
Dirtying it isn't a big concern because RecordPageWithFreeSpace() only
dirties the FSM when the freespace category actually changes -- in
which case it's worth it. And it's not WAL-logged, so there isn't much
to writing to it.
I concocted the worst case scenario I could come up with -- a relation
where every single page had to set the VM and needed a FSM update and
the query returns no rows and deforms no tuples. In this case, there
was a few percentage point slowdown due to the extra buffer pinning
and lock acquire/release. (Repro at end of email)
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.
I also thought of caching the pinned FSM page in the scan descriptor
like we do with the VM page. This doesn't work as nicely because each
FSM page covers fewer heap pages. Also, the pinning and unpinning all
happens inside of the FSM API functions.
Therefore, I think the best option is the simplest -- if we set the
page all-visible, also see if we should update the FSM.
Note that this fix is only needed for the primary -- when pruning set
the page all-visible on-access and emitted a WAL record for it, the
standby was already updating the FSM while replaying the prune record
(in heap_xlog_prune_freeze() -> XLogRecordPageWithFreeSpace()).
And, finally, we do not have to worry about vacuuming the FSM
on-access because vacuum will still do it for ranges of total pages --
regardless of what it skipped.
Proposed patch attached. This requires a backpatch to 19.
Repro:
CREATE TABLE t (id int, pad text) WITH (autovacuum_enabled=off);
-- 170000 is ~10,000 pages of 420-byte rows
INSERT INTO t SELECT g, repeat('x',420) FROM generate_series(1,170000);
VACUUM (FREEZE) t;
-- create one removable dead tuple per page by shrinking the first row per page
UPDATE t SET pad = repeat('y',5)
WHERE id IN (SELECT min(id) FROM t GROUP BY (ctid::text::point)[0]);
-- advance the xmin horizon (so previously created dead tuples are
removable on-access)
create table dummy (a int);
-- on-access prune during scan sets every page all-visible and updates FSM
SELECT 1 FROM t OFFSET 10000000;
-- this will show a different number before and after my patch
SELECT sum(avail) FROM pg_freespace('t');
- Melanie
Attachments:
v1-0001-Update-FSM-after-updating-VM-on-access.patchtext/x-patch; charset=US-ASCII; name=v1-0001-Update-FSM-after-updating-VM-on-access.patchDownload+23-4
On 30 Jun 2026, at 03:32, Melanie Plageman <melanieplageman@gmail.com> wrote:
<v1-0001-Update-FSM-after-updating-VM-on-access.patch>
Hi Melanie,
I applied the patch ("Update FSM after updating VM on-access") and it does
what it says. On a single node, on-access pruning now sets the VM and
refreshes the FSM to the post-prune free space, matching what a plain VACUUM
records (in my run summed avail 3200000 -> 7360000, VM 10000|0). On a
primary + streaming standby the FSM comes out identical on both sides after
replay (in this simple test) - i.e. the primary now records what the standby
already writes in heap_xlog_prune_freeze.
A dedicated test would be nice but is difficult here: on-access VM-setting is
nondeterministic (see the enum/plancache flakiness earlier), and even a TAP
test with autovacuum off + forced horizon advancement would likely be
unstable on the buildfarm - so it might even be net negative.
One comment nit: the reserve behavior is preserved (we skip the FSM update
unless the page became newly_all_visible), but the outer comment explaining
*why* we don't update the FSM in the common case is gone; the rationale now
survives only as a parenthetical inside the `if (presult.newly_all_visible)`
block. A one-line note at the point where we *skip* the update would spare a
future reader from wondering why it isn't unconditional.
Thanks for working on this!
Best regards, Andrey Borodin.
Hi,
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.
I also thought of caching the pinned FSM page in the scan descriptor
like we do with the VM page. This doesn't work as nicely because each
FSM page covers fewer heap pages.
I don't think that'd be an issue, an FSM page still covers thousands of heap
pages. I'd guess we'd need maybe a dozen pages or so to make the pin cost
basically invisible.
Also, the pinning and unpinning all happens inside of the FSM API functions.
However that definitely is an issue. I think it'd be good to renovate the FSM
functions, which also would allow us to do things like pinning the FSM page
before a critical section and then updating it as part of the CS, like we
already do for the VM.
The biggest benefit we could get is to avoid a lot of repeated work during
relation extension. We do separate RecordPageWithFreeSpace() calls for each
newly created page, and then a FreeSpaceMapVacuumRange() for all of
them. That's a lot of repeated pinning. And I've seen those accesses be rather
contended in concurrent COPY, so making them more efficient would be rather
nice.
But that's all clearly >19 material.
Therefore, I think the best option is the simplest -- if we set the
page all-visible, also see if we should update the FSM.
Agreed.
@@ -376,16 +380,32 @@ heap_page_prune_opt(Relation relation, Buffer buffer, Buffer *vmbuffer, if (presult.ndeleted > presult.nnewlpdead) pgstat_update_heap_dead_tuples(relation, presult.ndeleted - presult.nnewlpdead); + + /* + * If this prune newly set the page all-visible, VACUUM may later + * skip the page and thus not update its free space map (FSM) + * entry. Keep the FSM from going stale by recording it now. We do + * not want to update the freespace map otherwise (to reserve + * freespace on this page for future updates). + */ + if (presult.newly_all_visible) + { + record_free_space = true; + freespace = PageGetHeapFreeSpace(page); + } }/* And release buffer lock */
LockBuffer(buffer, BUFFER_LOCK_UNLOCK);/*
- * We avoid reuse of any free space created on the page by unrelated
- * UPDATEs/INSERTs by opting to not update the FSM at this point. The
- * free space should be reused by UPDATEs to *this* page.
Does the logic in this removed comment actually make sense to anyone? Because
it sure doesn't to me. I don't even know what the hell "unrelated"
UPDATEs/INSERTs means here. We don't have the slightest idea what's related
and unrelated, assuming that means it's for different tuples than what this
backend is reading the page for.
Sure, in-page updates are good (hot, general efficiency). But we do update the
FSM in other places without this consideration, so using that argument to skip
an FSM update here seems ... bogus.
I could see an argument for not inserting almost full pages into the FSM, but
if we went for that, it'd not be just this place we'd want to do so.
Greetings,
Andres Freund
On Fri, Jul 10, 2026 at 10:49 AM Andres Freund <andres@anarazel.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.
/*
- * We avoid reuse of any free space created on the page by unrelated
- * UPDATEs/INSERTs by opting to not update the FSM at this point. The
- * free space should be reused by UPDATEs to *this* page.Does the logic in this removed comment actually make sense to anyone? Because
it sure doesn't to me. I don't even know what the hell "unrelated"
UPDATEs/INSERTs means here. We don't have the slightest idea what's related
and unrelated, assuming that means it's for different tuples than what this
backend is reading the page for.Sure, in-page updates are good (hot, general efficiency). But we do update the
FSM in other places without this consideration, so using that argument to skip
an FSM update here seems ... bogus.I could see an argument for not inserting almost full pages into the FSM, but
if we went for that, it'd not be just this place we'd want to do so.
That comment was added in 42f9427aa98. I think what it means is if
this HOT pruning is happening during the scan that is part of an
UPDATE that we don't want to report the freespace and risk someone
taking it before we can use it during the HOT update we're about to
do. I don't know if there are other places updating the FSM in the
same situation? I tried to include my interpretation of the spirit of
the comment in my revised comment by saying "We do not update the FSM
[when not setting the VM] to reserve freespace on this page for HOT
updates"
- Melanie
On Sat, Jul 4, 2026 at 2:41 PM Andrey Borodin <x4mmm@yandex-team.ru> wrote:
A dedicated test would be nice but is difficult here: on-access VM-setting is
nondeterministic (see the enum/plancache flakiness earlier), and even a TAP
test with autovacuum off + forced horizon advancement would likely be
unstable on the buildfarm - so it might even be net negative.
Yea, I couldn't come up with a test that would behave.
One comment nit: the reserve behavior is preserved (we skip the FSM update
unless the page became newly_all_visible), but the outer comment explaining
*why* we don't update the FSM in the common case is gone; the rationale now
survives only as a parenthetical inside the `if (presult.newly_all_visible)`
block. A one-line note at the point where we *skip* the update would spare a
future reader from wondering why it isn't unconditional.
I massaged the comments a bit. I originally omitted the other comment
because I was suspicious of it (see Andres' complaint about the
comment upthread). But I think I captured the part of it that we want
to preserve in my revised comments.
Thanks for working on this!
Thanks for taking a look!
- Melanie
On Fri, Jul 10, 2026 at 6:35 PM Melanie Plageman
<melanieplageman@gmail.com> wrote:
On Fri, Jul 10, 2026 at 10:49 AM Andres Freund <andres@anarazel.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