Unique Constraints

Name: pgfence
Author: pgfence

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

The Problem

sql

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

Safe Rewrite

sql

-- Step 1: Build the index concurrently (non-blocking, allows reads + writes)
CREATE UNIQUE INDEX CONCURRENTLY IF NOT EXISTS uq_users_email_idx ON users(email);

-- Step 2: Attach constraint using the pre-built index (brief lock, no scan)
SET lock_timeout = '2s';
ALTER TABLE users ADD CONSTRAINT uq_users_email UNIQUE USING INDEX uq_users_email_idx;