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

On Tue, Mar 2, 2021 at 7:52 AM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:
>
> 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.

Multiple instances running on the same hardware and only one of them
being in trouble?

But it would probably be worthwhile throwing a WARNING if vacuum is
run with cost delay enabled in single user mode -- so that the user is
at least aware of the choice (and can cancel and try again). Maybe
even a warning directly when starting up a single user session, to let
them know?

--
Magnus Hagander
Me: https://www.hagander.net/
Work: https://www.redpill-linpro.com/