PostgreSQL VALIDATE CONSTRAINT: What Lock Mode It Actually Takes

ALTER TABLE ... VALIDATE CONSTRAINT takes a SHARE UPDATE EXCLUSIVE lock. That is the same weak, DDL-only lock that VACUUM and CREATE INDEX CONCURRENTLY take. It does not block SELECT, and it does not block INSERT, UPDATE, or DELETE. It blocks other schema changes and maintenance operations on the same table for as long as the validation scan runs.

That is the whole answer. If you came here to confirm you can run VALIDATE CONSTRAINT against a live production table without stopping traffic, you can, and the rest of this page is the source-verified detail behind that answer, including one very common way people accidentally undo it.

Why this question comes up

VALIDATE CONSTRAINT only exists because of a two-step pattern for adding constraints to large tables without a long outage:

-- Step 1: add the constraint, skip the scan
ALTER TABLE orders
  ADD CONSTRAINT orders_user_id_fkey
  FOREIGN KEY (user_id) REFERENCES users(id)
  NOT VALID;

-- Step 2: scan existing rows, in a separate migration
ALTER TABLE orders
  VALIDATE CONSTRAINT orders_user_id_fkey;

Most write-ups explain step 1 in detail (it is brief, it is metadata-only, it is safe) and then wave at step 2 with “then validate it later.” That leaves an obvious question unanswered: the whole reason for this dance was to avoid a long lock during the table scan, so what lock does the table scan itself run under? If it is another ACCESS EXCLUSIVE, the two-step pattern has not actually solved anything, it has just moved the outage to a second migration.

It has not moved the outage. VALIDATE CONSTRAINT runs the scan under a lock that is deliberately much weaker than the one the initial ADD CONSTRAINT takes, and the PostgreSQL documentation explains exactly why that is safe to do.

Quick reference: lock mode by step

Statement	Lock mode	Blocks reads	Blocks writes	Blocks other DDL
ADD CONSTRAINT ... CHECK ... NOT VALID	ACCESS EXCLUSIVE (brief, metadata only)	Yes, briefly	Yes, briefly	Yes, briefly
ADD CONSTRAINT ... FOREIGN KEY ... NOT VALID	SHARE ROW EXCLUSIVE, on both tables (brief, metadata only)	No	Yes, briefly	Yes, briefly
VALIDATE CONSTRAINT	SHARE UPDATE EXCLUSIVE	No	No	Yes, for the duration of the scan

The first row is what most ALTER TABLE subcommands do by default. Per the PostgreSQL documentation for ALTER TABLE: “An ACCESS EXCLUSIVE lock is acquired unless explicitly noted.” Foreign keys are the one exception called out on that page: “Although most forms of ADD table_constraint require an ACCESS EXCLUSIVE lock, ADD FOREIGN KEY requires only a SHARE ROW EXCLUSIVE lock.” NOT VALID does not change which lock mode the ADD CONSTRAINT statement itself takes. It only skips the table scan that would otherwise run while that lock is held, which is what keeps step 1 brief.

NOT VALID is not available for every constraint type. Per the same documentation, it “is currently only allowed for foreign-key, CHECK, and not-null constraints.” UNIQUE, PRIMARY KEY, and EXCLUDE constraints cannot be added as NOT VALID at all, so an ADD CONSTRAINT for one of those still holds ACCESS EXCLUSIVE for as long as its own index build takes, not just briefly. That is exactly why those three use a different safe recipe, covered later on this page.

VALIDATE CONSTRAINT is a separate statement with its own, weaker lock, which is the third row and the one people searching for this usually mean.

What SHARE UPDATE EXCLUSIVE actually conflicts with

SHARE UPDATE EXCLUSIVE sits in the middle of PostgreSQL’s eight table-level lock modes. Per the explicit locking documentation, it “conflicts with the SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE lock modes.” It does not appear in the conflict list for ACCESS SHARE, ROW SHARE, or ROW EXCLUSIVE, which are exactly the locks that ordinary queries take.

Translated into statements:

Keeps running normally while VALIDATE CONSTRAINT is scanning:

• SELECT (ACCESS SHARE)
• SELECT ... FOR UPDATE (ROW SHARE)
• INSERT, UPDATE, DELETE (ROW EXCLUSIVE)

Waits behind VALIDATE CONSTRAINT (and vice versa, since the conflict is symmetric):

• VACUUM (non-full), ANALYZE, CREATE INDEX CONCURRENTLY, REINDEX CONCURRENTLY, or another VALIDATE CONSTRAINT on the same table, because all of these also take SHARE UPDATE EXCLUSIVE, and SHARE UPDATE EXCLUSIVE conflicts with itself
• CREATE INDEX without CONCURRENTLY (SHARE)
• CREATE TRIGGER or ADD CONSTRAINT ... FOREIGN KEY (SHARE ROW EXCLUSIVE)
• REFRESH MATERIALIZED VIEW CONCURRENTLY on that relation (EXCLUSIVE)
• Most other ALTER TABLE subcommands, DROP TABLE, TRUNCATE (ACCESS EXCLUSIVE)

In practice, this means a long-running VALIDATE CONSTRAINT will delay your next migration if that migration touches the same table, and it will delay a concurrently running VACUUM or index build on that table. It will not delay application traffic.

Foreign keys touch a second table too

If the constraint being validated is a foreign key, PostgreSQL also needs to protect the referenced table while it scans. The documentation is explicit about this: validation “acquires only a SHARE UPDATE EXCLUSIVE lock on the table being altered. (If the constraint is a foreign key then a ROW SHARE lock is also required on the table referenced by the constraint.)”

ROW SHARE is the lock SELECT ... FOR UPDATE takes. It is one of the weakest locks in Postgres and only conflicts with EXCLUSIVE and ACCESS EXCLUSIVE. So validating orders.user_id_fkey against users takes SHARE UPDATE EXCLUSIVE on orders and ROW SHARE on users, and normal traffic on both tables continues.

Worked example: validating a foreign key on a large table

Say orders has 40 million rows and a user_id column that has never had a foreign key. You add one using the standard two-step recipe, in two separate migrations:

-- Migration 1, runs in milliseconds
SET lock_timeout = '2s';

ALTER TABLE orders
  ADD CONSTRAINT orders_user_id_fkey
  FOREIGN KEY (user_id) REFERENCES users(id)
  NOT VALID;

At this point the constraint exists and is already enforced for every new INSERT or UPDATE on orders. pg_constraint.convalidated is false for it: Postgres has not yet checked the 40 million pre-existing rows.

-- Migration 2, runs later, can take minutes
SET lock_timeout = '2s';

ALTER TABLE orders VALIDATE CONSTRAINT orders_user_id_fkey;

While that second statement is scanning, open another session and check what it actually holds:

SELECT locktype, relation::regclass, mode, granted
FROM pg_locks
WHERE pid = (
  SELECT pid FROM pg_stat_activity
  WHERE query ILIKE '%VALIDATE CONSTRAINT%' LIMIT 1
);

You will see ShareUpdateExclusiveLock on orders and RowShareLock on users, both granted = t. Meanwhile, in a third session, ordinary traffic is unaffected:

SELECT * FROM orders WHERE id = 12345;              -- returns instantly
INSERT INTO orders (user_id, total_cents) VALUES (7, 1999); -- succeeds instantly
UPDATE orders SET status = 'shipped' WHERE id = 12345;      -- succeeds instantly

If the scan finds a row that violates the constraint, VALIDATE CONSTRAINT fails with an error naming the constraint, the table stays exactly as it was (still NOT VALID), and you fix the offending rows before trying again. Nothing about a failed validation escalates the lock.

The mistake that makes VALIDATE CONSTRAINT look like it re-locks the table

This is the part that causes the most confusion, and it is not a Postgres quirk, it is how transactions work. PostgreSQL states it plainly in the locking documentation: “Once acquired, a lock is normally held until the end of the transaction.”

That single sentence is the whole story. If you run both statements from the worked example above back to back in one transaction instead of two separate migrations, watch what happens to the lock, not just to the statement that requested it:

BEGIN;

ALTER TABLE orders
  ADD CONSTRAINT orders_user_id_fkey
  FOREIGN KEY (user_id) REFERENCES users(id)
  NOT VALID;
-- SHARE ROW EXCLUSIVE acquired on orders and users right here

ALTER TABLE orders VALIDATE CONSTRAINT orders_user_id_fkey;
-- SHARE UPDATE EXCLUSIVE also acquired, but this does NOT replace
-- or downgrade the SHARE ROW EXCLUSIVE from the line above.
-- Both locks are held for the rest of the transaction.

COMMIT;

For the entire duration of that transaction, including the whole table scan that VALIDATE CONSTRAINT runs, the strongest lock Postgres is enforcing on orders is still SHARE ROW EXCLUSIVE from the first statement, because that lock is not released until COMMIT. SHARE ROW EXCLUSIVE conflicts with ROW EXCLUSIVE, so every INSERT, UPDATE, and DELETE against orders queues up and waits until the scan finishes and the transaction commits. Reads still go through, since SHARE ROW EXCLUSIVE does not conflict with ACCESS SHARE, but the write-blocking outage you were trying to avoid happens anyway.

For a CHECK constraint the same mistake is worse, because the first statement’s lock is ACCESS EXCLUSIVE, not SHARE ROW EXCLUSIVE. Combine ADD CONSTRAINT ... CHECK (...) NOT VALID and VALIDATE CONSTRAINT in one transaction, and reads are blocked too, for as long as the validation scan takes. That is functionally identical to skipping NOT VALID altogether and just running ADD CONSTRAINT ... CHECK (...) in one statement. The two-step pattern only buys you anything when the two steps are two separate transactions, committed independently, which in practice means two separate migrations.

This is exactly the failure mode pgfence checks for: it flags NOT VALID followed by VALIDATE CONSTRAINT in the same transaction, because the pattern is syntactically correct and semantically pointless.

The recipe, done correctly

The NOT VALID plus VALIDATE CONSTRAINT split only helps if the two statements are in separate transactions:

-- Migration 1: add the constraint, skip the scan
SET lock_timeout = '2s';
ALTER TABLE orders
  ADD CONSTRAINT orders_user_id_fkey
  FOREIGN KEY (user_id) REFERENCES users(id)
  NOT VALID;

-- Migration 2: separate deploy, separate transaction
SET lock_timeout = '2s';
ALTER TABLE orders VALIDATE CONSTRAINT orders_user_id_fkey;

The same shape applies to CHECK constraints, and as of PostgreSQL 18, to named NOT NULL constraints as well, which can now be added with NOT VALID and validated separately using the same two lock modes described above. UNIQUE and PRIMARY KEY constraints do not support NOT VALID at all; those need the CREATE UNIQUE INDEX CONCURRENTLY plus ADD CONSTRAINT ... USING INDEX recipe covered in ADD CONSTRAINT lock modes are not one-size-fits-all.

pgfence checks for both halves of this automatically: that a constraint add on an existing table uses NOT VALID, and that the matching VALIDATE CONSTRAINT is not sitting in the same transaction as it.

npx @flvmnt/pgfence analyze migrations/*.sql

For the full lock mode matrix behind this and every other DDL statement pgfence checks, see the Postgres lock mode cheat sheet.

GitHub | Docs | npm