Unique Constraints

ADD CONSTRAINT ... UNIQUE takes ACCESS EXCLUSIVE and does a full table scan. Use a concurrent index instead.

The Problem

sql

-- ACCESS EXCLUSIVE lock, full table scan
ALTER TABLE users ADD CONSTRAINT uq_users_email UNIQUE (email);

Safe Rewrite

Run these as two separate migrations. CREATE INDEX CONCURRENTLY cannot run inside a transaction, so it must ship in its own migration with the ORM transaction wrapper disabled.

Step 1: Build the index concurrently (separate migration, no transaction)

sql

SET lock_timeout = '2s';

-- Non-blocking, allows reads + writes during the build.
-- This statement must run outside a transaction block.
CREATE UNIQUE INDEX CONCURRENTLY IF NOT EXISTS uq_users_email_idx ON users(email);

Step 2: Attach the constraint (separate migration)

sql

SET lock_timeout = '2s';

  -- Brief ACCESS EXCLUSIVE lock, no table scan because the index already exists.
ALTER TABLE users ADD CONSTRAINT uq_users_email UNIQUE USING INDEX uq_users_email_idx;

Important: If your ORM auto-wraps every migration in BEGIN/COMMIT, disable the wrapper for Step 1. pgfence flags CREATE INDEX CONCURRENTLY inside a transaction as an error.