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.

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.