Does adding a CHECK constraint scan and lock the table?

CHECK constraint added without NOT VALID

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

What happens

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.

Why it is dangerous on a populated table

It completes, but the time it holds its lock or rewrites rows scales with the table, and on a busy one that is long enough for traffic to queue.

Measured

Both forms ran against the same table (20M rows, 1.7 GB) on Postgres 18.6, with one session inserting a row and another reading one row every 50 ms. Worst wait is the slowest of those probe calls while the statement ran. This run is from Sep 16, 2026; the whole benchmark has every scenario and how to run it.

	Statement	Time	Worst write wait	Worst read wait	Lock
Fires	ADD CONSTRAINT ... CHECK (scans under lock)	1.0 s	973 ms	972 ms	AccessExclusiveLock
Safe	NOT VALID, then VALIDATE CONSTRAINT	1.0 s	2 ms	1 ms	ShareUpdateExclusiveLock

Fires on

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

The safe pattern

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;

Fixtures

The rule ships with these files and the test suite runs them on every change: the first set must fire, the second must stay silent.

Fires (1)

validated check

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

Stays silent (1)

not valid check

ALTER TABLE orders ADD CONSTRAINT orders_total_positive CHECK (total >= 0) NOT VALID;

How to check locally

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. No install, nothing leaves your machine:

npx bolvrk check migration.sql --remote

Browse all 135 rules

← BV008 BV010 →