Launch autovaccum automatically

Question

Im new using posgtresql, i want to do an autovaccum when dead_rows are high.

How can i do it automatically.

Thank you,

Answer 1

You need to ensure autovacuum is set to 'on' in postgresql.conf. Then you will want to tune:

autovacuum_vacuum_scale_factor - set this to a number between 0 and 1 that represents how much of the table needs to change to trigger a vacuum autovacuum_vacuum_threshold - set this to the number of row changes that would trigger a vacuum.

So if you had a table will 1,000,000 rows, and you had autovacuum_vacuum_scale_factor set to 0.05 and autovacuum_vacuum_threshold set to 5000, this would mean 5% of the table (50,000 rows) + 5,000 rows (total of 55,000) would trigger an autovacuum. You can tune both of these in postgresql.conf, but you can also set it on the table directly if it is the exception.

e.g.

ALTER TABLE mytable
SET (autovacuum_vacuum_scale_factor = 0.05, autovacuum_vacuum_threshold = 5000);