Re: Why does autovacuum clean fewer rows than I expect?

[Nick Cabatoff <nick.cabatoff@xxxxxxxxx>]

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 <tgl@xxxxxxxxxxxxx> wrote:

Nick Cabatoff <nick.cabatoff@xxxxxxxxx> 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