Constraints
ADD CONSTRAINT ... UNIQUE
Lock mode ACCESS EXCLUSIVE
Risk HIGH
Blocks reads, writes, other DDL
pgfence rule
add-constraint-unique ALTER TABLE orders ADD CONSTRAINT orders_external_id_key UNIQUE (external_id);What this does
UNIQUE (like PRIMARY KEY and EXCLUDE) has no NOT VALID option in PostgreSQL, so the constraint always builds its backing index and validates uniqueness inline, under ACCESS EXCLUSIVE, for the full duration of the build.
Safe rewrite
Build the unique index concurrently first, then attach the constraint to the pre-built index
CREATE UNIQUE INDEX CONCURRENTLY orders_external_id_key_idx ON orders (external_id);
ALTER TABLE orders ADD CONSTRAINT orders_external_id_key UNIQUE USING INDEX orders_external_id_key_idx;