Absolutely check the logs, or do a manual vacuum verbose with setting cost
delay and cost limit (and maintenance work mem) the same as the values for
auto vacuum runs. It should work out the same and you could time it for a
period when the system is more lightly used it applicable.

If you have many very large indexes on the tables with a high number of
tuples and bloat, that may be slowing the execution particularly if your
allowed work memory for the operation doesn't allow a single pass of the
index.

If you are on PG12+, you can reindex concurrently and then run vacuum and
see how it goes.

Freezing will automatically happen according to settings, but if it is near
the threshold then it could be that autovacuum is doing more work scanning
old data. A manual vacuum freeze would mitigate that. That may not be
significant though.

For your larger tables, or system in general, turning down your scale
factor settings will qualify tables for autovacuum sooner. If it hurts, you
aren't doing it often enough.

Also, reducing cost delays may be needed to pause for less time in the
middle of autovacuum executions. The default changed from 20ms to 2ms with
PG12 but if your I/O system can handle it, lower may be prudent to get the
work done more quickly.

Reply via email to