Skip to main content

PG.L2.SET_NOT_NULL_SCAN — Proving no row is null costs a full scan

  • Category: safety
  • Level: 2
  • Confidence: deterministic
  • Downtime class: blocking
  • Stability: stable
  • Suites: lint
  • Applies to: PostgreSQL 18

SET NOT NULL cannot be a metadata change, because the server has to prove the claim: it scans every existing row to confirm none of them is null, and it holds an ACCESS EXCLUSIVE lock while it does. Reads and writes both wait.

The scan is the whole cost. The constraint itself is free.

The route around it, and why it works​

Since PostgreSQL 12 the server will accept a validated CHECK (col IS NOT NULL) as proof and skip the scan when the column is subsequently set NOT NULL. So the expensive step can be moved into the one place it is cheap:

ALTER TABLE orders ADD CONSTRAINT orders_email_not_null CHECK (email IS NOT NULL) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_email_not_null;  -- scans under a WEAK lock
ALTER TABLE orders ALTER COLUMN email SET NOT NULL;            -- now instant

The scan still happens. It happens under SHARE UPDATE EXCLUSIVE, which permits concurrent reads and writes, instead of under a lock that permits neither.

Flagged​

DB::statement('ALTER TABLE orders ALTER COLUMN email SET NOT NULL');

Preferred​

DB::statement('ALTER TABLE orders ADD CONSTRAINT orders_email_not_null CHECK (email IS NOT NULL) NOT VALID');
DB::statement('ALTER TABLE orders VALIDATE CONSTRAINT orders_email_not_null');

Backfill before you constrain​

None of this helps if rows are actually null — the validation will simply fail, which is the correct outcome and a much better place to find out than mid-deploy. Backfill in bounded batches first, in its own migration, and remember that a backfill is itself write load: see GEN.L1.DML_WITHOUT_WHERE and the batching guidance there.

The order matters​

Adding the column, backfilling it and constraining it in one migration puts the scan back where it started — the constraint sees a table it just wrote to, under a transaction that has been open the whole time. Split the deploys.

Sources​

  • PostgreSQL 18 — ALTER TABLE — SET NOT NULL verifies that no existing row holds a null, and that verification is a table scan under a lock; since PostgreSQL 12 a validated CHECK (col IS NOT NULL) lets the server skip it

The fix material this rule carries​

A finding from this rule carries machine-readable fix material. Which sequence depends on the statement:

  • concurrently — Build the index without taking the write lock the ordinary form takes.
  • not_valid_then_validate — Add the constraint unvalidated, then validate it in a second migration.

The payload is material for you or an agent to apply. SQLens writes no migration and runs no DDL. See the remediation payload for every field, the placeholder semantics, and the version rules a consumer has to follow.