Horton

Re: BUG #17717: Regression in vacuumdb (15 is slower than 10/11 and possible memory issue)

Tom Lane <tgl@sss.pgh.pa.us>

View thread
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Peter Geoghegan <pg@bowt.ie>
Cc: postgresql@taljaren.se, pgsql-bugs@lists.postgresql.org, Michael Paquier <michael@paquier.xyz>
Date: 2022-12-16T14:49:09Z
Lists: pgsql-bugs, pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Add options to control whether VACUUM runs vac_update_datfrozenxid.

  2. Use catalog query to discover tables to process in vacuumdb

Peter Geoghegan <pg@bowt.ie> writes:
> On Thu, Dec 15, 2022 at 1:57 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Or maybe we could modify things so that "autovacuum = off" doesn't prevent
>> occasional cycles of vac_update_datfrozenxid-and-nothing-else?

> That's what I was thinking of. It seems like a more natural approach
> to me, at least offhand.

Seems worth looking into.  But I suppose the launch frequency would
have to be more often than the current behavior for autovacuum = off,
so it would complicate the logic in that area.

> I have to imagine that the vast majority of individual calls to
> vac_update_datfrozenxid have just about zero chance of updating
> datfrozenxid or datminmxid as things stand.

That is a really good point.  How about teaching VACUUM to track
the oldest original relfrozenxid and relminmxid among the table(s)
it processed, and skip vac_update_datfrozenxid unless at least one
of those matches the database's values?  For extra credit, also
skip if we didn't successfully advance the source rel's value.

This might lead to a fix that solves the OP's problem while not
changing anything fundamental, which would make it reasonable
to back-patch.

			regards, tom lane