On 8/24/07, Tom Lane <tgl@xxxxxxxxxxxxx> 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
