R009 · default CAUTION

ALTER COLUMN SET NOT NULL

SET NOT NULL performs a full table scan under AccessExclusiveLock — unless a valid CHECK already proves the constraint.

Why this is a problem

PG 12+ skips the scan if a CHECK (col IS NOT NULL) constraint exists and is VALID. Without it, this is a multi-minute lock on large tables.

Don't do this

ALTER TABLE orders ALTER COLUMN status SET NOT NULL;

Do this instead

-- 1. CHECK constraint NOT VALID (instant)
ALTER TABLE orders ADD CONSTRAINT orders_status_not_null
  CHECK (status IS NOT NULL) NOT VALID;

-- 2. Validate (low-lock)
ALTER TABLE orders VALIDATE CONSTRAINT orders_status_not_null;

-- 3. SET NOT NULL is now fast (PG 12+ uses existing CHECK)
ALTER TABLE orders ALTER COLUMN status SET NOT NULL;
ALTER TABLE orders DROP CONSTRAINT orders_status_not_null;

References

• Adding NOT NULL with minimal locking →