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

On Fri, Jan 21, 2022 at 05:41:58PM -0500, John Naylor wrote:
> On Wed, Jan 19, 2022 at 5:26 PM Michael Paquier <michael(at)paquier(dot)xyz> wrote:
> >
> > Could you avoid introducing a new grammar pattern in VACUUM? Any new
> > option had better be within the parenthesized part as it is extensible
> > at will with its set of DefElems.
>
> This new behavior is not an option that one can sensibly mix with
> other options as the user sees fit, but rather hard-codes the
> parameters for its single purpose. That said, I do understand your
> objection.

This seems better, and it's shorter too.

I'm sure you meant "&" here (fixed in attached patch to appease the cfbot):
+ if (options | VACOPT_MINIMAL)

It should either refuse to run if a list of tables is specified with MINIMAL,
or it should filter that list by XID condition.

As for the name, it could be MINIMAL or FAILSAFE or EMERGENCY or ??
I think the name should actually be a bit more descriptive, and maybe say XID,
like MINIMAL_XID or XID_EMERGENCY...

Normally, options are independent, but VACUUM (MINIMAL) is a "shortcut" to a
hardcoded set of options: freeze on, truncate off, cleanup off. So it refuses
to be combined with other options - good.

This is effectively a shortcut to hypothetical parameters for selecting tables
by XID/MXID age. In the future, someone could debate adding user-facing knobs
for table selection by age.

I still wonder if the relations should be processed in order of decreasing age.
An admin might have increased autovacuum_freeze_max_age up to 2e9, and your
query might return thousands of tables, with a wide range of sizes and ages.

Processing them in order of decreasing age would allow the admin to quickly
vacuum the oldest tables, and optionally interrupt vacuum to get out of single
user mode ASAP - even if their just want to run VACUUM(MINIMAL) in a normal
backend when services aren't offline. Processing them out of order might be
pretty surprising - they might run vacuum for an hour (or overnight), cancel
it, attempt to start the DB in normal mode, and conclude that it made no
visible progress.

On Fri, Jan 21, 2022 at 12:59 AM Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
> > The purpose of this thread is to provide a way for users to run vacuum
> > only very old tables (while skipping index cleanup, etc.),
>
> Ah, thank you Sawada-san, now I understand why we have been talking
> past each other. The purpose is actually:
>
> - to have a simple, easy to type, command
> - intended for single-user mode, but not limited to it (so it's easy to test)
> - to get out of single user mode as quickly as possible