Some colleagues and I were looking at the autovacuum_naptime
documentation recently, and we were confused about the exact mechanism
at work here:
>Specifies the minimum delay between autovacuum runs on any given database. In each round the daemon examines the
databaseand issues VACUUM and ANALYZE commands as needed for tables in that database. If this value is specified
withoutunits, it is taken as seconds. The default is one minute (1min). This parameter can only be set in the
postgresql.conffile or on the server command line.
The Routine Vacuuming section has some additional info on this:
>The “autovacuum daemon” actually consists of multiple processes. There is a persistent daemon process, called the
autovacuumlauncher, which is in charge of starting autovacuum worker processes for all databases. The launcher will
distributethe work across time, attempting to start one worker within each database every autovacuum_naptime seconds.
(Therefore,if the installation has N databases, a new worker will be launched every autovacuum_naptime/N seconds.) A
maximumof autovacuum_max_workers worker processes are allowed to run at the same time. If there are more than
autovacuum_max_workersdatabases to be processed, the next database will be processed as soon as the first worker
finishes.Each worker process will check each table within its database and execute VACUUM and/or ANALYZE as needed.
log_autovacuum_min_durationcan be set to monitor autovacuum workers' activity.
This is clearer, but it still elides some important details.
We found a post from Robert Haas [1] that explains this in more
detail, and the documentation quoted above is consistent with this,
but I think the setting docs especially could still be improved:
1. The documentation doesn't talk about the behavior of additional
workers launched in the same database. Technically it doesn't
contradict that behavior, but it's not clear that that's what should
be inferred, and this seems fairly important.
2. The setting documentation doesn't talk about the interaction with
autovacuum_max_workers, i.e., what happens when all workers are busy.
The Routine Vacuuming section addresses it, but maybe something like
"Specifies the delay between autovacuum runs on any given database if
a vacuum worker is available." would be clearer?
3. The docs say "...between autovacuum runs...," but "In each
round..." I assume a round is the same thing as a run here, so maybe
using the same word would be clearer?
Thoughts?
Thanks,
Maciek
[1]: http://rhaas.blogspot.com/2019/02/tuning-autovacuumnaptime.html