PostgreSQL: Long running auto vacuum and high wal rate

Question

Recently a vacuum process triggered by auto vacuum daemon(PostgreSQL13) on one of our table took long 1.5hr to complete. During this period there was high WAL rate up to 7MB/sec and high Disk I/O. This table size around 75GB(200M rows) , and the auto vacuum count from stats table is just 2 only for last 2 year. What are some auto-vacuum tuning possibilities for huge tables? My considerations are,

• Should I disable auto vacuum for huge table and perform manually?
• Can decreasing threshold and hence more frequent vacuum solve long running vacuum?

Any other solutions?

Answer 1

-- Adjust autovacuum thresholds for the large table
ALTER TABLE your_large_table SET (
    autovacuum_vacuum_threshold = 5000,
    autovacuum_vacuum_scale_factor = 0.01,
    autovacuum_analyze_threshold = 5000,
    autovacuum_analyze_scale_factor = 0.01
);

-- Increase autovacuum cost limit and reduce delay
SET autovacuum_cost_limit = 2000;
SET autovacuum_cost_delay = 10;

-- Adjust fillfactor to reduce dead tuples
ALTER TABLE your_large_table SET (
    fillfactor = 80
);

-- Increase maintenance work memory
SET maintenance_work_mem = '2GB';

-- Schedule manual vacuum during off-peak hours
-- Example of crontab entry (run at 3 AM daily)
# crontab -e
0 3 * * * psql -d your_database -c 'VACUUM VERBOSE ANALYZE your_large_table;'

Answer 2

You could throttle auto vac to run slower by decreasing autovacuum_cost_limit or increasing autovacuum_cost_delay on the master. If the WAL is generated slower, it should also be replayed slower, creating less stress on the replica.

Turning off auto vac is not a good idea. You have to vac sometime and when you do it is only likely to be worse. Unless you could segregate it to weekends or nights or some other quiet period.

Making vacuum run more often probably wouldn't work well. The problem would just occur more frequently. Depending on how the tables are updated or deleted and indexed, each occurrence might be shorter but still the same length in aggregate, or they might still be the same length for each one.