Robert Haas <robertmhaas@gmail.com> wrote:
> Rick <richard.branton@ca.com> wrote:
>> Since vacuum just recovers space, that doesn't seem to be nearly
>> as critical for performance?
>
> That doesn't really match my experience. Without regular
> vacuuming, tables and indices end up being larger than they ought
> to be and contain large amounts of dead space that slows things
> down. How much of an impact that ends up having depends on how
> badly bloated they are and what you're trying to do, but it can
> get very ugly.
That has been my experience, too. When we first started using
PostgreSQL, we noticed a performance hit when some small tables
which were updated very frequently were vacuumed. Our knee-jerk
reaction was to tune autovacuum to be less aggressive, so that we
didn't get hit with the pain as often. Of course, things just got
worse, because every access to that table, when vacuum hadn't been
run recently, had to examine all versions of the desired row, and
test visibility for each version, to find the current one. So
performance fell off even worse. So we went to much more aggressive
settings for autovacuum (although only slightly more aggressive than
what has since become the default) and the problems disappeared.
Basically, as long as small tables are not allowed to bloat,
vacuuming them is so fast that you never notice it.
> 8.3 and higher are better about this because of an optimization
> called HOT, but there can still be problems.
Agreed on both counts.
-Kevin