PostgreSQL — CREATE INDEX Locks the Table: Use CONCURRENTLY

Fix write stalls during index creation on PostgreSQL, understand the ACCESS EXCLUSIVE lock of plain CREATE INDEX, and use CONCURRENTLY with its caveats.

intermediatedebuggingpostgresqlddllocks

Symptoms

CREATE INDEX on a large table freezes all writes to it for the build duration
Application requests queue and time out during migrations that add indexes
Migrations run in a transaction, making CONCURRENTLY fail with its specific error

Possible causes

Plain CREATE INDEX takes SHARE lock blocking all writes for the whole build
CREATE INDEX CONCURRENTLY was not used, or it was wrapped in a transaction block, which PostgreSQL forbids
A failed CONCURRENTLY build left an INVALID index that blocks later attempts with the same name
Long-running queries at migration time extend the lock acquisition phase

Diagnosis steps

Check the lock behavior of the pending statement

psql -c "SELECT locktype, mode FROM pg_locks WHERE relation = 'orders'::regclass;" during a test run

Interpretation

Share mode on the relation confirms writes queue; the acquisition phase also waits behind any open transaction touching the table.

Look for leftover invalid indexes

psql -c "SELECT indexrelid::regclass, indisvalid FROM pg_index WHERE NOT indisvalid;"

Interpretation

An INVALID leftover from a cancelled CONCURRENTLY build must be dropped before a retry with the same name.

Verify the migration runner semantics

grep -Rn "CONCURRENTLY\|transaction" migrations/ | head

Interpretation

CONCURRENTLY cannot run inside the transaction wrapper most migration tools apply by default; the tool must be told to run it outside.

Root cause

A plain CREATE INDEX locks the table against writes for the entire build to guarantee consistency of the new structure. On large tables this converts a maintenance operation into an application outage. CONCURRENTLY avoids the write lock but cannot run inside a transaction block and can leave invalid indexes if interrupted.

Fix

Run CREATE INDEX CONCURRENTLY outside transaction blocks for any table receiving live writes, drop and rebuild INVALID leftovers from failed attempts, and schedule builds to avoid long-running queries at acquisition time. Keep plain CREATE INDEX for maintenance windows you own.

Risk note

CONCURRENTLY is slower and resource-heavier than a plain build, and two concurrent builds on the same table can deadlock each other. Serialize index builds per table.

Verify the fix

During a staging build under write load, writes proceed without stalls; the index ends indisvalid true; and a duplicate-name retry after cleanup succeeds.

Symptom walkthrough

The outage has an exact shape: writes to one table freeze, reads continue, and the error log fills with lock waits on a single relation. The cause is one statement:

CREATE INDEX orders_created_idx ON orders (created_at);

That takes SHARE mode on orders — no INSERT, UPDATE, or DELETE until the build finishes, which on a large table is minutes of write outage. The two failure modes of the fix are equally recognizable:

ERROR: CREATE INDEX CONCURRENTLY cannot run inside a transaction block
ERROR: relation "orders_created_idx" already exists   <- INVALID leftover

Fix in practice

The non-blocking form, run outside any transaction wrapper:

CREATE INDEX CONCURRENTLY orders_created_idx ON orders (created_at);

Tell the migration tool to step out of its default transaction — Flyway needs the configuration, Rails needs disable_ddl_transaction!, SQLAlchemy needs autocommit for the step:

with op.get_context().autocommit_block():
    op.create_index(
        "orders_created_idx", "orders", ["created_at"],
        postgresql_concurrently=True,
    )

Clean up after a failed or cancelled build before retrying:

SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid;
DROP INDEX CONCURRENTLY orders_created_idx;   -- the INVALID one