0

I have 10-15 large table, 5GB to 30GB. And rest of them less than 1GB. Some tables data is huge and where daily 500 000 dead tuples generated. And autovacuum start almost 7 days later when dead tuples is more than 3.5 million. This database has about 60 tables. Here threshold for database is: 20%.

I want to change autovacuum threshold from 20% to 1%. Is there any impact on system performance (Since for smaller tables it will run frequently). What should be maintanance_work_mem & work_mem parameter value for this?

1 Answer 1

4

You can change the autovacuum scale factor for an individual table:

ALTER TABLE tab SET (autovacuum_vacuum_scale_factor = 0.01);

VACUUM is not cheap, so running it more often will take more resources. However, since VACUUM typically has less work to do when called more frequently, it is not as bad as it seems. The main overhead is that each VACUUM run will scan all indexes on the table, and that work does not become less if you run VACUUM more often (although PostgreSQL v14 has introduced an optimization that skips index scans if only very few index entries have to be deleted).

Normally, it is fine to have the standard 20% of bloat even for large tables. Consider increasing the frequency only if you are aiming at efficient index-only scans on that table.

The main thing you should watch out for is that autovacuum is fast enough on your large tables. For that, tune maintenance_work_mem to be high (so that the indexes don't have to be scanned more often than necessary) and maybe reduce autovacuum_vacuum_cost_delay on those tables.

3
  • Thanks for your continuous support. I am happy to know that I am not alone someone always helping me. Thanks a lot. Sep 26 at 19:50
  • I have set it but how to move it to global/default value, if I need to back again to global/default value for that table ? ALTER TABLE tab SET (autovacuum_vacuum_scale_factor TO DEFAULT); not working. Sep 27 at 16:50
  • 1
    ALTER TABLE tab RESET (autovacuum_vacuum_scale_factor); Sep 28 at 2:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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