Adding NOT NULL to an existing column requires a full table scan and exclusive lock.
ALTER TABLE ... SET NOT NULL acquires an ACCESS EXCLUSIVE lock and scans every row to verify the constraint, causing downtime on large tables.
BeforeMerge scans your pull requests against this rule and 3+ others. Get actionable feedback before code ships.
Adding NOT NULL to an existing column requires PostgreSQL to scan every row to verify no NULLs exist. This holds an ACCESS EXCLUSIVE lock for the duration.
Squawk catches this:
squawk migration.sql
# Outputs: adding-not-nullable-fieldUse a CHECK constraint with NOT VALID, then validate separately:
-- Step 1: Add constraint without validating (instant, no lock)
ALTER TABLE users
ADD CONSTRAINT users_email_not_null
CHECK (email IS NOT NULL) NOT VALID;
-- Step 2: Validate in a separate transaction (concurrent, no exclusive lock)
ALTER TABLE users
VALIDATE CONSTRAINT users_email_not_null;
-- Step 3: Now safe to add NOT NULL (PG knows all rows pass)
ALTER TABLE users ALTER COLUMN email SET NOT NULL;
ALTER TABLE users DROP CONSTRAINT users_email_not_null;adding-not-nullable-field, adding-field-with-default