Does CREATE INDEX block writes in Postgres?

Non-concurrent index creation

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

What happens

Measured on Postgres 16, one table of twenty million rows (1.6 GB), with a second session inserting a row every fifty milliseconds: a plain CREATE INDEX took 4.4 s and held a SHARE lock for all of it, and the slowest insert waited 4216 ms, almost exactly the build time. CREATE INDEX CONCURRENTLY took 7.3 s and the slowest insert waited 123 ms.

Why it is dangerous on a populated table

Reads go through, every write queues behind the SHARE lock for the whole build, and the wait scales with the table: four seconds on this table, minutes on a bigger one, and every checkout, signup and webhook handler hangs for that long.

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	CREATE INDEX	6.4 s	6.3 s	15 ms	ShareLock
Safe	CREATE INDEX CONCURRENTLY	7.4 s	15 ms	8 ms	ShareUpdateExclusiveLock

Fires on

CREATE INDEX idx_orders_region ON orders (region);

The safe pattern

Build the index with CREATE INDEX CONCURRENTLY in its own migration, outside any transaction. It takes only SHARE UPDATE EXCLUSIVE, so writes continue during the build. It can fail and leave an INVALID index, drop it and retry rather than ignoring it.

-- In its own migration file, run outside a transaction:
CREATE INDEX CONCURRENTLY idx_orders_region ON orders (region);

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 (1)

plain create index

CREATE INDEX idx_orders_region ON orders (region);

Stays silent (2)

concurrent index

CREATE INDEX CONCURRENTLY idx_orders_region ON orders (region);

index on new table

CREATE TABLE audit_log (id bigint PRIMARY KEY, entry text);
CREATE INDEX idx_audit_entry ON audit_log (entry);

How to check locally

Catch this before it ships

This rule runs locally in the free CLI, or with the full corpus through the hosted service. No install, nothing leaves your machine:

npx bolvrk check migration.sql

Browse all 135 rules

← BV002 BV004 →