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

-- 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

Run these as two separate migrations. NOT VALID and VALIDATE must not share a transaction; doing both in one tx defeats the safety primitive (the constraint stays write-locked from ADD CONSTRAINT through the full validation scan). pgfence flags same-tx NOT VALID + VALIDATE as an error.

Step 1: Add the constraint without validation (separate migration)

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;

Step 2: Validate existing rows (separate migration)

SET lock_timeout = '2s';

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