How to Add Foreign Keys to Large PostgreSQL Tables Without Blocking Writes

Adding a foreign key to a large PostgreSQL table using a single ALTER TABLE statement locks both tables for the entire duration of validation, which can block writes for minutes on busy systems. PostgreSQL offers a two-step alternative: first add the constraint with NOT VALID to record it instantly with only a brief lock, then run VALIDATE CONSTRAINT separately under a weaker lock that still permits inserts, updates, and deletes. The end result is the same validated constraint, but with far less disruption to live traffic. Additionally, PostgreSQL does not automatically index the referencing column of a foreign key, so creating one with CREATE INDEX CONCURRENTLY is recommended to prevent slow sequential scans during parent-row deletions. The same NOT VALID and validate pattern also applies to CHECK constraints and can be used to safely enforce NOT NULL on large tables without an expensive full-table lock.