ADD CONSTRAINT lock modes are not one-size-fits-all

The common shortcut is easy to remember:

ALTER TABLE ... ADD CONSTRAINT takes a strong lock. Use NOT VALID, then VALIDATE CONSTRAINT.

That advice is still right. The shortcut is just too broad.

PostgreSQL does not assign one lock mode to the whole ADD CONSTRAINT family. Foreign keys are special. VALIDATE CONSTRAINT is special. USING INDEX is brief, but still not the same as a concurrent index build. If a safety tool collapses all of those into one row, it either overstates some migrations or understates others.

pgfence v0.6 fixed this family against PostgreSQL’s AlterTableGetLockLevel() implementation and added tests so the lock table cannot drift quietly again.

The corrected map

Here is the production-relevant version:

Variant	Lock mode	Blocks reads?	Blocks writes?
ADD CONSTRAINT ... FOREIGN KEY	SHARE ROW EXCLUSIVE	No	Yes
ADD CONSTRAINT ... CHECK without NOT VALID	ACCESS EXCLUSIVE	Yes	Yes
ADD CONSTRAINT ... UNIQUE without USING INDEX	ACCESS EXCLUSIVE	Yes	Yes
ADD CONSTRAINT ... PRIMARY KEY without USING INDEX	ACCESS EXCLUSIVE	Yes	Yes
ADD CONSTRAINT ... EXCLUDE	ACCESS EXCLUSIVE	Yes	Yes
ADD CONSTRAINT ... UNIQUE USING INDEX	ACCESS EXCLUSIVE	Yes, briefly	Yes, briefly
ADD CONSTRAINT ... PRIMARY KEY USING INDEX	ACCESS EXCLUSIVE	Yes, briefly	Yes, briefly
VALIDATE CONSTRAINT	SHARE UPDATE EXCLUSIVE	No	No

The main exception is the foreign key path. It takes SHARE ROW EXCLUSIVE, and it takes it on both sides of the relationship because PostgreSQL installs trigger machinery on the referencing and referenced tables. Reads continue. Writes block.

Most other constraint adds take ACCESS EXCLUSIVE on the target table. That is the one that blocks reads and writes.

Why NOT VALID still matters

NOT VALID is not magic because it removes every strong lock. It is useful because it makes the strong lock brief.

For a CHECK constraint, the dangerous version is:

ALTER TABLE orders
  ADD CONSTRAINT orders_total_positive CHECK (total_cents >= 0);

PostgreSQL must both add the constraint and verify every existing row. That validation scan happens while the table is locked.

The safer shape is:

ALTER TABLE orders
  ADD CONSTRAINT orders_total_positive CHECK (total_cents >= 0) NOT VALID;

ALTER TABLE orders
  VALIDATE CONSTRAINT orders_total_positive;

The first statement is still a schema change, so it needs a brief table lock. The second statement does the long scan under SHARE UPDATE EXCLUSIVE, which allows normal reads and writes.

For a foreign key, the same pattern matters even more because the first step touches two tables:

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

ALTER TABLE orders
  VALIDATE CONSTRAINT orders_user_id_fkey;

That keeps the two-table write-blocking window short.

UNIQUE and PRIMARY KEY need a different recipe

NOT VALID is not available for UNIQUE or PRIMARY KEY constraints. The safe path is:

CREATE UNIQUE INDEX CONCURRENTLY users_email_key_idx
  ON users(email);

ALTER TABLE users
  ADD CONSTRAINT users_email_key
  UNIQUE USING INDEX users_email_key_idx;

The index build is the long part, and CONCURRENTLY keeps reads and writes flowing. The final USING INDEX statement is a brief metadata change. It still takes ACCESS EXCLUSIVE, so you should set lock_timeout, but it should not scan the whole table.

What pgfence v0.6 changed

Before v0.6, pgfence treated this family too loosely in a few places. That was bad in both directions: some messages made ADD CONSTRAINT sound safer than it is, and some policy checks could miss an ACCESS EXCLUSIVE window.

v0.6 now reports:

• foreign keys as SHARE ROW EXCLUSIVE, including the referenced table warning
• CHECK, UNIQUE, PRIMARY KEY, EXCLUDE, and USING INDEX constraint adds as ACCESS EXCLUSIVE
• VALIDATE CONSTRAINT as SHARE UPDATE EXCLUSIVE
• safe rewrites that distinguish NOT VALID validation from concurrent unique-index construction

This is the kind of detail pgfence is built for. The useful answer is not just “dangerous” or “safe.” It is the exact lock, what it blocks, and which rewrite actually shrinks the production blast radius.

npm install -D @flvmnt/pgfence@0.6
npx @flvmnt/pgfence analyze migrations/*.sql

GitHub | Docs | npm