Why use CREATE INDEX CONCURRENTLY on a production table, and what are its caveats?

Use CREATE INDEX CONCURRENTLY because a plain CREATE INDEX blocks writes. The plain version takes a lock that blocks inserts, updates and deletes on the table until the build finishes. On a big table, that can mean minutes of failed writes. Reads still work.

CREATE INDEX CONCURRENTLY builds the index while normal reads and writes continue. To do that, it scans the table twice. It also waits for existing transactions that could use the index to finish. So it is slower and uses more resources.

Caveats:

• It cannot run inside a transaction block. This matters for migration tools that wrap each migration in a transaction.
• If it fails, for example on a unique violation or a deadlock, it leaves an INVALID index. That index still slows down writes. Drop it and retry.
• Long-running transactions delay it.

DROP INDEX CONCURRENTLY and REINDEX CONCURRENTLY exist for the same reasons.