The vacuum does not execute regularly enough on some tables, therefore bloat is present on large tables. To overcome this problem, I am setting up a weekly job that runs a vacuum analyze command. Is this approach correct and is it compatible with good practice?

CodePudding user response：

It is certainly correct, but a better practice is to tune autovacuum.

On the table wher you want autovacuum to work as fast as a manual VACUUM, reduce the cost delay to 0:

ALTER TABLE busytab SET (autovacuum_vacuum_cost_delay = 0);

This will work, except if the problem is that you have strong locks on the table often enough to interrupt autovacuum (manual VACUUM won't be interrupted, but would block those locks). If you take such locks often, you are probably doing something wrong with the database and should find a better solution.

The best practice here is never to use the LOCK statement.