In Postgres, what should be the autovacuum strategy for big tables (hundreds of millions rows) that get added/removed thousands of rows every day? 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 &gt;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, &gt;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.
Your table seems fine at first glance. If autovacuum gets done and you have less than 30% of dead tuples, I see no need to worry.
You might want to use the pgstattuple extension to check if the table has a lot of free space; if yes, that would be an indication to make autovacuum faster.
The keyword here is faster: you'd have to lower autovacuum_vacuum_cost_delay or increase autovacuum_vacuum_cost_limit for that. Making autovacuum run more often won't do any good.
But if you are already experiencing performance problems with autovacuum being as fast as it currently is, I'd leave the settings alone.