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

On Wed, Jan 12, 2022 at 1:49 AM Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
> It seems to me that adding new syntax instead of a new option is less
> flexible. In the future, for instance, when we support parallel heap
> scan for VACUUM, we may want to add a parallel-related option to both
> VACUUM statement and VACUUM LIMIT statement. VACUUM LIMIT statement
> would end up becoming like VACUUM statement?

This is intended for single-user mode, so parallelism is not relevant.

> As another idea, we might be able to add a new option that takes an
> optional integer value, like VACUUM (MIN_XID), VACUUM (MIN_MXID), and
> VACUUM (MIN_XID 500000). We vacuum only tables whose age is older than
> the given value. If the value is omitted, we vacuum only tables whose
> age exceeds a threshold (say autovacuum_freeze_max_age * 0.95), which
> can be used in an emergency case and output in GetNewTransactionID()
> WARNINGs output. vacuumdb’s --min-xid-age and --min-mxid-age can use
> this option instead of fetching the list of tables from the server.

That could work, and maybe also have general purpose, but I see two
problems if I understand you correctly:

- If we have a default threshold when the values are omitted, that
implies we need to special-case single-user mode with non-obvious
behavior, which is not ideal, as Andres mentioned upthread. (Or, now
manual VACUUM by default would not do anything, except in extreme
cases, which is worse.)

- In the single-user case, the admin would still need to add
INDEX_CLEANUP = off for minimum downtime, and it should be really
simple.

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

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