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?

  • 1
    Did this cause a problem? Why do you care? A problem that is not a problem is not in need of a solution.
    – jjanes
    Commented Jun 28 at 15:45
  • This indeed caused a problem of high WAL+ disk I/O and resulted in a poorly performing replica with high cpu waiting on I/O. During this time all of the queries were super slow on replica, otherwise we wouldn't even notice this thing!
    – mediocre
    Commented Jul 2 at 1:37

2 Answers 2

-- 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;'
  • Thanks for the answer. Could you mind explaining how to derive autovacuum_vacuum_scale_factor value? Is it based on size or row count?
    – mediocre
    Commented Jul 2 at 1:41

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.

  • Thanks for the comment. The first option help to reduce write intensity but increases over all vacuum time and some impact on the system during this period because it needs to handle 1 yr volume of dead tuples."each occurrence might be shorter but still the same length in aggregate" I don't understand this part. i have tested a vacuum following immediately after a vacuum and it finished in ms for this table, so what I understand from it for huge table the idea is to bring down the number of dead/inserted tuples for each run by increasing the frequency whether it auto or manual?
    – mediocre
    Commented Jul 3 at 4:11
  • @mediocre A vacuum immediately after one just finished might easily find zero work to do (and find that out quickly, using the visibility map). But one two months later will surely find work to do.
    – jjanes
    Commented Jul 4 at 1:26
  • Yes i understand, but a run once in a month and a run once in an year should be different in the means of the target volume of tuples it need to handle and there by the run time?
    – mediocre
    Commented Jul 4 at 2:19
  • @mediocre Maybe. But if yearly needs to rewrite every page to remove about 24 tuples from each page, and monthly needs to rewrite every page to remove about 2 tuples from each, it is the same amount of IO for both (assuming maitenance_work_mem is large enough), but one needs to be done 12 times more often.
    – jjanes
    Commented Jul 4 at 2:55
  • That's quite odd and unlikely to happen in a OLTP production environment. But I understand it's a possibility. Based on your input I believe it could be more sensible for Postgres to set vacuum threshold on page changes rather than tuple changes. Or is there exists such an option ?
    – mediocre
    Commented Jul 4 at 3:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.