ADD COLUMN + NOT NULL

Adding a NOT NULL column with a default is one of the most common production incidents in Postgres. Here is the correct expand/contract pattern.

The Problem

sql 
-- ACCESS EXCLUSIVE lock for the full duration of a table rewrite
ALTER TABLE users ADD COLUMN email_verified BOOLEAN NOT NULL DEFAULT false;

Safe Rewrite (3-Step)

Step 1: Add nullable column (instant)

sql 
SET lock_timeout = '2s';
ALTER TABLE users ADD COLUMN IF NOT EXISTS email_verified BOOLEAN;

Step 2: Backfill out-of-band (not in a migration)

Run this as a background job, not inside a migration. Use the CTE pattern to avoid UPDATE … LIMIT, which PostgreSQL does not support:

sql 
-- Repeat until 0 rows updated
WITH batch AS (
  SELECT id FROM users
  WHERE email_verified IS NULL
  ORDER BY id
  LIMIT 1000
  FOR UPDATE SKIP LOCKED
)
UPDATE users u
SET email_verified = false
FROM batch
WHERE u.id = batch.id;

Step 3: Add NOT NULL constraint (non-blocking scan)

sql 
SET lock_timeout = '2s';

-- NOT VALID skips the full table scan (brief ACCESS EXCLUSIVE for metadata only)
ALTER TABLE users
  ADD CONSTRAINT chk_email_verified_nn
  CHECK (email_verified IS NOT NULL) NOT VALID;

-- VALIDATE takes SHARE UPDATE EXCLUSIVE, does not block reads or writes
ALTER TABLE users VALIDATE CONSTRAINT chk_email_verified_nn;

-- Now safe to set NOT NULL (Postgres skips the scan when constraint exists)
ALTER TABLE users ALTER COLUMN email_verified SET NOT NULL;

-- Drop the helper constraint
ALTER TABLE users DROP CONSTRAINT chk_email_verified_nn;