Robert Haas <robertmhaas@xxxxxxxxx> wrote: > Rick <richard.branton@xxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
