I'm trying to get PostgreSQL to aggressively auto vacuum my database. I've currently configured auto vacuum as follows:
- autovacuum_vacuum_cost_delay = 0 #Turn off cost based vacuum
- autovacuum_vacuum_cost_limit = 10000 #Max value
- autovacuum_vacuum_threshold = 50 #Default value
- autovacuum_vacuum_scale_factor = 0.2 #Default value
I notice that the auto vacuum only kicks in when the database is not under load, so I get into situations where there are far more dead tuples than live tuples. See the attached screenshot for an example. One of the tables has 23 live tuples but 16845 dead tuples awaiting vacuum. That's insane!
Auto vacuum kicks in when the test run finishes and the database server is idle, which is not what I want as I would like auto vacuum to kick in whenever the number of dead tuples exceeds 20% live tuples + 50, as the database has been configured. Auto vacuum when the server is idle is useless to me, as the production server is expected to hit 1000s of updates / sec for a sustained period which is why I need auto vacuum to run even when the server is under load.
Is there anything that I'm missing? How do I force auto vacuum to run while the server is under heavy load?
Could this be a locking issue? The tables in question are summary tables which are populated via an after insert trigger. These tables are locked in SHARE ROW EXCLUSIVE mode to prevent concurrent writes to the same row.