How to tune vacuum process in PostgreSQL?

Question

As per my understanding PostgreSQL autovacuum run depending on below formula. Here I found an issue is that, it could run any time when below result/value is matched. Suppose threshold=50 and scale factor = 0.2 and live tuple is 1000 , if total dead tuple > 50+(1000*0.2)=250 then it run autovacuum any time. Most case it could run at peak hour.

threshold + pg_class.reltuples * scale_factor

Because of that reason I think it is better to run everyday on those table whose table size is grater than mentioned amount. Or we can run everyday on all tables off peak hour.

I am looking for expert opinion, is it right way to solve those vacuum issue?

Answer 1

Small tables are not your problem. An autovacuum run on a 1250 row table will be done in the blink of an eye.

Autovacuum on larger tables gets triggered less often, but can potentially cause more load. For that reason, autovacuum already runs slower than it could by taking a break of autovacuum_vacuum_cost_delay whenever it has done work amounting to autovacuum_vacuum_cost_limit. It is possible to reduce the strain on the database by increasing the cost delay or reducing the cost limit, but the danger then is that autovacuum runs to slow and you end up with bloated tables.

My advice is not to mess with the autovacuum configuration. Very likely it won't cause a performance problem for you, but if it does, consider getting a stronger machine rather than crippling this necessary maintenance process.