Columns
ADD COLUMN ... REFERENCES (inline foreign key)
Lock mode ACCESS EXCLUSIVE
Risk HIGH
Blocks reads, writes, other DDL
pgfence rule
add-column-inline-foreign-key Also takes a SHARE ROW EXCLUSIVE lock on the referenced table (blocks writes, other DDL).
ALTER TABLE orders ADD COLUMN customer_id bigint REFERENCES customers(id);What this does
An inline REFERENCES clause on ADD COLUMN takes ACCESS EXCLUSIVE on the table being altered and also takes SHARE ROW EXCLUSIVE on the referenced table to validate the constraint immediately, blocking writes on both tables for the duration.
Safe rewrite
Add the column first, then add the foreign key as NOT VALID and validate separately
ALTER TABLE orders ADD COLUMN IF NOT EXISTS customer_id bigint;
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_customer_id ON orders (customer_id);
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;