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
