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
