Does adding a primary key lock the table in PostgreSQL?

Short answer

ALTER TABLE orders ADD PRIMARY KEY (id) takes an ACCESS EXCLUSIVE lock, so nothing can read or write orders until it finishes. While it holds the lock it does two slow things: it reads every row to make sure id has no NULLs (if the column isn’t already NOT NULL), and it builds a unique index. On our 3-million-row table that took about 0.6 s; on a large table, it’s minutes of downtime.

Do the slow parts first, without blocking anyone, then attach the key:

CREATE UNIQUE INDEX CONCURRENTLY orders_id_key ON orders (id);           -- reads and writes continue
ALTER TABLE orders ADD CONSTRAINT orders_id_not_null CHECK (id IS NOT NULL) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_id_not_null;               -- reads and writes continue
ALTER TABLE orders ADD CONSTRAINT orders_pkey PRIMARY KEY USING INDEX orders_id_key;  -- 2–3 ms
ALTER TABLE orders DROP CONSTRAINT orders_id_not_null;

What it locks

USING INDEX also takes a SHARE UPDATE EXCLUSIVE lock on the index, and renames it to the constraint’s name:

NOTICE:  ALTER TABLE / ADD CONSTRAINT USING INDEX will rename index "big_id_idx" to "big_pkey"

The key point: USING INDEX on its own isn’t enough. If the column allows NULL, PostgreSQL makes it NOT NULL as part of adding the key, and that means a full scan under ACCESS EXCLUSIVE. A validated CHECK (id IS NOT NULL) proves there are no NULLs, so the scan is skipped.

Does it rewrite the table?

No. The table’s data file doesn’t change. The cost is the scan to check for NULLs and the index build, which reads the whole table and sorts it.

Run it safely

1. Make sure the values are unique and not NULL:

SELECT id, count(*) FROM orders GROUP BY id HAVING count(*) > 1 LIMIT 20;
SELECT count(*) FROM orders WHERE id IS NULL;

2. Build the unique index concurrently, outside any transaction, and check it’s valid:

CREATE UNIQUE INDEX CONCURRENTLY orders_id_key ON orders (id);

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

If there are duplicates it fails and leaves an invalid index; drop it with DROP INDEX CONCURRENTLY orders_id_key, fix the data, and try again. See CREATE INDEX CONCURRENTLY. USING INDEX refuses an invalid or partial index:

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

3. Prove NOT NULL without a long lock (skip if the column is already NOT NULL):

SET lock_timeout = '2s';
ALTER TABLE orders ADD CONSTRAINT orders_id_not_null CHECK (id IS NOT NULL) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_id_not_null;

On PostgreSQL 18 you can use ADD CONSTRAINT orders_id_not_null NOT NULL id NOT VALID and validate that instead; the primary key step then skips the scan too (6 ms in our test) and there’s nothing to drop afterwards. See SET NOT NULL.

4. Attach the key in a short transaction with lock_timeout:

BEGIN;
SET LOCAL lock_timeout = '2s';
ALTER TABLE orders ADD CONSTRAINT orders_pkey PRIMARY KEY USING INDEX orders_id_key;
ALTER TABLE orders DROP CONSTRAINT orders_id_not_null;
COMMIT;

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

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 with a nullable id and no index:

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 PRIMARY KEY (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 the USING INDEX cases, CREATE UNIQUE INDEX CONCURRENTLY big_id_idx ON seo_mig.big (id) was committed first (and, for the third column, a validated CHECK (id IS NOT NULL)), then ALTER TABLE seo_mig.big ADD CONSTRAINT big_pkey PRIMARY KEY USING INDEX big_id_idx ran in a rolled-back transaction.

With a second session holding ALTER TABLE seo_mig.nopk ADD PRIMARY KEY (id) open on a 1,000-row table, a SELECT and an INSERT from a third session (lock_timeout = '1s') both waited, on all five versions. On 18.6, after ADD CONSTRAINT big_id_not_null NOT NULL id NOT VALID and VALIDATE CONSTRAINT, the USING INDEX step made 0 scans in 6 ms.

In Inlet

The structure editor shows the DDL for a new primary key before it runs and warns when a change rewrites or scans the table. While the slow steps run, the Activity monitor shows which sessions are waiting on them.