4

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.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
Ankit Arora
  • 155
  • 1
  • 2
  • 8
  • 1
    Does this help? https://www.percona.com/blog/2018/08/10/tuning-autovacuum-in-postgresql-and-autovacuum-internals/ –  Aug 31 '20 at 13:03
  • 1
    @a_horse_with_no_name It says that "autovacuum_vacuum_cost_delay : autovacuum will sleep for these many milliseconds when a cleanup reaching autovacuum_vacuum_cost_limit cost is done." and "autovacuum_vacuum_cost_limit" says that it's total cost limit autovacuum could reach (combined by all autovacuum jobs). Can you please help me understand "autovacuum_vacuum_cost_limit"? – Ankit Arora Aug 31 '20 at 13:20

1 Answers1

3

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.

Ekeko
  • 1,879
  • 14
  • 15
Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263