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

On 1/13/22, 4:58 AM, "John Naylor" <john(dot)naylor(at)enterprisedb(dot)com> wrote:
> On Wed, Jan 12, 2022 at 12:26 PM Bossart, Nathan <bossartn(at)amazon(dot)com> wrote:
>> As I've stated upthread, Sawada-san's suggested approach was my
>> initial reaction to this thread. I'm not wedded to the idea of adding
>> new options, but I think there are a couple of advantages. For both
>> single-user mode and normal operation (which may be in imminent
>> wraparound danger), you could use the same command:
>>
>> VACUUM (MIN_XID_AGE 1600000000, ...);
>
> My proposed top-level statement can also be used in normal operation,
> so the only possible advantage is configurability. But I don't really
> see any advantage in that -- I don't think we should be moving in the
> direction of adding more-intricate ways to paper over the deficiencies
> in autovacuum scheduling. (It could be argued that I'm doing exactly
> that in this whole thread, but [imminent] shutdown situations have
> other causes besides deficient scheduling.)

The new top-level command would be configurable, right? Your patch
uses autovacuum_freeze_max_age/autovacuum_multixact_freeze_max_age, so
the behavior of this new command now depends on the values of
parameters that won't obviously be related to it. If these parameters
are set very low (e.g., the default values), then this command will
end up doing far more work than is probably necessary.

If we did go the route of using a parameter to determine which tables
to vacuum, I think vacuum_failsafe_age is a much better candidate, as
it defaults to a much higher value that is more likely to prevent
doing extra work. That being said, I don't know if overloading
parameters is the right way to go.

>> (As an aside, we'd need to figure out how XID and MXID options would
>> work together. Presumably most users would want to OR them.)
>>
>> This doesn't really tie in super nicely with the failsafe mechanism,
>> but adding something like a FAILSAFE option doesn't seem right to me,
>
> I agree -- it would be awkward and messy as an option. However, I see
> the same problem with xid/mxid -- I would actually argue they are not
> even proper options; they are "selectors". Your comments above about
> 1) needing to OR them and 2) emitting a message when a VACUUM command
> doesn't actually do anything are evidence of that fact.

That's a fair point. But I don't think these problems are totally
intractable. We already emit "skipping" messages from VACUUM
sometimes, and interactions between VACUUM options exist today, too.
For example, FREEZE is redundant when FULL is specified, and
INDEX_CLEANUP is totally ignored when FULL is used.

>> The other advantage I see with age-related options is that it can be
>> useful for non-imminent-wraparound situations as well. For example,
>> maybe a user just wants to manually vacuum everything (including
>> indexes) with an age above 500M on the weekends.
>
> There is already vaccumdb for that, and I think it's method of
> selecting tables is sound -- I'm not convinced that pushing table
> selection to the server command as "options" is an improvement.

I guess I'm ultimately imagining the new options as replacing the
vacuumdb implementation. IOW vacuumdb would just use MIN_(M)XID_AGE
behind the scenes (as would a new top-level command).

Nathan