Postgres can, Oracle can't (create duplicate indexes)

This is in the series of "Postgres can, Oracle can't" articles. This one is not really something Oracle can't do, but probably something they chose not to.

If you try to create an index on a set of columns that already have an index, then Oracle will throw an error. But Postgres allows you to do this (probably because of partial indexes, or probably in the spirit of Open Source).

This can be leveraged in routine REINDEX operations. If you try to reindex an index in Postgres, it takes such a lock on the underlying table that INSERT/UPDATE/DELETE operations on that table are blocked, hence causing probable application downtime.

So, combining three of Postgres' unique features, we can re-index indexes without causing application downtime; these features are:
1. DDL obeys transactions
2. Ability to create duplicate indexes
3. Ability to create indexes concurrently.

CREATE INDEX CONCURRENTLY command allows you to create an index in such a way that other sessions are allowed INSERT/UPDATE/DELETE operations.

/* Concurrent index creation canot work in a transaction */

create index concurrently temp_emp_deptno on emp(dept);
begin transaction;
alter index emp_deptno rename to dropped_emp_deptno;
alter index temp_emp_deptno rename to emp_dropped;
drop index dropped_emp_deptno;
commit transaction;

Please refer to he CREATE INDEX documentation for caveats of using CREATE INDEX CONCURRENTLY command. Also, this method does not lend itself to reindexing Primary Key indexes.