Re: [PERFORM] Default autovacuum settings too conservative

Jim C. Nasby wrote:
> Small tables are most likely to have either very few updates (ie: a
> 'lookup table') or very frequent updates (ie: a table implementing a
> queue). In the former, even with vacuum_threshold = 0 vacuum will be a
> very rare occurance. In the later case, a high threshold is likely to
> cause a large amount of un-nececcasry bloat.

Well a threshold of 0 won't work because then a 0 tuple table will get
vacuumed every time. Or at least autovacuum needs to special case this.

> Also, vacuum_scale_factor of 0.4 seems unreasonably large. It means
> tables will be 40% dead space, which seems excessively wasteful.
> Something between 0.1 and 0.2 seems much better.

Depends on the app and the usage patterns as to what too much slack
space is.

> Has anyone looked at how effective these two settings are?

As far I as I know, we are still looking for real world feedback. 8.1
is the first release to have the integrated autovacuum. The thresholds
in 8.1 are a good bit less conservative than the thresholds in the
contrib version. The contrib thresholds were universally considered WAY
to conservative, but that was somewhat necessary since you couldn't set
them on a per table basis as you can in 8.1. If we continue to hear
from people that the current 8.1 default thresholds are still to
conservative we can look into lowering them.

I think the default settings should be designed to minimize the impact
autovacuum has on the system while preventing the system from ever
getting wildly bloated (also protect xid wraparound, but that doesn't
have anything to do with the thresholds).

Matt