NOT VALID + VALIDATE: Add Constraints Without Long Locks

Adding a foreign key or a CHECK to an empty table takes a second. On a table with tens of millions of rows, the same ALTER TABLE scans every row under a lock and can freeze writes for minutes. PostgreSQL splits that operation into two steps: an instant NOT VALID and a background VALIDATE CONSTRAINT.

Why a plain ALTER is dangerous

When you run ALTER TABLE orders ADD CONSTRAINT ..., PostgreSQL must prove that ALL existing rows satisfy the constraint. To do that it grabs an ACCESS EXCLUSIVE (or near-equivalent) lock and reads the whole table.

• The bigger the table, the longer the scan.
• While the scan runs, INSERT, UPDATE, DELETE on that table queue up behind the lock.
• For a foreign key, the parent table gets locked too.

On a production orders table that means real write downtime. The idea behind NOT VALID is to separate "start enforcing the rule on new rows" from "verify the old rows."

Step 1: ADD CONSTRAINT ... NOT VALID

The NOT VALID flag says: record the constraint in the catalog and enforce it on every new or modified row, but do not scan the existing data.

-- Foreign key: instant, no full-table scan
ALTER TABLE orders
  ADD CONSTRAINT fk_orders_user
  FOREIGN KEY (user_id) REFERENCES users(id)
  NOT VALID;

-- CHECK works the same way
ALTER TABLE orders
  ADD CONSTRAINT chk_amount_positive
  CHECK (amount > 0)
  NOT VALID;

The statement takes a short metadata-only lock and returns immediately. From now on, a new order with a missing user_id or with amount <= 0 is rejected. The old "dirty" rows stay for now, and the constraint is flagged as not validated.

Step 2: VALIDATE CONSTRAINT

The second step finishes scanning the existing rows, but without the aggressive lock.

ALTER TABLE orders VALIDATE CONSTRAINT fk_orders_user;
ALTER TABLE orders VALIDATE CONSTRAINT chk_amount_positive;

VALIDATE CONSTRAINT only takes a SHARE UPDATE EXCLUSIVE lock: reads and concurrent writes keep flowing, and only other schema operations are blocked. If even one row breaks the rule, the command fails and the constraint stays in the NOT VALID state, so you can clean the data and retry.

It pays to find the bad rows before you validate:

-- Orphan orders that would fail the FK
SELECT o.id, o.user_id
FROM orders o
LEFT JOIN users u ON u.id = o.user_id
WHERE u.id IS NULL;

Where it applies, and one gotcha

The pattern works for FOREIGN KEY and CHECK constraints (including CHECK (col IS NOT NULL) as a way to add NOT NULL without a long scan). For UNIQUE and PRIMARY KEY there is no NOT VALID syntax; use CREATE INDEX CONCURRENTLY plus ADD CONSTRAINT ... USING INDEX instead.

• Gotcha: while a constraint is NOT VALID, the planner will not rely on it for optimizations (for example, constraint exclusion on partitions). Always follow through with VALIDATE.
• Run the two steps in separate transactions. If you wrap both in one transaction, you are back to a long scan under a heavy lock.
• Check the status with \d+ orders, which shows NOT VALID next to unvalidated constraints, or query pg_constraint.convalidated.

SELECT conname, convalidated
FROM pg_constraint
WHERE conrelid = 'orders'::regclass;

What about MySQL and ClickHouse

The story differs elsewhere. In MySQL/InnoDB, foreign keys are validated at creation time and there is no separate NOT VALID; CHECK arrived in 8.0.16 and is also validated immediately. For CHECK there is a workaround: create it as NOT ENFORCED, then switch to ENFORCED, but that does not make the scan a background job. Modern InnoDB runs many ALTER operations online (ALGORITHM=INPLACE, LOCK=NONE), yet adding an FK still needs care on large tables.

In ClickHouse there are no foreign keys at all; CHECK constraints are declared in CREATE TABLE and verified on insert, not retroactively across the whole table. So this two-step trick is a PostgreSQL specialty, and that is exactly why it is so valuable for zero-downtime migrations.