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

On Tue, Dec 21, 2021 at 6:39 PM Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
> Even not in the situation where the database has to run as the
> single-user mode to freeze tuples, I think there would be some use
> cases where users want to run vacuum (in failsafe mode) on tables with
> relfrozenxid/relminmxid greater than their failsafe thresholds before
> falling into that situation. I think it’s common that users are
> somehow monitoring relfrozenxid/relminmxid and want to manually run
> vacuum on them rather than relying on autovacuums. --min-xid-age
> option and --min-mxid-age option of vacuumdb command would be good
> examples. So I think this new command/facility might not necessarily
> need to be specific to single-user mode.

It wouldn't be specific to single-user mode, since that is not really
special. It's only special in a way that's quite artificial (it can
continue to allocate XIDs past the point where we usually deem it
unsafe).

So, I think we agree; this new emergency vacuuming feature shouldn't
be restricted to single-user mode in any way, and shouldn't care about
whether we're in single user mode or not when run. OTOH, it probably
will be presented as something that is typically run in single user
mode, in situations like the one John's customer found themselves in
-- disastrous, unpleasant situations. It's not just a good policy
(that makes testing easy). The same kind of problem can easily be
caught a little earlier, before the system actually becomes unable to
allocate new XIDs (when not in single-user mode) -- that's quite
likely, and almost as scary.

As I said before, ISTM that the important thing is to have something
dead simple -- something that is easy to use when woken at 4am, when
the DBA is tired and stressed. Something that makes generic choices,
that are not way too conservative, but also don't risk making the
problem worse instead of better.

--
Peter Geoghegan