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

On Wed, Jan 12, 2022 at 12:26 PM Bossart, Nathan <bossartn(at)amazon(dot)com> wrote:
>
> > - For the general case, we would now have the ability to vacuum a
> > table, and possibly have no effect at all. That seems out of place
> > with the other options.
>
> Perhaps a message would be emitted when tables are specified but
> skipped due to the min-xid-age option.
>
> 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.)

> (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.

> 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.

--
John Naylor
EDB: http://www.enterprisedb.com