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​

On PostgreSQL 18 a not-null constraint is a constraint in its own right — a pg_constraint row with contype = 'n' — and it accepts NOT VALID. So the reading moves into a second statement, where it is cheap:

ALTER TABLE orders ADD CONSTRAINT orders_email_not_null NOT NULL email NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_email_not_null;  -- reads under a WEAK lock

The reading still happens, and it does not get faster: measured on 18.0 over two million rows, the bare SET NOT NULL and the VALIDATE that replaces it both take about 46 ms. What changes is the lock held while it runs — SHARE UPDATE EXCLUSIVE, which permits concurrent reads and writes, instead of one that permits neither. On a table where the reading is minutes rather than milliseconds, that is the difference between an outage and a slow migration.

Between the two statements the catalog says NOT NULL before it is true

The first statement sets the column's attnotnull immediately, so information_schema reports is_nullable = NO while rows that were already there may still be null. Looking at the column will not show you the gap; sqlens:predeploy finds it, because an unvalidated constraint is what it reads. The VALIDATE is what proves the existing rows, and it fails with 23502 if one of them is null.

Below PostgreSQL 18​

The four-statement route is still correct and is what to use on an older server: add CHECK (col IS NOT NULL) unvalidated, validate it, then SET NOT NULL — which since PostgreSQL 12 trusts the validated check and skips its own reading — and finally drop the now-redundant check. SQLens does not flag that sequence.

Flagged​

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

Preferred​

DB::statement('ALTER TABLE orders ADD CONSTRAINT orders_email_not_null NOT NULL email 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 reads the table under a lock. A not-null constraint may be added NOT VALID and validated afterwards, and SET NOT NULL validates an invalid not-null constraint where one is already present

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.