On Tue, Jun 3, 2025 at 12:40 PM Renan Alves Fonseca <[email protected]> wrote: > > in the scope of improving the autovacuum algorithm, this patch proposes > to maintain a histogram of *smallest prunable xid per page* for each > relation. It allows to estimate the number of pages that would > be pruned by vacuum for a given cutoff.
Thanks for working on this, Renan. It is quite a large patch set, which makes sense because we do not have histogram types in any of the shared memory stats right now. I think if we want to go the route of adding a histogram, we might want to make it general purpose enough to use for other types of stats. In fact there is nothing about your PruneXidHistogram struct which has to be related to prune xids. Before doing that, I was wondering if there was a way to make incremental progress by adding and maintaining the oldest prune xid. It would mean adding code in the same places (heap_update/delete/etc). In heap_page_prune_and_freeze(), we would have to keep track of the oldest new prune xid given that we will probably be pruning away the previous oldest one. Then when vacuuming, we could consider skipping vacuuming the relation if the oldest prunable xid is newer than OldestXmin (unless we thought we'd be able to freeze tuples). I know your patch doesn't use the histograms now in vacuum decisions (only displays them). But maybe it is worth approaching from the opposite direction and use a coarser heuristic first and then make it more detailed. - Melanie
