autovacuum and VACUUM are not one thing

VACUUM is a statement you issue against tables you name. Autovacuum is a launcher process and a pool of workers that choose tables themselves, on thresholds derived from how much of each table has changed, and then perform the same work the statement performs. The implementation underneath is shared. The trigger, the speed limit, the settings that govern them and the counters that record them are all separate, which is why “we vacuum nightly” answers a different question from “autovacuum is keeping up”.

Same work, two different speed limits

The pair of settings people most often miss is the cost delay. Both forms of vacuum accumulate a cost as they read and dirty pages, and both sleep when that cost passes a limit. The shipped delay for the daemon is a couple of milliseconds; the shipped delay for the statement is zero. A VACUUM typed at a prompt or fired by a cron job therefore runs flat out and can saturate the same storage the application is using, while the daemon doing identical work on the same table crawls deliberately.

There is a second asymmetry with sharper teeth. An autovacuum worker that ends up blocking another session’s lock request gets cancelled and tries again later, which is what stops routine maintenance from queueing a schema change behind it. A VACUUM you issued has no such courtesy: it holds its lock until it is done, and anything that conflicts waits. The exception is a worker running to prevent transaction id wraparound, which does not yield, and that is usually the first time an operator discovers the rule existed.

Reading the two budgets side by side

The four settings that govern this are easier to understand together than apart.

SELECT name, setting, unit FROM pg_settings
WHERE name IN ('vacuum_cost_delay', 'autovacuum_vacuum_cost_delay',
               'vacuum_cost_limit', 'autovacuum_vacuum_cost_limit')
ORDER BY name;

             name             | setting | unit 
------------------------------+---------+------
 autovacuum_vacuum_cost_delay | 2       | ms
 autovacuum_vacuum_cost_limit | -1      | 
 vacuum_cost_delay            | 0       | ms
 vacuum_cost_limit            | 200     | 
(4 rows)

The minus one is the trap. It does not mean unlimited; it means the daemon borrows vacuum_cost_limit, so raising that setting to make a manual vacuum faster silently speeds up every autovacuum worker as well, and the limit is then divided among however many workers are running. Two of the four numbers above are shared between the two mechanisms and two are not, and nothing in the names says which.

Deciding which one you actually want

Nightly manual vacuums are usually a workaround for a daemon whose thresholds are wrong for the table, and they leave the table untended for the other twenty-three hours. Lowering the scale factor on that one table is the durable version of the same intent, and the reasoning behind it, along with the three separate causes of dead rows that refuse to clear, is in autovacuum and table bloat. If the tables in question are large and old rather than merely busy, the pressure is age rather than churn, and transaction id wraparound is the guide that matters.