Constraints
ADD CONSTRAINT ... UNIQUE USING INDEX
Lock mode ACCESS EXCLUSIVE
Risk LOW
Blocks reads, writes, other DDL
pgfence rule
add-constraint-unique-using-index ALTER TABLE orders ADD CONSTRAINT orders_external_id_key UNIQUE USING INDEX orders_external_id_key_idx;What this does
Attaches a constraint to an index that was already built (typically with CREATE UNIQUE INDEX CONCURRENTLY). The ACCESS EXCLUSIVE lock is still taken, since attaching a constraint is a catalog change, but it is brief because there is no index build left to do.
Safe rewrite
Already using the safe pattern: the index must be built with CONCURRENTLY beforehand
-- Ensure the referenced index was built with CREATE UNIQUE INDEX CONCURRENTLY.