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

On Tue, Jan 11, 2022 at 8:57 PM Peter Geoghegan <pg@bowt.ie> wrote:
> On Tue, Jan 11, 2022 at 4:59 PM John Naylor
> > For the PoC I wanted to try re-using existing keywords. I went with
> > "VACUUM LIMIT" since LIMIT is already a keyword that cannot be used as
> > a table name. It also brings "wraparound limit" to mind. We could add
> > a single-use unreserved keyword (such as VACUUM_MINIMAL or
> > VACUUM_FAST), but that doesn't seem great.
>
> This seems reasonable, but you could add a new option instead, without
> much downside. While INDEX_CLEANUP kind of looks like a keyword, it
> isn't really a keyword. (Perhaps you knew this already.)
>
> Making this a new option is a little awkward, admittedly. It's not
> clear what it means to "VACUUM (LIMIT) my_table" -- do you just throw
> an error for stuff like that? So perhaps your approach of adding
> VacuumMinimalStmt (a minimal variant of the VACUUM command) is better.

We'd also have to do some checks to either ignore other options or
throw an error, which seems undesirable for code maintenance. For that
reason, I prefer the separate top-level statement, but I'm open to
bike-shedding on the actual syntax. I also briefly looked into a SQL
function, but the transaction management would make that more
difficult.

> > I'm not sure what the right trade-off is, but as written I used 95% of
> > max age. It might be undesirable to end up so close to kicking off
> > uninterruptible vacuums, but the point is to get out of single-user
> > mode and back to streaming WAL as quickly as possible. It might also
> > be worth overriding the min ages as well, but haven't done so here.
>
> I wonder if we should keep autovacuum_freeze_max_age out of it -- its
> default is too conservative in general. I'm concerned that applying
> this autovacuum_freeze_max_age test during VACUUM LIMIT doesn't go far
> enough -- it may require VACUUM LIMIT to do significantly more work
> than is needed to get the system back online (while leaving a sensible
> amount of headroom). Also seems like it might be a good idea to avoid
> relying on the user configuration, given that VACUUM LIMIT is only run
> when everything is already in disarray. (Besides, it's not clear that
> it's okay to use the autovacuum_freeze_max_age GUC without also using
> the reloption of the same name.)
>
> What do you think of applying a similar test using a generic 1 billion
> XID (and 1 billion MXID) age cutoff?

I like that a lot, actually. It's simple and insulates us from
wondering about corner cases in configuration.

> The GetNewTransactionId() WARNINGs ought to be changed to reference
> VACUUM LIMIT. (You probably just didn't get around to that in this
> POC, but couldn't hurt to remind you.)

I'll do that as well as documentation after we have agreement (or at
least lack of objection) on the syntax.

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