Re: When/if to Reindex

In response to "Decibel!" <decibel(at)decibel(dot)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(at)collaborativefusion(dot)com
Phone: 412-422-3463x4023