Re: Index bloat of 4x

In response to Alvaro Herrera <alvherre@commandprompt.com>:

> Bill Moran wrote:
> >
> > We just did a bunch of maintenance on one of our production databases that
> > involved a lot of alter tables and moving records about and the like.
> >
> > Afterwards, I did a vacuum full and analyze to get the database back on
> > track -- autovac maintains it under normal operations.
> >
> > Today I decided to run reindex during a slow period, and was shocked to
> > find the database size drop from 165M to 30M.  Keep in mind that the
> > 165M is after vacuum full.  So, apparently, there was 135M of index bloat?
> > That seems a little excessive to me, especially when the docs claim that
> > reindexing is usually not necessary.
>
> It's been said that vacuum full does not fix index bloat -- in fact,
> it's a problem it worsens.  However, I very much doubt that it would be
> this serious.  I guess the question is, how large was the index *before*
> all the alter tables?

I don't have details on the various indexes.  I do keep an mrtg graph of
pg_database_size(), so I can track the overall size of the database and
correlate it to events.  I'm not tracking individual relations, indexes,
etc though.

The entire database was around 28M prior to the upgrades, etc.  Immediately
after the upgrades, it was ~270M.  Following a vacuum full, it dropped to
165M.  Following a database-wide reindex, it dropped to 30M.

> I'd expect that it was the ALTER TABLEs that caused this much index
> growth, which VACUUM FULL was subsequently unable to fix.
>
> I don't expect you kept a log of index sizes throughout the operation
> however :-(

Not index size, specifically, no.

I can probably reproduce the issue, however.  I have access to the scripts
that were run to update the database, and I can pull a pre-upgrade version
from backup.

I guess my question is whether or not this behaviour is strange enough to
warrant me taking the time to do so.  Just because I've never seen it
before doesn't mean that it's unheard of. ;)

Is this level of index bloat known?  Would it be worthwhile for me to
investigate it and report any details on what's going on or is this a known
factor that folks don't need any additional details on?

--
Bill Moran
Collaborative Fusion Inc.