On Tue, 2 Mar 2021 at 04:32, Hannu Krosing <han...@google.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