I have quite a big table with a lot of daily traffic for reads, inserts and deletions. Currently, it has 392 million of live tuples and 27 million of dead ones. Vacuum settings (autovacuum_vacuum_threshold
, autovacuum_vacuum_scale_factor
, etc) are set to the defaults.
Occasionally I do get some performance issues that make queries last >2 minutes when they usually take couple of seconds.
At first, I'd have thought about lowering the vacuum scale factor from the current 0.2 to 0.05 or even 0.01. But, because the autovacuum runs already several times a day (and they may run for a while, >1 hour), I'm not sure if lowering the scale factor would make it worse as it would run even more often, despite meaning that less number of dead tuples the autovacuum will work on.
auto_explain
can be useful here, so can settinglog_autovacuum_min_duration
)