Constraints
ADD CONSTRAINT ... PRIMARY KEY USING INDEX
Lock mode ACCESS EXCLUSIVE
Risk MEDIUM
Blocks reads, writes, other DDL
pgfence rule
add-pk-using-index ALTER TABLE orders ADD CONSTRAINT orders_pkey PRIMARY KEY USING INDEX orders_pkey_idx;What this does
The index build itself is already done, but Postgres still needs to prove every indexed column is NOT NULL (a primary key requires it). If nullability was not already proven by an earlier constraint, that check can scan the table under the ACCESS EXCLUSIVE lock this statement holds.
Safe rewrite
Prove the indexed columns are NOT NULL before attaching the primary key
-- Ensure every indexed column is already NOT NULL or validated by a prior constraint.
ALTER TABLE orders ADD CONSTRAINT orders_pkey PRIMARY KEY USING INDEX orders_pkey_idx;