CHECK constraint added without NOT VALID

Warning — this works, but blocks traffic or rewrites data at scale.

What it catches

Adding a validated CHECK constraint scans the whole table while holding ACCESS EXCLUSIVE — nothing can read or write until the scan finishes. NOT VALID makes the ALTER instant; VALIDATE CONSTRAINT afterwards only takes SHARE UPDATE EXCLUSIVE, which does not block reads or writes.

Fires on

ALTER TABLE orders ADD CONSTRAINT positive CHECK (total >= 0);

Do this instead

Add the CHECK constraint NOT VALID — instant, enforced for new writes — then VALIDATE CONSTRAINT in a later migration. Validation scans under SHARE UPDATE EXCLUSIVE, which blocks neither reads nor writes.

SET lock_timeout = '5s';
ALTER TABLE orders ADD CONSTRAINT positive CHECK (total >= 0) NOT VALID;
-- later migration, non-blocking scan:
--    ALTER TABLE orders VALIDATE CONSTRAINT positive;

Catch this before it ships

This rule runs in the hosted service on Startup and above — add --remote with a team token, or use the GitHub Action: npx bolvrk check migration.sql

Browse all 69 rules

← BV008 BV010 →