Re: is there any adverse effect on DB if I set autovacuum scalefactor to zero?

On 08/13/2018 11:07 AM, Raghavendra Rao J S V wrote:
> Hi All,
> 
> We are using postgres *9.2*  version on *Centos *operating system.  We 
> have around *1300+* tables.We have following auto vacuum settings are 
> enables. Still few of the tables(84 tables) which are always busy are 
> not vacuumed.Dead tuples in those tables are more than 5000.  Due to 
> that tables are bloating and observed few areas has performance 
> degradation.
> 

You don't say how large the tables are, so it's impossible to say 
whether 5000 dead tuples is excessive or not. IMHO it's a negligible 
amount and should not lead to excessive bloat or issues.

A certain amount of wasted is expected - it's a trade-off between 
immediate and delayed cleanup. If you delay the cleanup a bit, it's 
going to be more efficient overall.

It's also unclear why the tables are not vacuumed - it may easily be due 
to all the autovacuum workers being constantly busy, unable to cleanup 
all tables in a timely manner. In that case lowering the threshold is 
not going to help, on the contrary.

regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services