On Wed, Feb 16, 2022 at 10:08 PM Peter Geoghegan <p...@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
