Constraints
ADD CONSTRAINT ... PRIMARY KEY without USING INDEX
Lock mode ACCESS EXCLUSIVE
Risk HIGH
Blocks reads, writes, other DDL
pgfence rule
add-pk-without-using-index ALTER TABLE orders ADD CONSTRAINT orders_pkey PRIMARY KEY (id);What this does
Like UNIQUE, PRIMARY KEY has no NOT VALID option, so PostgreSQL builds the backing index and validates it inline under ACCESS EXCLUSIVE for the whole table.
Safe rewrite
Build a unique index concurrently first, then attach the primary key to it
CREATE UNIQUE INDEX CONCURRENTLY orders_pkey_idx ON orders (id);
ALTER TABLE orders ADD CONSTRAINT orders_pkey PRIMARY KEY USING INDEX orders_pkey_idx;