Adding NOT NULL via a CHECK Constraint in Postgres

The backfill finished: every one of the 250 million rows in events now has a tenant_id, and the last step of the expand-and-contract plan is to make the column NOT NULL. ALTER TABLE events ALTER COLUMN tenant_id SET NOT NULL looks like a metadata change, but it is not. PostgreSQL must prove there are no NULLs, so it scans the entire table while holding ACCESS EXCLUSIVE — no reads, no writes — for as long as the scan takes, which on this table is several minutes. Since PostgreSQL 12 there is a way around it: if a validated CHECK (tenant_id IS NOT NULL) constraint already exists, SET NOT NULL uses it as proof and skips the scan. And a check constraint can be added NOT VALID and validated online. This guide walks through the three statements that turn a multi-minute outage into three brief locks and one non-blocking scan. It belongs to Adding Constraints Without Downtime.

Four Statements Instead of One Four steps. Add CHECK tenant_id IS NOT NULL NOT VALID, instant. VALIDATE CONSTRAINT scans online under SHARE UPDATE EXCLUSIVE. SET NOT NULL finds the validated check and skips its scan, instant. Drop the now-redundant check constraint, instant. Four Statements Instead of One STEP 1 CHECK … NOT VALID instant STEP 2 VALIDATE online scan STEP 3 SET NOT NULL proof found, no scan STEP 4 Drop the check redundant now
The scan moves from SET NOT NULL, where it blocks everything, to VALIDATE, where it blocks nothing.

Symptom / Error Signatures

You need this technique when:

  • ALTER TABLE ... SET NOT NULL on a large table appears in a migration, and in pg_stat_activity it runs for minutes while other sessions queue with wait_event_type = 'Lock'.
  • A migration tool generated SET NOT NULL as the final step of adding a required column — for example Rails’ change_column_null, Django’s AlterField to null=False, or Prisma making a field required.
  • strong_migrations reports Setting NOT NULL on an existing column blocks reads and writes while every row is checked.

If the scan runs and finds a NULL, it fails after doing all the work:

ERROR:  column "tenant_id" of relation "events" contains null values

Root Cause Analysis

NOT NULL is a column attribute, not a named constraint, and PostgreSQL has no NOT VALID form for it. To set it, the server must be sure no existing row violates it, and until PostgreSQL 12 the only way was to scan the table under ACCESS EXCLUSIVE. PostgreSQL 12 added an optimisation: when setting NOT NULL, the planner checks whether an existing, validated CHECK constraint already implies it. A constraint of the form CHECK (col IS NOT NULL) does, so the scan is skipped and SET NOT NULL becomes a catalog update.

CHECK constraints can be added NOT VALID (brief lock, no scan, enforced for new writes) and validated with VALIDATE CONSTRAINT, which scans under SHARE UPDATE EXCLUSIVE and allows concurrent reads and writes. Combining the two features gives an online path.

Statement Lock Scan Duration on 250M rows
SET NOT NULL (no proof) ACCESS EXCLUSIVE yes minutes, all traffic blocked
ADD CONSTRAINT ... CHECK (...) NOT VALID ACCESS EXCLUSIVE, brief no milliseconds
VALIDATE CONSTRAINT SHARE UPDATE EXCLUSIVE yes minutes, traffic continues
SET NOT NULL (validated check exists) ACCESS EXCLUSIVE, brief no milliseconds
DROP CONSTRAINT (the check) ACCESS EXCLUSIVE, brief no milliseconds

The check must be exactly col IS NOT NULL (additional conjuncts are fine, but a different expression such as col <> '' does not prove non-nullness). On PostgreSQL 11 and earlier the optimisation does not exist; there, the validated check itself is the guarantee and you can leave it in place instead of converting to NOT NULL.

Where the Exclusive Lock Is Held Timeline over 7 minutes. Plain SET NOT NULL holds ACCESS EXCLUSIVE for about 6 minutes of scanning. The check-constraint path holds ACCESS EXCLUSIVE three times for milliseconds each (add, set not null, drop), with a 6-minute validation in between under SHARE UPDATE EXCLUSIVE. Where the Exclusive Lock Is Held Plain SET NOT NULL ACCESS EXCLUSIVE + scan Check path: locks Check path: VALIDATE SHARE UPDATE EXCLUSIVE scan 0 1 min 3 min 5 min 7 min exclusive lock online scan
Three sub-second exclusive locks and one harmless scan replace six minutes of total blockage.

Immediate Mitigation

1. Cancel a blocking SET NOT NULL if one is running. It is transactional; cancelling it rolls back cleanly and releases the table.

-- PostgreSQL · requires pg_signal_backend
SELECT pid, now() - query_start AS running FROM pg_stat_activity
WHERE query ILIKE '%SET NOT NULL%' AND state = 'active';
SELECT pg_cancel_backend(<pid>);

2. Confirm there are no NULLs left. Validation will fail on the first one after scanning; check first, ideally on a replica.

-- PostgreSQL · read-only · uses an index on tenant_id if one exists
SELECT count(*) FROM events WHERE tenant_id IS NULL;

3. Add the check as NOT VALID. It is enforced for new writes from this moment, so the application must already always set the column.

-- PostgreSQL 12+ · migration role · brief ACCESS EXCLUSIVE
-- WARNING: inserts or updates that leave tenant_id NULL fail from now on.
BEGIN;
SET LOCAL lock_timeout = '3s';
ALTER TABLE events ADD CONSTRAINT events_tenant_id_nn CHECK (tenant_id IS NOT NULL) NOT VALID;
COMMIT;
-- ROLLBACK PATH: ALTER TABLE events DROP CONSTRAINT IF EXISTS events_tenant_id_nn;

4. Validate, then promote and tidy up.

-- PostgreSQL 12+ · migration role · run VALIDATE on its own, then the short transaction
SET lock_timeout = '3s';
ALTER TABLE events VALIDATE CONSTRAINT events_tenant_id_nn;   -- online scan

BEGIN;
SET LOCAL lock_timeout = '3s';
ALTER TABLE events ALTER COLUMN tenant_id SET NOT NULL;       -- instant: uses the validated check
ALTER TABLE events DROP CONSTRAINT events_tenant_id_nn;       -- redundant after SET NOT NULL
COMMIT;
-- ROLLBACK PATH: ALTER TABLE events ALTER COLUMN tenant_id DROP NOT NULL;

Permanent Fix / Long-Term Pattern

Adopt the four-statement sequence as the only way to add NOT NULL to an existing column on any table larger than a few hundred thousand rows. Place it at the end of the expand-and-contract lifecycle: add the column nullable, deploy code that always writes it, backfill old rows, then tighten — the order described in Backfill Optimization and used throughout Expand and Contract Methodology. Put the NOT VALID addition in one migration and the validation plus promotion in the next, so a problem discovered after enforcement begins can be fixed before the scan runs.

Teach your tools the pattern. ORMs generate the one-statement form; override it with hand-written SQL or the framework’s constraint operations, as covered for Django in adding a non-null field in Django without locking and for Prisma in customizing Prisma migrations for zero downtime. A lint rule that flags SET NOT NULL not preceded by a validated check on the same column catches regressions.

Which Path for NOT NULL? Decision tree. If the table is small, plain SET NOT NULL is fine. Otherwise, if the server is PostgreSQL 12 or newer, use the check-constraint path and convert to NOT NULL. If older, add and validate the check and keep it as the guarantee. Which Path for NOT NULL? Table larger than ~100k rows? yes no PostgreSQL 12 or newer? yes no CHECK NOT VALID → VALIDATE → SET NOT NULL CHECK NOT VALID → VALIDATE, keep the check Plain SET NOT NULL, with lock_timeout
On PostgreSQL 11 and earlier the validated check is the end state; on 12+ it is a stepping stone to a real NOT NULL.

Verification Checklist

Frequently Asked Questions

Why doesn’t PostgreSQL have SET NOT NULL NOT VALID? NOT NULL is stored as a column attribute rather than as a separately named constraint with a validity flag, so it cannot exist in an unvalidated state. The CHECK (col IS NOT NULL) constraint fills that role, and PostgreSQL 12+ recognises it as proof.

Is the helper check constraint needed after SET NOT NULL? No. Once the column is NOT NULL, the check is redundant and adds a small cost to every write, so drop it. On PostgreSQL 11 and earlier, where SET NOT NULL would still scan, keep the check as the enforcement mechanism instead.

Does VALIDATE CONSTRAINT block writes? No. It takes SHARE UPDATE EXCLUSIVE, which allows inserts, updates and deletes. It does conflict with other schema changes, VACUUM and concurrent index builds on the same table, so avoid running them at the same time.

What if new NULLs appear during validation? They cannot: the NOT VALID constraint already rejects inserts and updates that would set the column to NULL. Validation only has to check rows that existed before it was added.

Related Articles