Re: Rules of Thumb for Autovaccum

Hi,

On 16 February 2012 01:14, Robert James <srobertjames(at)gmail(dot)com> wrote:
> What rules of thumb exist for:
> * How often a table needs to be vacuumed?
> * How often a table needs to be analyzed?
> * How to tune Autovacuum?

I prefer to use autovacuum daemon and sets thresholds on per table
basis i.e. sets reasonable defaults and then add few exceptions.
I keep *_threshold as is and change *_scale_factor and turn off cost
based vacuum/analyse (see other note about this). My lowest
scale_factor is 0.002 ie. 0.2% of table has to change (which
corresponds to ~8mil rows) to trigger analyse/vacuum.

autovacuum/analyse can produce significant I/O so you have two options:
- tune cost based settings in order to limit I/O used by this porocess
- turn off autovacuum daemon and schedule manual acuum/analyse in quiet period

--
Ondrej Ivanic
(ondrej(dot)ivanic(at)gmail(dot)com)