← all rules

CREATE INDEX without CONCURRENTLY

Why it's unsafe

CREATE INDEX takes a lock that blocks writes to the table for the entire build. On a large table that can be minutes of blocked writes.

Safe rewrite

Use CREATE INDEX CONCURRENTLY (outside a transaction block). A failed CONCURRENTLY build leaves an INVALID index: drop it with DROP INDEX CONCURRENTLY and retry, or rebuild with REINDEX INDEX CONCURRENTLY.

Unsafe

CREATE INDEX idx_users_email ON users (email);

Safe

CREATE INDEX CONCURRENTLY idx_users_email ON users (email);

Related

• reindex-non-concurrent — REINDEX without CONCURRENTLY takes an ACCESS EXCLUSIVE lock on each index it rebuilds.
• drop-index-non-concurrent — DROP INDEX without CONCURRENTLY takes an ACCESS EXCLUSIVE lock, blocking reads and writes.