public inbox for [email protected]
help / color / mirror / Atom feedMissing FSM Update when Updating VM On-access
2+ messages / 2 participants
[nested] [flat]
* Missing FSM Update when Updating VM On-access
@ 2026-06-29 22:32 Melanie Plageman <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: Melanie Plageman @ 2026-06-29 22:32 UTC (permalink / raw)
To: PostgreSQL Hackers <[email protected]>; +Cc: Andres Freund <[email protected]>
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:
[text/x-patch] v1-0001-Update-FSM-after-updating-VM-on-access.patch (2.7K, ../../CAAKRu_b2StZrEC=HmW8LePuQbczyFRnfs8qTAJwn_=W76-y24w@mail.gmail.com/2-v1-0001-Update-FSM-after-updating-VM-on-access.patch)
download | inline diff:
From 5b170ea2c43b4ab828e67afc0bc875b1d593ecd6 Mon Sep 17 00:00:00 2001
From: Melanie Plageman <[email protected]>
Date: Mon, 29 Jun 2026 18:24:01 -0400
Subject: [PATCH v1] Update FSM after updating VM on-access
b46e1e54d078de allowed setting the VM while on-access pruning, but it
neglected to update the freespace map. Once the page was all-visible,
vacuum could skip it, leading to stale freespace map values and,
effectively, bloat. Fix it by updating the FSM if we updated the VM.
---
src/backend/access/heap/pruneheap.c | 26 +++++++++++++++++++++++---
1 file changed, 23 insertions(+), 3 deletions(-)
diff --git a/src/backend/access/heap/pruneheap.c b/src/backend/access/heap/pruneheap.c
index fdddd23035b..bcffec3055e 100644
--- a/src/backend/access/heap/pruneheap.c
+++ b/src/backend/access/heap/pruneheap.c
@@ -27,6 +27,7 @@
#include "miscadmin.h"
#include "pgstat.h"
#include "storage/bufmgr.h"
+#include "storage/freespace.h"
#include "utils/rel.h"
#include "utils/snapmgr.h"
@@ -321,6 +322,9 @@ heap_page_prune_opt(Relation relation, Buffer buffer, Buffer *vmbuffer,
if (PageIsFull(page) || PageGetHeapFreeSpace(page) < minfree)
{
+ bool record_free_space = false;
+ Size freespace = 0;
+
/* OK, try to get exclusive buffer lock */
if (!ConditionalLockBufferForCleanup(buffer))
return;
@@ -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.
+ * RecordPageWithFreeSpace() only dirties the FSM when the recorded
+ * free-space category actually changes. Note that vacuum will still
+ * do FreeSpaceMapVacuum() for ranges of pages that are skipped, so we
+ * don't have to worry about that here.
*/
+ if (record_free_space)
+ RecordPageWithFreeSpace(relation, BufferGetBlockNumber(buffer), freespace);
}
}
--
2.43.0
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Missing FSM Update when Updating VM On-access
@ 2026-07-04 18:41 Andrey Borodin <[email protected]>
parent: Melanie Plageman <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Andrey Borodin @ 2026-07-04 18:41 UTC (permalink / raw)
To: Melanie Plageman <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>; Andres Freund <[email protected]>
> On 30 Jun 2026, at 03:32, Melanie Plageman <[email protected]> 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.
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2026-07-04 18:41 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-06-29 22:32 Missing FSM Update when Updating VM On-access Melanie Plageman <[email protected]>
2026-07-04 18:41 ` Andrey Borodin <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox