Does adding a UNIQUE constraint lock the table in PostgreSQL?

Short answer

ALTER TABLE customers ADD CONSTRAINT customers_email_key UNIQUE (email) takes an ACCESS EXCLUSIVE lock and builds a unique index while it holds it. Nothing can read or write the table until the build finishes: 0.45 to 0.67 s for our 3 million rows, and much longer on a big table.

It’s stricter than CREATE UNIQUE INDEX, which only takes a SHARE lock (reads carry on). To avoid blocking anything for longer than a moment:

CREATE UNIQUE INDEX CONCURRENTLY customers_email_key ON customers (email);  -- reads and writes continue
ALTER TABLE customers ADD CONSTRAINT customers_email_key UNIQUE USING INDEX customers_email_key;  -- 2–3 ms

What it locks

USING INDEX also takes a SHARE UPDATE EXCLUSIVE lock on the index, and renames the index to the constraint’s name if they differ.

Does it rewrite the table?

No. The data file is unchanged; PostgreSQL reads the table once to build the index. If two rows share a value, the build fails and the constraint isn’t added:

ERROR:  could not create unique index "big_n_key"
DETAIL:  Key (n)=(593) is duplicated.

NULLs don’t count as duplicates by default, so several rows can have email IS NULL.

Do you need the constraint at all?

A unique index enforces uniqueness exactly the same way, and that’s step 1 of the safe route anyway. INSERT … ON CONFLICT (email) works with only the index. Attach it as a constraint when something needs to see a constraint: a tool that reads pg_constraint, or ON CONFLICT ON CONSTRAINT customers_email_key, which with only an index fails with ERROR: constraint "cu_email_key" for table "cu" does not exist (from our test).

Run it safely

1. Find duplicates first:

SELECT email, count(*) FROM customers
GROUP BY email HAVING count(*) > 1
ORDER BY count(*) DESC LIMIT 20;

2. Build the index concurrently, outside a transaction block, with no short lock_timeout (it applies to the build’s waits and would cancel it):

SET lock_timeout = 0;
CREATE UNIQUE INDEX CONCURRENTLY customers_email_key ON customers (email);

SELECT indisvalid FROM pg_index WHERE indexrelid = 'customers_email_key'::regclass;

If a duplicate exists, or one is inserted during the build, it fails and leaves an INVALID index that USING INDEX refuses (ERROR: index "big_n_key" is not valid). Drop it with DROP INDEX CONCURRENTLY, fix the data and build again. See CREATE INDEX CONCURRENTLY.

3. Attach it with lock_timeout:

SET lock_timeout = '2s';
ALTER TABLE customers ADD CONSTRAINT customers_email_key UNIQUE USING INDEX customers_email_key;

If it fails with ERROR: canceling statement due to lock timeout, nothing changed; retry after a pause (lock timeout). The index must be a plain, valid, non-partial B-tree index:

ERROR:  "t_a_part" is a partial index
DETAIL:  Cannot create a primary key or unique constraint using such an index.

How we checked

On PostgreSQL 14.24, 15.19, 16.14, 17.11 and 18.6 (Docker on a Mac), 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_id_key UNIQUE (id);
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;

For USING INDEX, CREATE UNIQUE INDEX CONCURRENTLY big_id_idx ON seo_mig.big (id) was committed first. The blocking test held each ALTER TABLE open on a 1,000-row table in one session while another ran a SELECT and an INSERT with lock_timeout = '1s'.

ADD … UNIQUE also showed a SHARE lock on the table and ACCESS EXCLUSIVE on the new index; CREATE UNIQUE INDEX big_id_idx on its own took ShareLock (433–541 ms, one run each). The data file number didn’t change in any case. The invalid-index and partial-index errors, two NULLs being accepted by a unique index, and ON CONFLICT (email) working with only an index were identical on all five versions.

In Inlet

The structure editor shows the DDL for a new unique constraint before it runs and warns when a change rewrites or scans the table. If the final ALTER TABLE is waiting for its lock, the Activity monitor shows which session it’s waiting for.