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​

-- migration 1
ALTER TABLE orders ADD CONSTRAINT … NOT VALID;   -- fast: metadata only, no scan
-- migration 2
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, as long as it runs in a transaction of its own.

Flagged​

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

Also flagged, on the VALIDATE: both halves in one migration.

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');

Preferred​

// Migration 1 — add the key NOT VALID. New rows are checked at once, and the lock is brief:
DB::statement('ALTER TABLE orders ADD CONSTRAINT orders_customer_id_fkey FOREIGN KEY (customer_id) REFERENCES customers (id) NOT VALID');

// Migration 2 — validate it. In a transaction of its own the scan takes only SHARE UPDATE EXCLUSIVE:
DB::statement('ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_id_fkey');

The two halves need two migrations. Laravel runs a PostgreSQL migration in one transaction, and a lock is held until its transaction ends, so a VALIDATE in the same migration as its NOT VALID scans under the lock the add took. Measured on PostgreSQL 18.4, read from pg_locks right after the VALIDATE, the session still holds that lock:

The addStill held after the VALIDATE
CHECK, NOT NULLACCESS EXCLUSIVE on the table
FOREIGN KEYSHARE ROW EXCLUSIVE on the table and on the one it references

In a migration of its own the same VALIDATE holds SHARE UPDATE EXCLUSIVE alone. The rule reports the pair on the VALIDATE, and stays silent on a migration that sets public $withinTransaction = false;, where each statement commits before the next one starts.

The second migration can ship in the same deploy. On a large table a later deploy is safer still, because the validation pass is the slow one and the deploy then no longer waits for it.

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.

A VALIDATE beside its NOT VALID is excused on the same grounds: when its table was born empty in this migration, and when every table the add names was created here, because a table created inside an open transaction is invisible to every other session.

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.