Postgresql – Vacuum increases bloat size as reported by Heroku’s bloat SQL query

There are a least three major reasons for why you should upgrade to a more recent version, preferably to the current version.

1. As mentioned by @a_horse_with_no_name the autovacuum mechanism has been improved in many places since version 8.2.

2. You don't see frequent DELETEs, so the table bloat most probably comes from UPDATEs 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 UPDATEs and DELETEs (Pavan Deolasee, with ideas from many others)

   UPDATEs and DELETEs leave dead tuples behind, as do failed INSERTs. 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.

3. 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 UPDATEs launched, use the configuration parameter log_statement and check your database log. Maybe a misconfigured app or a trigger launches a lot more UPDATEs 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.

I think your best option is to use your favorite OS scheduling tool to run ANALYZE <table> explicitly and pre-emptively at the time of your choosing, say at 3 a.m.

PostgreSQL's autovacuum and autoanalyze are activity-counter driven. This has the unfortunate effect that the time when the activity counters cross the thresholds is mostly likely to be exactly when the database is most active, which is when you least want those maintenance tasks to run. By running the task preemptively during a off-peak time you will reset the activity counters, so that the auto-versions rarely will find work to do. But in case of something going wrong, they are still there to save your bacon.

Will the query planner eventually suffer from not having auto analyze running on a table that never gets updates or deletes?

This can't be answered with the information you provide. If that table gets nothing but single-row lookups via a unique index, for example, out-of-date statistics are unlikely to be a problem. If it gets complicated analytics queries, it is likely to be much more of a problem.