PostgreSQL lets you add a constraint with the NOT VALID clause and run VALIDATE CONSTRAINT later. Walk through the two steps — what each one locks and checks, and exactly what guarantee you have in the window between them.

## The problem it solves A plain `ADD CONSTRAINT` bundles two things: recording the rule in the catalog, and proving every existing row obeys it. The first is instantaneous, the second is a full scan — and because they happen in one statement, the strong lock needed for the catalog change is held for the entire scan. `NOT VALID` unbundles them. ## Step 1: adding the constraint unvalidated ```sql ALTER TABLE orders ADD CONSTRAINT orders_customer_fk FOREIGN KEY (customer_id) REFERENCES customers (id) NOT VALID; ``` This records the constraint and attaches its enforcement machinery, then returns. No rows are read. It still needs `ACCESS EXCLUSIVE` on `orders` and a lock on `customers`, but only for the moment the catalog is written, so with a short `lock_timeout` and a retry loop it is safe to run at any time of day. From the instant it commits: - Every `INSERT` is checked. - Every `UPDATE` that touches the constrained columns is checked. (Subtlety worth knowing: an update to an unrelated column of a pre-existing violating row is *not* forced to fix it for a CHECK constraint — the check applies to rows being inserted or updated in the constrained columns.) - Existing untouched rows are not examined and may violate the rule. The constraint is marked `convalidated = false` in `pg_constraint`. ## Step 2: validating ```sql ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_fk; ``` This is the scan. It reads the table, verifies each row, and on success flips the constraint to validated. Crucially it takes only `SHARE UPDATE EXCLUSIVE` on the table — the same class of lock as `VACUUM` and `CREATE INDEX CONCURRENTLY` — which does **not** conflict with `SELECT`, `INSERT`, `UPDATE` or `DELETE`. For a foreign key it takes `ROW SHARE` on the referenced table. So the long part runs while the application keeps working. What it does conflict with is other `SHARE UPDATE EXCLUSIVE` holders — another validation, a concurrent index build, an autovacuum on that table — so schedule accordingly. If validation finds a violating row it raises an error and the constraint simply stays `NOT VALID`. Nothing is lost; you clean the data and try again. That is why the practical order is: add NOT VALID first (to stop new bad data arriving), *then* fix the historical rows, *then* validate. Fixing first and adding later leaves a race in which new violations appear between the cleanup and the constraint. ## What you actually give up in the window The temptation is to think an unvalidated constraint is decorative. It is not — it is enforced for all new writes. Two real limitations: 1. **The planner will not trust it.** PostgreSQL will not use an unvalidated CHECK constraint for constraint exclusion or partition pruning, because the guarantee does not hold over existing rows. If you added the CHECK specifically to enable pruning, you get nothing until validation completes. 2. **Your invariant is not actually true yet.** Application code that assumes "every order has a customer" can still meet an orphan from before the migration. Until validation succeeds, treat the invariant as aspirational in code paths that read old data. Some operations also refuse to proceed against unvalidated constraints — attaching a partition, for instance, benefits from a validated CHECK to skip its own scan. ## The end-to-end staged rollout 1. **Stop the source.** Fix the application code producing violations, and deploy it. 2. **Add `NOT VALID`.** Short lock, immediate enforcement of new writes. 3. **Repair history in batches.** Fix or delete violating rows in small transactions, so you never hold long locks. Query for them with an anti-join. 4. **Validate.** Off-peak, under the weak lock. Expect it to take as long as a full scan of the table. 5. **Verify.** Confirm `convalidated` is true, and re-check anything that depends on planner behaviour. Each step is independently revertible, and the risky step (the brief strong lock) is decoupled from the slow step (the scan). ## Where it does not apply `NOT VALID` is supported for CHECK and foreign-key constraints. It is not available for `UNIQUE` or `PRIMARY KEY`, because there the constraint *is* an index and there is no way to have the index without building it. The equivalent staged approach for uniqueness is `CREATE UNIQUE INDEX CONCURRENTLY`, which builds without blocking writes, followed by `ALTER TABLE ... ADD CONSTRAINT ... UNIQUE USING INDEX`, which adopts the finished index with only a brief lock and no rescan. For `NOT NULL`, the modern route is to add `CHECK (col IS NOT NULL) NOT VALID`, validate it, then `SET NOT NULL` — from PostgreSQL 12 onward the planner uses the validated CHECK to skip the scan that `SET NOT NULL` would otherwise perform.