There are a least three major reasons for why you should upgrade to a more recent version, preferably to the current version.
As mentioned by @a_horse_with_no_name the autovacuum mechanism has been improved in many places since version 8.2.
You don't see frequent DELETE
s, so the table bloat most probably comes from UPDATE
s as @Milen commented. A new feature has been introduced with version 8.3, that counters this problem at its root: Heap-Only Tuples, I quote the release notes:
Heap-Only Tuples (HOT) accelerate space reuse for most UPDATE
s and
DELETE
s (Pavan Deolasee, with ideas from many others)
UPDATE
s and DELETE
s leave dead tuples behind, as do failed INSERT
s.
Previously only VACUUM
could reclaim space taken by dead tuples. With
HOT dead tuple space can be automatically reclaimed at the time of
INSERT
or UPDATE
if no changes are made to indexed columns. This
allows for more consistent performance. Also, HOT avoids adding
duplicate index entries.
Emphasis mine.
PostgreSQL 8.2 has reached end of life in Dec. 2011. No more security updates. Upgrade to a more recent version as soon as possible.
To find out whether there are, in fact, a lot of UPDATE
s launched, use the configuration parameter log_statement
and check your database log. Maybe a misconfigured app or a trigger launches a lot more UPDATE
s than you think?
If you SET
the parameter in a session it only changes for this session:
SET log_statement = 'all';
So, rather change it in your postgresql.conf
:
log_statement = all
and reload. For instance with pg_ctl reload
. But don't forget to change it back (and reload) or your log files may grow huge. Then check the log files to see what's actually going on.