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

Excerpts from Gareth.Williams's message of mar jun 01 02:44:35 -0400 2010:
> Hi,
>
> We want to reindex the database behind a production service without interrupting the service.
>
> I had an idea for creating the index with a new name then dropping the existing index and renaming the new one - and it seems to work and would reduce the time without an index to be minimal. I tried:
> psql -d ICAT -c 'create unique index concurrently tmp_idx_objt_access1
> on R_OBJT_ACCESS (object_id,user_id);'
> # would check if that worked before proceeding #
> psql -d ICAT -c 'drop index idx_objt_access1;'
> psql -d ICAT -c 'alter index tmp_idx_objt_access1 rename to idx_objt_access1;'

Note that you should wait until the new index becomes usable before
deleting the old one; otherwise you could have an intermediate period
during which you have no index.

IIRC a concurrently created index does not become usable until the
oldest transaction that was current when index creation started has
finished (IOW the transaction in pg_index.indcheckxmin has gone).

--
Álvaro Herrera <alvherre(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support