Indexes

CREATE INDEX without CONCURRENTLY

Lock mode SHARE
Risk MEDIUM
Blocks writes, other DDL
pgfence rule create-index-not-concurrent 
CREATE INDEX idx_orders_status ON orders (status);

What this does

Blocks every INSERT, UPDATE, and DELETE on the table for as long as the index build takes, a single table scan. The read path is unaffected: SHARE does not conflict with ACCESS SHARE.

Safe rewrite

Use CREATE INDEX CONCURRENTLY to allow reads and writes during the build

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_status ON orders (status);
-- Note: CONCURRENTLY must run outside a transaction block (disable ORM transaction wrappers)

Read more

More in Indexes

Run pgfence on your migrations ← All lock mode patterns