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

2010/6/1 <Gareth(dot)Williams(at)csiro(dot)au>

> 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;'
>
> But then a colleague pointed out that maybe the name of the index is not
> meaningful and this might not be any use.
>
> Can any experts confirm the validity of this approach or shoot it down?
>
>
The index name is not used for planning query execution (most important
thing is the index definition), but of course it is important to have some
logical name convention that usually helps in fast understanding database
schema. Name the index as you want, database really doesn't understand the
names.

regards
Szymon Guz