Re: We should stop telling users to "vacuum that database in single-user mode"

On Tue, 2 Mar 2021 at 04:32, Hannu Krosing <hannuk(at)google(dot)com> wrote:
>
> It looks like we are unnecessarily instructing our usiers to vacuum their
> databases in single-user mode when just vacuuming would be enough.
>
> We should fix the error message to be less misleading.

It would be good to change the message as it's pretty outdated. Back
in 8ad3965a1 (2005) when the message was added, SELECT and VACUUM
would have called GetNewTransactionId(). That's no longer the case.
We only do that when we actually need an XID.

However, I wonder if it's worth going a few steps further to try and
reduce the chances of that message being seen in the first place.
Maybe it's worth considering ditching any (auto)vacuum cost limits for
any table which is within X transaction from wrapping around.
Likewise for "VACUUM;" when the database's datfrozenxid is getting
dangerously high.

Such "emergency" vacuums could be noted in the auto-vacuum log and
NOTICEd or WARNING sent to the user during manual VACUUMs. Maybe the
value of X could be xidStopLimit minus a hundred million or so.

I have seen it happen that an instance has a vacuum_cost_limit set and
someone did start the database in single-user mode, per the advice of
the error message only to find that the VACUUM took a very long time
due to the restrictive cost limit. I struggle to imagine why anyone
wouldn't want the vacuum to run as quickly as possible in that
situation.

(Ideally, the speed of auto-vacuum would be expressed as a percentage
of time spent working vs sleeping rather than an absolute speed
limit... that way, faster servers would get faster vacuums, assuming
the same settings. Vacuums may also get more work done per unit of
time during offpeak, which seems like it might be a thing that people
might want.)

David