On Tue, Dec 2, 2025 at 2:39 PM Heikki Linnakangas <[email protected]> wrote: > > A prune record can have XLogRecord->xl_xid == InvalidTransactionId, if > the transaction hasn't been assigned a transaction ID yet. I think > ReadNextTransactionId() - 1 would work. (Using TransactionIdRetreat > rather than plain - 1, of course)
I was thinking about this some more, and I propose we just clear pd_prune_xid whenever we set PD_ALL_VISIBLE like in the attached patch. AFAICT we weren't clearing it when setting the VM in vacuum phase III -- which meant it would have a stale value on a normal Postgres primary (not a promoted standby). And we weren't initializing pd_prune_xid to InvalidTransactionId in COPY FREEZE (though perhaps the page will be zero-initialized and thus pd_prune_xid will read as InvalidTransactionId anyway). So, this patch covers those cases plus clears pd_prune_xid on redo. As previously discussed, this saves prune cycles after promotion. I take your point that it could be worth setting pd_prune_xid to something on the standby when there are prunable tuples left so that we trigger a prune cycle after promotion, but since Andres didn't like that idea and we'll update pd_prune_xid on the next delete/update anyway, I think we don't need to do it. Regarding Andres' idea to include pd_prune_xid to the xl_heap_prune WAL record: I don't feel quite comfortable that pd_prune_xid would then be half WAL-logged (since it isn't logged when update/delete sets it). Maybe it's fine, though. Anyway, I think my attached patch is an improvement with no risk - Melanie
From 3291d70db1523fca0fb9226543fb02e647d675f2 Mon Sep 17 00:00:00 2001 From: Melanie Plageman <[email protected]> Date: Mon, 23 Feb 2026 18:13:27 -0500 Subject: [PATCH v0 01/17] Save prune cycles by consistently clearing prune hints on all-visible pages All-visible pages can't contain prunable tuples. We already clear the prune hint (pd_prune_xid) during pruning of all-visible pages, but we were not doing so in vacuum phase three, nor initializing it for all-frozen pages created by COPY FREEZE, and we were not clearing it on standbys. Because page hints are not WAL-logged, pages on a standby carry stale pd_prune_xid values. After promotion, that stale hint triggers unnecessary on-access pruning. Fix this by clearing the prune hint everywhere we currently mark a heap page all-visible. Clearing it when setting PD_ALL_VISIBLE ensures no extra overhead. --- src/backend/access/heap/heapam.c | 1 + src/backend/access/heap/heapam_xlog.c | 7 +++++++ src/backend/access/heap/vacuumlazy.c | 2 ++ 3 files changed, 10 insertions(+) diff --git a/src/backend/access/heap/heapam.c b/src/backend/access/heap/heapam.c index 98d53caeea8..d1e9eb3e2ca 100644 --- a/src/backend/access/heap/heapam.c +++ b/src/backend/access/heap/heapam.c @@ -2577,6 +2577,7 @@ heap_multi_insert(Relation relation, TupleTableSlot **slots, int ntuples, else if (all_frozen_set) { PageSetAllVisible(page); + PageClearPrunable(page); visibilitymap_set_vmbits(BufferGetBlockNumber(buffer), vmbuffer, VISIBILITYMAP_ALL_VISIBLE | diff --git a/src/backend/access/heap/heapam_xlog.c b/src/backend/access/heap/heapam_xlog.c index f765345e9e4..6d39a5fff7c 100644 --- a/src/backend/access/heap/heapam_xlog.c +++ b/src/backend/access/heap/heapam_xlog.c @@ -164,7 +164,10 @@ heap_xlog_prune_freeze(XLogReaderState *record) * modification would fail to clear the visibility map bit. */ if (vmflags & VISIBILITYMAP_VALID_BITS) + { PageSetAllVisible(page); + PageClearPrunable(page); + } MarkBufferDirty(buffer); @@ -305,6 +308,7 @@ heap_xlog_visible(XLogReaderState *record) page = BufferGetPage(buffer); PageSetAllVisible(page); + PageClearPrunable(page); if (XLogHintBitIsNeeded()) PageSetLSN(page, lsn); @@ -734,7 +738,10 @@ heap_xlog_multi_insert(XLogReaderState *record) /* XLH_INSERT_ALL_FROZEN_SET implies that all tuples are visible */ if (xlrec->flags & XLH_INSERT_ALL_FROZEN_SET) + { PageSetAllVisible(page); + PageClearPrunable(page); + } MarkBufferDirty(buffer); } diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c index dcbaf49401b..88bcbf55ed7 100644 --- a/src/backend/access/heap/vacuumlazy.c +++ b/src/backend/access/heap/vacuumlazy.c @@ -1932,6 +1932,7 @@ lazy_scan_new_or_empty(LVRelState *vacrel, Buffer buf, BlockNumber blkno, log_newpage_buffer(buf, true); PageSetAllVisible(page); + PageClearPrunable(page); visibilitymap_set(vacrel->rel, blkno, buf, InvalidXLogRecPtr, vmbuffer, InvalidTransactionId, @@ -2943,6 +2944,7 @@ lazy_vacuum_heap_page(LVRelState *vacrel, BlockNumber blkno, Buffer buffer, * set PD_ALL_VISIBLE. */ PageSetAllVisible(page); + PageClearPrunable(page); visibilitymap_set_vmbits(blkno, vmbuffer, vmflags, vacrel->rel->rd_locator); -- 2.43.0
