How to Add an Index Without Locking the Table (Postgres)

CREATE INDEX takes an exclusive lock that blocks writes; CONCURRENTLY builds the index without that lock but cannot run inside a transaction.

A plain CREATE INDEX blocks writes to the table for the whole build. CREATE INDEX CONCURRENTLY builds it in the background, but it must run outside a transaction, so most migration tools need an explicit flag to disable their automatic transaction wrapper.

The statement

-- must not be inside BEGIN/COMMIT
CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders (user_id);

Disable the tool transaction wrapper

# Rails: mark the migration non-transactional
disable_ddl_transaction!

# Django: set atomic = False on the Migration class
# atomic = False

# node-pg-migrate: run with --no-transaction

Gotchas

• A failed CONCURRENTLY build leaves an INVALID index; drop it and retry.
• It is slower and does two table scans, so it costs more total work than a locking build.

Related guides