Re: Autovacuum freeze scores wrap around for just-vacuumed tables

Hi,

On Wed, Nov 11, 2026 at 6:47 AM Baji Shaik <baji(dot)pgdev(at)gmail(dot)com> wrote:
>
> relation_needs_vacanalyze() computes the freeze score ages as
> recentXid - relfrozenxid and recentMulti - relminmxid, but both are
> read before pg_class is scanned. If a table's relfrozenxid or
> relminmxid advances in between, for example by a concurrent VACUUM or
> a table rewrite, the subtraction wraps to about 4 billion and the
> just-vacuumed table gets a score around 1e44, which can put it at the
> front of autovacuum's list.

Yes, your finding is correct. It depends on the window between when
the recent xids were read and the catalog scan happened, and in that
window if others could move the relfrozenxid or relminmxid ahead. And,
the window could be bigger with more number of relations in the
database.

> The window grows with the number of
> relations; with a few thousand tables, pg_stat_autovacuum_scores shows
> it hundreds of times a minute.

I remember in the past, I measured how big this window could be by
having thousands of relations in a database and capturing the catalog
scan time, if my memory is right, the window isn't that big. However,
it's worth fixing it.

> 0001 treats the age as zero in that case.

Patch LGTM. I would have simplified the comments and commit message
but I will leave it to the committer.

Refreshing the recent xids and recomputing the ages could be another
option but that seems an overkill here.

> 0002 is a TAP test, using
> the existing autovacuum-worker-start injection point, that shows it for
> an autovacuum worker; it's for the record rather than for commit.

TAP test here is a nice way to repro the issue and yes it's not for
the commit but for the records. BTW, it could be simplified by just
using wait_for_event() instead (no need to change that though).

> Please find the attached patches. Thoughts?

I think this needs to be back-patched to PG19 (assuming we have
another release after RC2 before the planned GA on Nov 19th). Although
the issue looks rare in practice, it is good for keeping the version
diff small.

--
Bharath Rupireddy
Amazon Web Services: https://aws.amazon.com