What is autovacuum_vacuum_cost_delay in autovacuum in PostgreSQL?

Question

I am trying to tweak the PostgreSQL server with the following config parameters in the configuration file:

autovacuum_freeze_max_age = 500000000
autovacuum_max_workers = 6
autovacuum_naptime = '15s'
autovacuum_vacuum_cost_delay = 0
maintenance_work_mem = '10GB'
vacuum_freeze_min_age = 10000000

I want to understand what autovacuum_vacuum_cost_delay does. I tried searching a lot on google but didn't get anything good enough to understand.

Answer 1

The documentation refers to vacuum_cost_delay, which says:

The amount of time that the process will sleep when the cost limit has been exceeded.

This is a feature to slow down autovacuum. Whenever an autovacuum worker does work (delete a tuple, read a block, ...), it collects work points. As soon as these points exceed autovacuum_vacuum_cost_limit (200 by default), it makes a pause of autovacuum_vacuum_cost_delay.

The reason is that VACUUM uses a lot of resources, so it is slowed down by default, in the hope not to be disruptive to normal operation. However, if autovacuum is too slow, you can end up with bloated tables.