From: Bill Moran <wmoran@collaborativefusion.com>
To: Decibel! <decibel@decibel.org>
Cc: Steven Flatt <steven.flatt@gmail.com>
Cc: Vivek Khera <vivek@khera.org>
Cc: Pgsql performance <pgsql-performance@postgresql.org>
Subject: Re: When/if to Reindex
Date: Thu, 9 Aug 2007 09:04:11 -0400
Message-ID: <20070809090411.9c2c3014.wmoran@collaborativefusion.com> (raw)
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>
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
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-performance@postgresql.org
Cc: wmoran@collaborativefusion.com, decibel@decibel.org, steven.flatt@gmail.com, vivek@khera.org
Subject: Re: When/if to Reindex
In-Reply-To: <20070809090411.9c2c3014.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