Constraints
ADD CONSTRAINT ... FOREIGN KEY without NOT VALID
Lock mode SHARE ROW EXCLUSIVE
Risk HIGH
Blocks writes, other DDL
pgfence rule
add-constraint-fk-no-not-valid ALTER TABLE orders ADD CONSTRAINT orders_customer_id_fkey FOREIGN KEY (customer_id) REFERENCES customers(id);What this does
Foreign keys are the one constraint type PostgreSQL documents as lighter than ACCESS EXCLUSIVE: SHARE ROW EXCLUSIVE on both the referencing and referenced tables, so reads keep flowing. Writes on both tables still block for the full validation scan, and SHARE ROW EXCLUSIVE is self-exclusive, so a second FOREIGN KEY or CREATE TRIGGER on either table has to wait too.
Safe rewrite
Add the FK as NOT VALID, then validate separately under a much lighter lock
ALTER TABLE orders ADD CONSTRAINT orders_customer_id_fkey FOREIGN KEY (customer_id) REFERENCES customers(id) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_id_fkey;
-- VALIDATE CONSTRAINT only requires SHARE UPDATE EXCLUSIVE, which allows concurrent reads and writes but blocks other schema changes.