Does SET NOT NULL scan the table under an exclusive lock?

SET NOT NULL scanning the table under ACCESS EXCLUSIVE

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

What happens

SET NOT NULL must verify every existing row, and it does so while holding ACCESS EXCLUSIVE, the table is fully unavailable for the scan. The safe path is a CHECK (col IS NOT NULL) NOT VALID constraint, VALIDATE CONSTRAINT (which does not block), then SET NOT NULL, which sees the validated constraint and skips the scan.

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.

Fires on

ALTER TABLE orders ALTER COLUMN region SET NOT NULL;

The safe pattern

SET lock_timeout = '5s';
ALTER TABLE orders ADD CONSTRAINT orders_region_nn
  CHECK (region IS NOT NULL) NOT VALID;
-- later migrations:
--    ALTER TABLE orders VALIDATE CONSTRAINT orders_region_nn;  -- non-blocking
--    ALTER TABLE orders ALTER COLUMN region SET NOT NULL;      -- scan skipped (PG12+)
--    ALTER TABLE orders DROP CONSTRAINT orders_region_nn;

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.

ALTER TABLE orders ALTER COLUMN region SET NOT NULL;

CREATE TABLE audit_log (id bigint, entry text);
ALTER TABLE audit_log ALTER COLUMN entry SET NOT NULL;

How to check locally

npx bolvrk check migration.sql