Constraints
ADD CONSTRAINT ... CHECK without NOT VALID
Lock mode ACCESS EXCLUSIVE
Risk MEDIUM
Blocks reads, writes, other DDL
pgfence rule
add-constraint-check-no-not-valid ALTER TABLE orders ADD CONSTRAINT orders_status_check CHECK (status IN ('pending','paid','shipped'));What this does
Unlike FOREIGN KEY, a CHECK constraint added without NOT VALID takes the AlterTableGetLockLevel() default: ACCESS EXCLUSIVE, held for the entire validation scan of the existing table.
Safe rewrite
Add the CHECK as NOT VALID, then validate separately
ALTER TABLE orders ADD CONSTRAINT orders_status_check CHECK (status IN ('pending','paid','shipped')) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_status_check;