From: Alvaro Herrera <alvherre@commandprompt.com>
To: Bill Moran <wmoran@collaborativefusion.com>
Cc: pgsql-general@postgresql.org
Subject: Re: Index bloat of 4x
Date: Wed, 17 Jan 2007 12:42:51 -0300
Message-ID: <20070117154251.GG26080@alvh.no-ip.org> (raw)
In-Reply-To: <20070117093223.bcdb302e.wmoran@collaborativefusion.com>
References: <20070117093223.bcdb302e.wmoran@collaborativefusion.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'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 :-(
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
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: alvherre@commandprompt.com, wmoran@collaborativefusion.com
Subject: Re: Index bloat of 4x
In-Reply-To: <20070117154251.GG26080@alvh.no-ip.org>
* 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