Re: autovacuum strategy / parameters

On Wed, Apr 21, 2010 at 11:06 AM, Rick <richard(dot)branton(at)ca(dot)com> wrote:
> I have a DB with small and large tables that can go up to 15G.
> For performance benefits, it appears that analyze has much less cost
> than vacuum, but the same benefits?

Err, no. ANALYZE gathers statistics for the query planner; VACUUM
clears out old, dead tuples so that space can be reused by the
database system.

> I can’t find any clear recommendations for frequencies and am
> considering these parameters:
>
> Autovacuum_vacuum_threshold = 50000
> Autovacuum_analyze_threshold = 10000
> Autovacuum_vacuum_scale_factor = 0.01
> Autovacuum_analyze_scale_factor = 0.005
>
> This appears it will result in table analyzes occurring around 10,000
> to 85,000 dead tuples and vacuum occuring around 50,000 to 200,000,
> depending on the table sizes.
>
> Can anyone comment on whether this is the right strategy and targets
> to use?

I'm not that familiar with tuning these parameters but increasing the
default thesholds by a thousand-fold doesn't seem like a good idea.
Small tables will never get vacuumed or analyzed at all.

...Robert