Trying to deal with a bloated index

From: Jack Orenstein <jack(dot)orenstein(at)hds(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Trying to deal with a bloated index
Date: 2009-02-19 20:58:23
Message-ID: 499DC7EF.7080404@hds.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I have a postgres application, with customers using version 7.4 and the next
version of the product being built on 8.3 (with HOT). We are having problems
with two bloated indexes. One index is on a field whose value never changes.
The other, which bloats more severely, is on a field whose value changes on
every update.

If I have to, I'll do periodic reindexing. Trying to hide that will be somewhat
tricky.

But I'm wondering about an alternative approach. Suppose I have table T and an
index on T, idx1(some_column). Instead of reindexing idx1 how would well would
this work:

- Create a second index, idx2(some_column), using the CONCURRENTLY option.

- When idx2 is ready, drop idx1.

Questions:

- How well does the CONCURRENTLY option work? The documentation on REINDEX says
"An index build with the CONCURRENTLY option failed ..." I'm not sure if I'm
supposed to read this as a comment on the reliability of the CONCURRENTLY option.

- How much of a drag on performance will be caused by the creation of idx2?

- How will the optimizer deal with this approach? When idx2 is ready and idx1 is
dropped, will the optimizer immediately switch over? What about optimization of
any prepared statements on connections whose lifetime spans the creation of idx2?

Jack Orenstein

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2009-02-19 21:43:26 Re: Trying to deal with a bloated index
Previous Message Frank Featherlight 2009-02-19 20:13:44 Re: Service not starting during install