Re: is there any adverse effect on DB if I set autovacuum scale factor 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