Date: Thu, 9 Aug 2007 09:04:11 -0400
From: Bill Moran <wmoran@collaborativefusion.com>
To: "Decibel!" <decibel@decibel.org>
Cc: Steven Flatt <steven.flatt@gmail.com>, Vivek Khera <vivek@khera.org>,
	Pgsql performance <pgsql-performance@postgresql.org>
Subject: Re: When/if to Reindex
Message-Id: <20070809090411.9c2c3014.wmoran@collaborativefusion.com>
In-Reply-To: <20070808203554.GP20424@nasby.net>
References: <357fa7590707181008k65ab2f20l55bebb3c14f52770@mail.gmail.com>
	<38118C09-FC0A-4CCB-9461-FC6D29D81EE4@khera.org>
	<357fa7590708081212p7b19cbbbn39dbadae8603edd6@mail.gmail.com>
	<20070808152757.9eb9bbcc.wmoran@collaborativefusion.com>
	<20070808203554.GP20424@nasby.net>
Organization: Collaborative Fusion
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

In response to "Decibel!" <decibel@decibel.org>:

> On Wed, Aug 08, 2007 at 03:27:57PM -0400, Bill Moran wrote:
> > I've had similar experience.  One thing you didn't mention that I've noticed
> > is that VACUUM FULL often bloats indexes.  I've made it SOP that
> > after application upgrades (which usually includes lots of ALTER TABLES and
> > other massive schema and data changes) I VACUUM FULL and REINDEX (in that
> > order).
> 
> You'd be better off with a CLUSTER in that case. It'll be faster, and
> you'll ensure that the table has optimal ordering.

Point taken.

> > Lots of ALTER TABLEs seem to bloat the database size considerably, beyond
> > what normal VACUUM seems to fix.  A FULL seems to fix that, but it appears
> > to bloat the indexes, thus a REINDEX helps.
> 
> Hrm, are you sure that's still true? I just did an ALTER TABLE ... TYPE
> and it created a new file, meaning no bloating.

No, I'm not.  This isn't something I've analyzed or investigated in detail.
During upgrades, a lot happens: ATLER TABLES, tables are dropped, new tables
are created, massive amounts of data may be altered in a short period, stored
procedures are replaced, etc, etc.

I don't remember what led me to believe that the ALTER TABLES were causing the
worst of the problem, but it's entirely possible that I was off-base.  (I seem
to remember being concerned about too many DROP COLUMN and ADD COLUMNs)  In any
event, my original statement (that it's a good idea to REINDEX after VACUUM
FULL) still seems to be correct.

-- 
Bill Moran
Collaborative Fusion Inc.
http://people.collaborativefusion.com/~wmoran/

wmoran@collaborativefusion.com
Phone: 412-422-3463x4023