Hi Tom, Thanks for the quick reply. 8.4.9. No db crashes, in fact not even a restart in May until the 28th. I don't believe any HOT updates were occurring in that table: all updates modify one or more indexed fields (update status from pending to done, or update to store a stack trace and bump the time at which to retry the row into future). We don't delete from the table, just insert and update.
During the time interval in question a bug was leading to an unusually high level of activity, both inserts and updates. I'm thinking this is what was responsible for a huge amount of table and index bloat, but haven't yet connected all the dots. Since I don't see autovacuum failing, or failing to run, it's a bit of a mystery. I guess the next step is to attempt to reproduce the bug on a test system and see if we get the bloat. I'll let you know what turns up. On Thu, Jul 3, 2014 at 5:35 PM, Tom Lane <t...@sss.pgh.pa.us> wrote: > Nick Cabatoff <nick.cabat...@gmail.com> writes: > > I'm having trouble making sense of the these two autovacuum log entries. > > I'm running PostgreSQL 8.4. > > 8.4.what? > > It'd probably be a good idea to monitor the counts in pg_stat_all_tables > for the troublesome table(s). I suspect there are a whole lot of HOT > updates going on in this table, which'd account for autoanalyze activity > happening even though autovacuum finds few dead tuples to remove. That > still doesn't directly explain physical table bloat, but it might be > a piece of the puzzle. > > Also, have you had any database crashes in between these autovacuums? > I'm wondering if the WAL-replay issue fixed in commits 6f2aead1f and > siblings could have produced the bloat. > > regards, tom lane >
