Does adding a PRIMARY KEY or UNIQUE constraint lock the table while the index builds?

PRIMARY KEY or UNIQUE constraint building its index under full lock

Warning: this works, but blocks traffic or rewrites data at scale.

What happens

ADD PRIMARY KEY / ADD UNIQUE builds a whole index while holding ACCESS EXCLUSIVE, build time scales with table size and the table is completely unavailable meanwhile. Building the index CONCURRENTLY first and attaching it with ADD CONSTRAINT ... USING INDEX reduces the exclusive lock to a metadata swap.

Why it is dangerous on a populated table

It completes, but the time it holds its lock or rewrites rows scales with the table, and on a busy one that is long enough for traffic to queue.

Measured

Both forms ran against the same table (20M rows, 1.7 GB) on Postgres 18.6, with one session inserting a row and another reading one row every 50 ms. Worst wait is the slowest of those probe calls while the statement ran. This run is from Sep 16, 2026; the whole benchmark has every scenario and how to run it.

	Statement	Time	Worst write wait	Worst read wait	Lock
Fires	ADD CONSTRAINT ... UNIQUE (index built under lock)	3.8 s	3.8 s	3.8 s	AccessExclusiveLock, ShareLock
Safe	CREATE UNIQUE INDEX CONCURRENTLY, then ADD CONSTRAINT ... USING INDEX	4.8 s	179 ms	1 ms	ShareUpdateExclusiveLock

Fires on

ALTER TABLE orders ADD CONSTRAINT orders_pk PRIMARY KEY (id);

The safe pattern

Build the unique index CONCURRENTLY in its own migration, then attach it with ADD CONSTRAINT ... USING INDEX, the exclusive lock shrinks from a full index build to a metadata swap. For PRIMARY KEY the columns must already be NOT NULL; on Postgres 12+ get there scan-free via a validated CHECK (see BV011).

-- migration 1 (alone, outside a transaction):
--    CREATE UNIQUE INDEX CONCURRENTLY orders_pk_idx ON orders (id);
-- migration 2:
SET lock_timeout = '5s';
ALTER TABLE orders ADD CONSTRAINT orders_pk PRIMARY KEY USING INDEX orders_pk_idx;

Fixtures

The rule ships with these files and the test suite runs them on every change: the first set must fire, the second must stay silent.

Fires (2)

add primary key

ALTER TABLE orders ADD CONSTRAINT orders_pk PRIMARY KEY (id);

add unique

ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE (email);

Stays silent (2)

on new table

CREATE TABLE audit_log (id bigint, entry text);
ALTER TABLE audit_log ADD CONSTRAINT audit_pk PRIMARY KEY (id);

using index

ALTER TABLE orders ADD CONSTRAINT orders_pk PRIMARY KEY USING INDEX orders_id_idx;

How to check locally

Catch this before it ships

This rule runs in the hosted service on Startup and above: add --remote with a team token, or use the GitHub Action. No install, nothing leaves your machine:

npx bolvrk check migration.sql --remote

Browse all 135 rules

← BV011 BV013 →