MIME-Version: 1.0
From: Melanie Plageman <melanieplageman@gmail.com>
Date: Mon, 29 Jun 2026 18:32:47 -0400
Message-ID: 
 <CAAKRu_b2StZrEC=HmW8LePuQbczyFRnfs8qTAJwn_=W76-y24w@mail.gmail.com>
Subject: Missing FSM Update when Updating VM On-access
To: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Cc: Andres Freund <andres@anarazel.de>
Content-Type: multipart/mixed; boundary="0000000000005e7a8206556c0aa7"
Archived-At: 
 <https://www.postgresql.org/message-id/CAAKRu_b2StZrEC%3DHmW8LePuQbczyFRnfs8qTAJwn_%3DW76-y24w%40mail.gmail.com>
Precedence: bulk

--0000000000005e7a8206556c0aa7
Content-Type: text/plain; charset="UTF-8"

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

--0000000000005e7a8206556c0aa7
Content-Type: text/x-patch; charset="US-ASCII";
	name="v1-0001-Update-FSM-after-updating-VM-on-access.patch"
Content-Disposition: attachment;
	filename="v1-0001-Update-FSM-after-updating-VM-on-access.patch"
Content-Transfer-Encoding: base64
Content-ID: <f_mqzsm4k90>
X-Attachment-Id: f_mqzsm4k90

RnJvbSA1YjE3MGVhMmM0M2I0YWI4MjhlNjdhZmMwYmM4NzViMWQ1OTNlY2Q2IE1vbiBTZXAgMTcg
MDA6MDA6MDAgMjAwMQpGcm9tOiBNZWxhbmllIFBsYWdlbWFuIDxtZWxhbmllcGxhZ2VtYW5AZ21h
aWwuY29tPgpEYXRlOiBNb24sIDI5IEp1biAyMDI2IDE4OjI0OjAxIC0wNDAwClN1YmplY3Q6IFtQ
QVRDSCB2MV0gVXBkYXRlIEZTTSBhZnRlciB1cGRhdGluZyBWTSBvbi1hY2Nlc3MKCmI0NmUxZTU0
ZDA3OGRlIGFsbG93ZWQgc2V0dGluZyB0aGUgVk0gd2hpbGUgb24tYWNjZXNzIHBydW5pbmcsIGJ1
dCBpdApuZWdsZWN0ZWQgdG8gdXBkYXRlIHRoZSBmcmVlc3BhY2UgbWFwLiBPbmNlIHRoZSBwYWdl
IHdhcyBhbGwtdmlzaWJsZSwKdmFjdXVtIGNvdWxkIHNraXAgaXQsIGxlYWRpbmcgdG8gc3RhbGUg
ZnJlZXNwYWNlIG1hcCB2YWx1ZXMgYW5kLAplZmZlY3RpdmVseSwgYmxvYXQuIEZpeCBpdCBieSB1
cGRhdGluZyB0aGUgRlNNIGlmIHdlIHVwZGF0ZWQgdGhlIFZNLgotLS0KIHNyYy9iYWNrZW5kL2Fj
Y2Vzcy9oZWFwL3BydW5laGVhcC5jIHwgMjYgKysrKysrKysrKysrKysrKysrKysrKystLS0KIDEg
ZmlsZSBjaGFuZ2VkLCAyMyBpbnNlcnRpb25zKCspLCAzIGRlbGV0aW9ucygtKQoKZGlmZiAtLWdp
dCBhL3NyYy9iYWNrZW5kL2FjY2Vzcy9oZWFwL3BydW5laGVhcC5jIGIvc3JjL2JhY2tlbmQvYWNj
ZXNzL2hlYXAvcHJ1bmVoZWFwLmMKaW5kZXggZmRkZGQyMzAzNWIuLmJjZmZlYzMwNTVlIDEwMDY0
NAotLS0gYS9zcmMvYmFja2VuZC9hY2Nlc3MvaGVhcC9wcnVuZWhlYXAuYworKysgYi9zcmMvYmFj
a2VuZC9hY2Nlc3MvaGVhcC9wcnVuZWhlYXAuYwpAQCAtMjcsNiArMjcsNyBAQAogI2luY2x1ZGUg
Im1pc2NhZG1pbi5oIgogI2luY2x1ZGUgInBnc3RhdC5oIgogI2luY2x1ZGUgInN0b3JhZ2UvYnVm
bWdyLmgiCisjaW5jbHVkZSAic3RvcmFnZS9mcmVlc3BhY2UuaCIKICNpbmNsdWRlICJ1dGlscy9y
ZWwuaCIKICNpbmNsdWRlICJ1dGlscy9zbmFwbWdyLmgiCiAKQEAgLTMyMSw2ICszMjIsOSBAQCBo
ZWFwX3BhZ2VfcHJ1bmVfb3B0KFJlbGF0aW9uIHJlbGF0aW9uLCBCdWZmZXIgYnVmZmVyLCBCdWZm
ZXIgKnZtYnVmZmVyLAogCiAJaWYgKFBhZ2VJc0Z1bGwocGFnZSkgfHwgUGFnZUdldEhlYXBGcmVl
U3BhY2UocGFnZSkgPCBtaW5mcmVlKQogCXsKKwkJYm9vbAkJcmVjb3JkX2ZyZWVfc3BhY2UgPSBm
YWxzZTsKKwkJU2l6ZQkJZnJlZXNwYWNlID0gMDsKKwogCQkvKiBPSywgdHJ5IHRvIGdldCBleGNs
dXNpdmUgYnVmZmVyIGxvY2sgKi8KIAkJaWYgKCFDb25kaXRpb25hbExvY2tCdWZmZXJGb3JDbGVh
bnVwKGJ1ZmZlcikpCiAJCQlyZXR1cm47CkBAIC0zNzYsMTYgKzM4MCwzMiBAQCBoZWFwX3BhZ2Vf
cHJ1bmVfb3B0KFJlbGF0aW9uIHJlbGF0aW9uLCBCdWZmZXIgYnVmZmVyLCBCdWZmZXIgKnZtYnVm
ZmVyLAogCQkJaWYgKHByZXN1bHQubmRlbGV0ZWQgPiBwcmVzdWx0Lm5uZXdscGRlYWQpCiAJCQkJ
cGdzdGF0X3VwZGF0ZV9oZWFwX2RlYWRfdHVwbGVzKHJlbGF0aW9uLAogCQkJCQkJCQkJCQkgICBw
cmVzdWx0Lm5kZWxldGVkIC0gcHJlc3VsdC5ubmV3bHBkZWFkKTsKKworCQkJLyoKKwkJCSAqIElm
IHRoaXMgcHJ1bmUgbmV3bHkgc2V0IHRoZSBwYWdlIGFsbC12aXNpYmxlLCBWQUNVVU0gbWF5IGxh
dGVyCisJCQkgKiBza2lwIHRoZSBwYWdlIGFuZCB0aHVzIG5vdCB1cGRhdGUgaXRzIGZyZWUgc3Bh
Y2UgbWFwIChGU00pCisJCQkgKiBlbnRyeS4gS2VlcCB0aGUgRlNNIGZyb20gZ29pbmcgc3RhbGUg
YnkgcmVjb3JkaW5nIGl0IG5vdy4gV2UgZG8KKwkJCSAqIG5vdCB3YW50IHRvIHVwZGF0ZSB0aGUg
ZnJlZXNwYWNlIG1hcCBvdGhlcndpc2UgKHRvIHJlc2VydmUKKwkJCSAqIGZyZWVzcGFjZSBvbiB0
aGlzIHBhZ2UgZm9yIGZ1dHVyZSB1cGRhdGVzKS4KKwkJCSAqLworCQkJaWYgKHByZXN1bHQubmV3
bHlfYWxsX3Zpc2libGUpCisJCQl7CisJCQkJcmVjb3JkX2ZyZWVfc3BhY2UgPSB0cnVlOworCQkJ
CWZyZWVzcGFjZSA9IFBhZ2VHZXRIZWFwRnJlZVNwYWNlKHBhZ2UpOworCQkJfQogCQl9CiAKIAkJ
LyogQW5kIHJlbGVhc2UgYnVmZmVyIGxvY2sgKi8KIAkJTG9ja0J1ZmZlcihidWZmZXIsIEJVRkZF
Ul9MT0NLX1VOTE9DSyk7CiAKIAkJLyoKLQkJICogV2UgYXZvaWQgcmV1c2Ugb2YgYW55IGZyZWUg
c3BhY2UgY3JlYXRlZCBvbiB0aGUgcGFnZSBieSB1bnJlbGF0ZWQKLQkJICogVVBEQVRFcy9JTlNF
UlRzIGJ5IG9wdGluZyB0byBub3QgdXBkYXRlIHRoZSBGU00gYXQgdGhpcyBwb2ludC4gIFRoZQot
CQkgKiBmcmVlIHNwYWNlIHNob3VsZCBiZSByZXVzZWQgYnkgVVBEQVRFcyB0byAqdGhpcyogcGFn
ZS4KKwkJICogUmVjb3JkUGFnZVdpdGhGcmVlU3BhY2UoKSBvbmx5IGRpcnRpZXMgdGhlIEZTTSB3
aGVuIHRoZSByZWNvcmRlZAorCQkgKiBmcmVlLXNwYWNlIGNhdGVnb3J5IGFjdHVhbGx5IGNoYW5n
ZXMuIE5vdGUgdGhhdCB2YWN1dW0gd2lsbCBzdGlsbAorCQkgKiBkbyBGcmVlU3BhY2VNYXBWYWN1
dW0oKSBmb3IgcmFuZ2VzIG9mIHBhZ2VzIHRoYXQgYXJlIHNraXBwZWQsIHNvIHdlCisJCSAqIGRv
bid0IGhhdmUgdG8gd29ycnkgYWJvdXQgdGhhdCBoZXJlLgogCQkgKi8KKwkJaWYgKHJlY29yZF9m
cmVlX3NwYWNlKQorCQkJUmVjb3JkUGFnZVdpdGhGcmVlU3BhY2UocmVsYXRpb24sIEJ1ZmZlckdl
dEJsb2NrTnVtYmVyKGJ1ZmZlciksIGZyZWVzcGFjZSk7CiAJfQogfQogCi0tIAoyLjQzLjAKCg==
--0000000000005e7a8206556c0aa7--