Adding NOT NULL Constraints to Existing PostgreSQL Columns Safely

Adding a NOT NULL constraint to an existing column is one of the most common schema changes, and one of the most dangerous. The naive approach scans the entire table under an ACCESS EXCLUSIVE lock. On a large table, this causes an outage. Here is the safe pattern. The Problem with SET NOT NULL When you run ALTER TABLE ... ALTER COLUMN ... SET NOT NULL , PostgreSQL must verify that no existing rows have NULL values in that column. To do this, it scans the entire table while holding an ACCESS EXCLUSIVE lock. -- DANGEROUS: Full table scan under ACCESS EXCLUSIVE -- On a 50M row table, this blocks ALL queries for minutes ALTER TABLE users ALTER COLUMN email SET NOT NULL; -- What happens internally: -- 1. Acquires ACCESS EXCLUSIVE lock (blocks everything) -- 2. Scans every row to verify email IS NOT NULL -- 3. If any NULL found: fails with error -- 4. If all rows pass: sets the constraint -- 5. Releases lock -- During step 2, no query can read or write the table ACCESS EXCLUSIVE means nothin