Re: Are my autovacuum settings too aggressive for this table?

>
>
>
> Live tuples = 19,766,480
> Analyze scale factor = 0.001
> Analyze thresh = 5000
> Thresh + live_tuples * factor = 24,766
>
> So an autovacuum analyze should trigger around 24K tuples modified, is
> this to little or too much?

This seems too much to me. Was there a specific problem occurring that
spurred this change? If many of the tuple modifications are occurring on a
certain subset of the data which changes the distribution in an important
way, then maybe this would be justified. (But maybe
partitioning between active and largely inactive would be a better solution)

> Same goes for autvacuum vacuum, is it too aggressive?
>
>
> #------------------------------------------------------------------------------
> # AUTOVACUUM
>
> #------------------------------------------------------------------------------
> autovacuum_naptime = 1h
> autovacuum_vacuum_threshold = 10000
> autovacuum_analyze_threshold = 5000
> autovacuum_vacuum_scale_factor = 0.002
> autovacuum_analyze_scale_factor = 0.001
>
>
The analyze settings don't seem defensible to me, but maybe you can make a
case for them by referring to problems that were showing up in particular
queries.

The naptime seems particularly indefensible. If you think you overdid it
with some changes, then you should back off those changes. Not randomly
whack around other settings in an attempt to compensate for the first ones,
without having some identifiable theory which supports this. If you do
have such a theory, you haven't told us what it might be.

If index-only-scans (and hence rel_allvisible) are particularly important
to you, then the autovac settings might make sense. However, this is
unlikely to be true at the global level, but rather on a table by table
basis. But, I don't see the point in setting
autovacuum_vacuum_scale_factor = 0.002. If it needs a drastic decrease (to
pair with a well-considered increase in autovacuum_vacuum_threshold) why
would 99% be the correct decrease, rather than 100%? A nice thing about
exact 0 is it stands out as being intentional, as opposed to a typo or a
mistranslation between percentage and fraction.

Cheers,

Jeff