CREATE INDEX

Name: pgfence
Author: pgfence

CREATE INDEX takes a SHARE lock which blocks all writes for the duration of the index build. Use CONCURRENTLY to allow writes during the build.

The Problem

sql

-- SHARE lock, blocks all writes while index is built
CREATE INDEX idx_users_email ON users(email);

Safe Rewrite

sql

-- CONCURRENTLY: no lock on writes, index built in background
-- Must run OUTSIDE a transaction block
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_users_email ON users(email);

Important: CREATE INDEX CONCURRENTLY cannot run inside a transaction. pgfence will flag it as an error if it detects it inside a BEGIN/COMMIT block.