On Wed, Feb 16, 2022 at 10:08 PM Peter Geoghegan <pg@bowt.ie> wrote:
> > 6. Sometimes the user decides to run VACUUM FULL instead of plain
> > VACUUM because it sounds better.
>
> It's a pity that the name suggests otherwise. If only we'd named it
> something that suggests "option of last resort". Oh well.
Unfortunately, such a name would also be misleading, just in a
different way. It is really not at all difficult to have a workload
that demands routine use of VACUUM FULL. I suppose technically it is a
last resort in such situations, because what would you resort to after
trying VF? But it's not like some kind of in-emergency-break-glass
kind of thing, it's just the right tool for the job.
Some years ago I worked with a customer who had a table that was being
used as an update-heavy queue. I don't remember all the details any
more, but I think the general pattern was that they would insert rows,
update them A TON, and then eventually delete them. And they got
really bad table bloat, because vacuum just wasn't running often
enough to keep up. Reducing autovacuum_naptime to 15s fixed the issue,
fortunately, but I was initially thinking that it might be completely
unfixable, because what if they'd also been running a series
4-minute-long reporting queries in a loop on some other table? More
frequent vacuuming wouldn't have helped then, because xmin would not
have been able to advance until the current instance of the reporting
query finished, and then vacuuming more often would have done nothing
useful. I think, anyway.
That's just one example that comes to mind. I think there are lots of
workloads where it's simply not possible to make VACUUM keep up.
--
Robert Haas
EDB: http://www.enterprisedb.com