Re: PoC: Compute a histogram of prune_xid to support autovacuum improvements

On Tue, Jun 3, 2025 at 12:40 PM Renan Alves Fonseca
<renanfonseca(at)gmail(dot)com> 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