R004 · default CAUTION

CREATE INDEX without CONCURRENTLY

CREATE INDEX (without CONCURRENTLY) acquires a ShareLock that blocks writes until the index is built.

Why this is a problem

On a 100M-row B-tree index, this is multi-minute write downtime. CREATE INDEX CONCURRENTLY runs without blocking writes (uses MVCC snapshot) and is ~2× slower — but no downtime.

Don't do this

CREATE INDEX orders_status_idx ON orders (status);

Do this instead

-- Must run OUTSIDE a transaction:
-- Alembic: postgresql_concurrently=True
-- Rails: disable_ddl_transaction!
CREATE INDEX CONCURRENTLY IF NOT EXISTS orders_status_idx ON orders (status);

References

• PG: Building Indexes Concurrently →