Re: create index concurrently - duplicate index to reduce time without an index

Gareth(dot)Williams(at)csiro(dot)au wrote:
> Re-reading the documentation http://www.postgresql.org/docs/8.4/interactive/sql-reindex.html it is clear now that reindex or recreating and index should not normally be needed - certainly not to keep an index up-to-date. I would have guessed that VACUUM or VACUUM ANALYSE on the table that the index is associated would have been sufficient to reclaim space for a 'bloated' index (maybe only VACUUM FULL would help). In any case we can leave reindexing or full vacuum for outages where we are interrupting service anyway.
>

It is a periodic preventative maintenance operation you can expect to
need occasionally, but certainly not often. Indexes maintain themselves
just fine most of the time. They can get out of whack if you delete a
lot of data out of them and there are some use patterns that tend to a
aggravate the problems here (like tables where you're always inserting
new data and deleting old), but it's certainly not something you run all
the time.

You should read http://wiki.postgresql.org/wiki/VACUUM_FULL to clear up
when it is needed and what the better alternatives are.

> A few times when I was trying to drop an index (before or after creating a duplicate index with 'concurrently'), the dropping of the index stalled. It seems that this was because of existing connection:
> postgres: rods ICAT 130.102.163.141(58061) idle in transaction
> And new clients block. Is that expected? Should we look for 'bugs' in out client that seems to be holding a connection?
>

You do need to be careful that there are no clients connected when you
try this, or yes this is expected behavior. One popular technique is to
put some sort of "block access to the database" switch in the
application itself, specifically to support small outages while keeping
the app from going crazy. You can flip that for a few second around
when you're doing the index switch.

> Ps. Greg, I don't understand the issue with 'concurrently rebuild (of) an index that enforces a constraint or unique index'. I don't think I care much right at the moment, but I'm generally interested and others might be too. Would you expect the create index to fail or to cause locking or just transient performance degradation?
>

The description Alban wrote covers what I was alluding to. You can't
just can't drop an index that supports a constraint, and that has some
(bad) implication for how you can rebuild it.

--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
greg(at)2ndQuadrant(dot)com www.2ndQuadrant.us