From: Bill Moran <wmoran@collaborativefusion.com>
To: Alvaro Herrera <alvherre@commandprompt.com>
Cc: pgsql-general@postgresql.org
Subject: Re: Index bloat of 4x
Date: Wed, 17 Jan 2007 11:32:16 -0500
Message-ID: <20070117113216.a6d72185.wmoran@collaborativefusion.com> (raw)
In-Reply-To: <20070117154251.GG26080@alvh.no-ip.org>
References: <20070117093223.bcdb302e.wmoran@collaborativefusion.com>
<20070117154251.GG26080@alvh.no-ip.org>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-general@postgresql.org
Cc: wmoran@collaborativefusion.com, alvherre@commandprompt.com
Subject: Re: Index bloat of 4x
In-Reply-To: <20070117113216.a6d72185.wmoran@collaborativefusion.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox