PRIMARY KEY or UNIQUE constraint building its index under full lock

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

What it catches

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.

Fires on

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

Do this instead

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;

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: npx bolvrk check migration.sql

Browse all 69 rules

← BV011 BV013 →