Skip to main content

PG.L2.CONSTRAINT_NOT_VALIDATED — Adding a constraint scans every row under a lock

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

Adding a check or foreign-key constraint the ordinary way makes PostgreSQL verify every existing row before the statement returns — while holding its lock. The statement looks like a metadata change and behaves like a full table scan.

For a foreign key it is worse than that, and it is the part people miss: the server takes an ACCESS EXCLUSIVE lock on both tables — the referencing one and the one it references — while it installs the enforcing triggers. Adding a key to a small table can therefore lock a large, busy one that the migration never mentions.

The two-step​

ALTER TABLE orders ADD CONSTRAINT … NOT VALID;   -- fast: metadata only, no scan
ALTER TABLE orders VALIDATE CONSTRAINT …;         -- the scan, under a weaker lock

NOT VALID means "enforce this for new and changed rows, and do not check the ones already here". VALIDATE CONSTRAINT then does the scan under a SHARE UPDATE EXCLUSIVE lock, which permits concurrent reads and writes.

Flagged​

DB::statement('ALTER TABLE orders ADD CONSTRAINT orders_customer_id_fkey FOREIGN KEY (customer_id) REFERENCES customers (id)');

Preferred​

DB::statement('ALTER TABLE orders ADD CONSTRAINT orders_customer_id_fkey FOREIGN KEY (customer_id) REFERENCES customers (id) NOT VALID');
DB::statement('ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_id_fkey');

Both halves may live in one migration. Splitting them across two deploys is even safer on a large table, because the validation pass is the slow one and it no longer blocks anything.

What is not flagged​

A table born in this migration. Schema::create() followed by foreignId()->constrained() emits the create and then the ALTER TABLE, and the form this rule recommends would buy nothing there: the only thing NOT VALID skips is the scan of rows already in the altered table, and a table two statements old has none. The locks — including the one on a live referenced table — are taken and released identically either way.

The exception is scoped to what it can justify. A table that the same migration fills before constraining it (create, backfill, constrain) is flagged as usual, because there the scan is real and runs while the referenced table waits for it.

Do not stop at NOT VALID​

A constraint left NOT VALID forever is a different problem: the server enforces it going forward but has never confirmed the existing rows, so the guarantee you think you have is only partial — and query planning cannot rely on it. That state is tracked as a migration debt, and the deploy suite's debt ledger reports it rather than letting it quietly become permanent.

Sources​

An outage on the record​

Adding a foreign key against a table with a long-running read on it queued behind that read, and the queued ACCESS EXCLUSIVE lock then blocked everything arriving after it — about fifteen seconds of unplanned API downtime from a migration that changed no data. This rule refuses the shape that takes that lock.

See outages on the record for the write-up and the second rule that would have stopped the same incident independently.

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.