Re: do only critical work during single-user vacuum?

On Thu, Dec 9, 2021 at 3:53 PM John Naylor <john(dot)naylor(at)enterprisedb(dot)com> wrote:
> > single-user mode should prompt the user about
> > what exact VACUUM command they ought to run to get things going.
>
> The current message is particularly bad in its vagueness because some
> users immediately reach for VACUUM FULL, which quite logically seems
> like the most complete thing to do.

You mean the GetNewTransactionId() error, about single-user mode? Why
do we need to use single-user mode at all? I'm pretty sure that the
reason is "as an escape hatch", but I wonder what that really means.

***Thinks***

I suppose that it might be a good idea to make sure that autovacuum
cannot run, because in general autovacuum might need to allocate an
XID (for autoanalyze), and locking all that down in exactly the right
way might not be a very good use of our time.

But even still, why not have some variant of single-user mode just for
this task? Something that's easy to use when the DBA is rudely
awakened at 4am -- something a little bit like a big red button that
fixes the exact problem of XID exhaustion, in a reasonably targeted
way? I don't think that this needs to involve the VACUUM command
itself.

The current recommendation to do a whole-database VACUUM doesn't take
a position on how old the oldest datfrozenxid has to be in order to
become safe again, preferring to "make a conservative recommendation"
-- which is what a database-level VACUUM really is. But that doesn't
seem helpful at all. In fact, it's not even conservative. We could
easily come up with a reasonable definition of "datfrozenxid that's
sufficiently new to make it safe to come back online and allocate XIDs
again". Perhaps something based on the current
autovacuum_freeze_max_age (and autovacuum_multixact_freeze_max_age)
settings, with sanity checks.

We could then apply this criteria in new code that implements this
"big red button" (maybe this is a new option for the postgres
executable, a little like --single?). Something that's reasonably
targeted, and dead simple to use.

--
Peter Geoghegan