Columns
ALTER COLUMN ... SET NOT NULL
Lock mode ACCESS EXCLUSIVE
Risk MEDIUM
Blocks reads, writes, other DDL
pgfence rule
alter-column-set-not-null ALTER TABLE orders ALTER COLUMN customer_id SET NOT NULL;What this does
Postgres must confirm no existing row is NULL, which is a full table scan held under ACCESS EXCLUSIVE unless a previously validated CHECK constraint already proves it (PostgreSQL 12+ can skip the scan in that case, but the lock is still taken).
Safe rewrite
Add a CHECK constraint as NOT VALID, validate it (non-blocking), then SET NOT NULL skips the scan
-- Migration 1: add constraint without validating (brief ACCESS EXCLUSIVE lock)
ALTER TABLE orders ADD CONSTRAINT chk_customer_id_nn CHECK (customer_id IS NOT NULL) NOT VALID;
-- Migration 2: validate (SHARE UPDATE EXCLUSIVE, allows reads and writes)
ALTER TABLE orders VALIDATE CONSTRAINT chk_customer_id_nn;
-- Migration 3: the validated CHECK lets Postgres skip the full table scan
ALTER TABLE orders ALTER COLUMN customer_id SET NOT NULL;
ALTER TABLE orders DROP CONSTRAINT chk_customer_id_nn;