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

On Wed, Feb 16, 2022 at 4:48 PM Peter Geoghegan <pg(at)bowt(dot)ie> wrote:
> There might well be an element of survivorship bias here. Most VACUUM
> operations won't ever attempt truncation (speaking very generally).
> How many times might (say) the customer that John mentioned have
> accidentally gone over xidStopLimit for just a little while, before
> the situation corrected itself without anybody noticing? A lot of
> applications are very read-heavy, or aren't very well monitored.
>
> Eventually (maybe after several years of this), some laggard
> anti-wraparound vacuum needs to truncate the relation, due to random
> happenstance. Once that happens, the situation is bound to come to a
> head. The user is bound to finally notice that the system has gone
> over xidStopLimit, because there is no longer any way for the problem
> to go away on its own.

I think that's not really what is happening, at least not in the cases
that typically are brought to my attention. In those cases, the
typical pattern is:

1. Everything is fine.

2. Then the user forgets about a prepared transaction or a replication
slot, or leaves a transaction open forever, or has some kind of
corruption that causes VACUUM to fall over and die every time it tries
to run.

3. The user has no idea that VACUUM is no longer advanced
relfrozenxid. Time passes.

4. Eventually the system stops being willing to allocate new XIDs. It
tells the user to go to single user mode. So they do.

5. None of the tables in the database have been vacuumed in a long
time. There are a million XIDs left. How many of the tables in the
database are going to be truncate when they are vacuumed and burn one
of the remaining XIDs? Anybody's guess, could be all or none.

6. Sometimes the user decides to run VACUUM FULL instead of plain
VACUUM because it sounds better.

--
Robert Haas
EDB: http://www.enterprisedb.com