Date: Wed, 17 Jan 2007 12:42:51 -0300
From: Alvaro Herrera <alvherre@commandprompt.com>
To: Bill Moran <wmoran@collaborativefusion.com>
Cc: pgsql-general@postgresql.org
Subject: Re: Index bloat of 4x
Message-ID: <20070117154251.GG26080@alvh.no-ip.org>
Mail-Followup-To: Bill Moran <wmoran@collaborativefusion.com>,
	pgsql-general@postgresql.org
References: <20070117093223.bcdb302e.wmoran@collaborativefusion.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <20070117093223.bcdb302e.wmoran@collaborativefusion.com>
User-Agent: Mutt/1.5.13 (2006-08-11)

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