Foreign Keys

Adding a foreign key without NOT VALID takes SHARE ROW EXCLUSIVE on both the source and referenced table and scans the entire table to validate existing rows.

The Problem

sql 
-- SHARE ROW EXCLUSIVE on posts AND users, full table scan
ALTER TABLE posts ADD CONSTRAINT fk_posts_user_id
  FOREIGN KEY (user_id) REFERENCES users(id);

Safe Rewrite

sql 
SET lock_timeout = '2s';

-- NOT VALID: brief SHARE ROW EXCLUSIVE for metadata only, no table scan
ALTER TABLE posts ADD CONSTRAINT fk_posts_user_id
  FOREIGN KEY (user_id) REFERENCES users(id) NOT VALID;

-- VALIDATE: SHARE UPDATE EXCLUSIVE, non-blocking scan of existing rows
ALTER TABLE posts VALIDATE CONSTRAINT fk_posts_user_id;