Does adding a CHECK constraint lock the table in PostgreSQL?

Short answer

ALTER TABLE orders ADD CONSTRAINT orders_total_check CHECK (total >= 0) takes an ACCESS EXCLUSIVE lock and then checks every existing row. Nothing is rewritten, but nobody can read or write the table until the scan finishes: 0.1 s for our 3 million rows, and proportionally longer on a big table.

Do it in two steps instead:

ALTER TABLE orders ADD CONSTRAINT orders_total_check CHECK (total >= 0) NOT VALID;  -- instant
ALTER TABLE orders VALIDATE CONSTRAINT orders_total_check;  -- scans; reads and writes carry on

What it locks

SHARE UPDATE EXCLUSIVE conflicts with other schema changes, VACUUM, ANALYZE and CREATE INDEX CONCURRENTLY on the same table, not with SELECT, INSERT, UPDATE or DELETE. Autovacuum won’t run on the table while VALIDATE holds it.

Does it rewrite the table?

No. The data file doesn’t change. The constraint reads each row and evaluates the expression.

If a row fails, the whole statement fails:

ERROR:  check constraint "t_a_check" of relation "t" is violated by some row

NOT VALID only skips checking the rows already there. From the moment it’s added, new rows are checked, and so is every updated row, even if the update doesn’t touch the checked column. With CHECK (a >= 0) NOT VALID on a table where row 1 had a = -1:

UPDATE seo_mig.t SET s = 'touched' WHERE id = 1;
ERROR:  new row for relation "t" violates check constraint "t_a_check"
DETAIL:  Failing row contains (1, -1, touched, 2026-10-09 10:06:20.079777, b).

So fix the bad rows before you add the constraint, not after, or your application will start getting errors on unrelated updates.

Run it safely

1. Find and fix rows that would fail, in batches if there are many:

SELECT id, total FROM orders WHERE NOT (total >= 0) LIMIT 100;

UPDATE orders SET total = 0
WHERE id BETWEEN 1 AND 10000 AND NOT (total >= 0);

NOT (total >= 0) skips rows where total is NULL, which is right: a CHECK passes when its expression is NULL.

2. Add the constraint NOT VALID, with lock_timeout. It’s instant, but it still needs ACCESS EXCLUSIVE, so it can queue behind a long query and hold everyone else up:

SET lock_timeout = '2s';
ALTER TABLE orders ADD CONSTRAINT orders_total_check CHECK (total >= 0) NOT VALID;

If it fails with ERROR: canceling statement due to lock timeout, nothing changed: retry after a pause. See lock timeout.

3. Validate it. This is the slow part, and it doesn’t block reads or writes:

ALTER TABLE orders VALIDATE CONSTRAINT orders_total_check;

If it fails, the constraint stays in place but NOT VALID; fix the rows it complains about and run it again. If you have a statement_timeout, raise it for this session so the scan can finish (see statement timeout).

A validated CHECK is also what lets SET NOT NULL and ATTACH PARTITION skip their own scans.

How we checked

On PostgreSQL 14.24, 15.19, 16.14, 17.11 and 18.6 (Docker on a Mac), each statement in a rolled-back transaction on a 3-million-row table:

CREATE TABLE seo_mig.big (id bigint, n int, s text);
INSERT INTO seo_mig.big SELECT g, g % 1000, md5(g::text) FROM generate_series(1, 3000000) g;

BEGIN;
ALTER TABLE seo_mig.big ADD CONSTRAINT big_n_check CHECK (n >= 0);
SELECT relation::regclass, mode FROM pg_locks
WHERE pid = pg_backend_pid()
  AND relation IN (SELECT oid FROM pg_class WHERE relnamespace = 'seo_mig'::regnamespace);
SELECT seq_scan FROM pg_stat_xact_user_tables WHERE relid = 'seo_mig.big'::regclass;
ROLLBACK;

VALIDATE was measured against a committed NOT VALID constraint. Times are the median of three runs; the data file number didn’t change in any of them.

With a second session holding each statement open on a 1,000-row table, a third session with lock_timeout = '1s' found, on all five versions:

The update-of-an-old-row error above is from 18.6; 14.24 printed the same.

In Inlet

The structure editor shows the ADD CONSTRAINT statement before it runs and warns when a change rewrites or scans the table. While a constraint is being added, the Activity monitor shows the sessions waiting on it and lets you cancel it.