Re: When/if to Reindex

On 8/24/07, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> You might be able to work around it for now by faking such a reindex
> "by hand"; that is, create a duplicate new index under a different
> name using CREATE INDEX CONCURRENTLY, then exclusive-lock the table
> for just long enough to drop the old index and rename the new one
> to match.

This is a good suggestion, one that we had thought of earlier. Looks like
it might be time to try it out and observe system impact.

> It's probably worth asking also how badly you really need routine
> reindexing. Are you certain your app still needs that with 8.2,
> or is it a hangover from a few releases back? Could more aggressive
> (auto)vacuuming provide a better solution?

Routine reindexing was added (recently, since moving to 8.2) as more of an
optimization than a necessity. If the idea above doesn't work for us or
causes locking issues, then we could always do away with the periodic
reindexing. That would be unfortunate, because reindexing serves to be
quite a nice optimization for us. We've observed up to 40% space savings
(after setting the fillfactor to 100, then reindexing) along with general
improvement in read performance (although hard to quantify).

As mentioned earlier in this thread, we're only reindexing insert-only
partitioned tables, once they're fully loaded.

Thanks for your help.

Steve