🔒 CREATE INDEX Is a Write Outage in Disguise

CREATE INDEX without CONCURRENTLY locks out writes for the entire build.

On a large, live table that plain statement is not a migration. It is a multi-minute write outage for every endpoint touching that table. And the worst part is it sails through every test you have, because your test data is tiny and the build finishes before you can blink.

The lock nobody reads about

A plain CREATE INDEX acquires a SHARE lock on the table and holds it until the build completes.

CREATE INDEX idx_orders_customer_id ON orders (customer_id);

SHARE allows concurrent reads. It blocks anything that writes: INSERT, UPDATE, DELETE. Every write against that table queues up behind the index build and waits. Not for a moment at the start. For the entire duration.

On a small table that duration is milliseconds, so nothing queues and nobody notices. That is exactly why this bug survives code review and staging. The table you tested against had ten thousand rows. The table in production has two hundred million, and building the index on it takes four minutes. For those four minutes, every write to orders is stalled.

The application does not error. It hangs. Requests pile up, connection pools drain, timeouts cascade to services that never touched the database directly. From the outside it looks like a full outage, and the migration that caused it already "succeeded" in your CI.

The fix

CREATE INDEX CONCURRENTLY was built for exactly this.

CREATE INDEX CONCURRENTLY idx_orders_customer_id ON orders (customer_id);

It does not take the blocking SHARE lock. It builds the index in the background, scanning the table while writes keep flowing the whole time. Reads and writes both continue. This is the default you want for any table that is already taking production traffic.

It is not free

CONCURRENTLY trades a fast, clean build for a slower, riskier one. Three costs, all real:

It is slower. To build without blocking writes, Postgres does two full passes over the table instead of one. One pass to build, a second to catch rows that changed during the first. On a huge table that roughly doubles the build time. You are trading wall-clock time for availability, which is almost always the right trade, but it is a trade.

It cannot run inside a transaction block. This is the one that breaks migration tooling. Most migration frameworks wrap each migration in a transaction by default, and CREATE INDEX CONCURRENTLY will error out if it finds itself inside one. You cannot bundle it with other DDL in the same atomic step. It has to run on its own, outside a transaction, which means you lose the "all or nothing" guarantee for that migration.

It can fail into an INVALID index. This is the sharp edge. If a CONCURRENTLY build fails partway, a deadlock, a cancelled session, a statement timeout, Postgres leaves a half-built index behind and marks it INVALID. It does not roll back. It does not clean up. That index is not used by the planner, but it is still updated on every write and still consumes disk. It just sits there, dead weight, until a human notices.

You find them by asking the catalog directly:

SELECT c.relname AS index_name,
       t.relname AS table_name
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
JOIN pg_class t ON t.oid = i.indrelid
WHERE i.indisvalid = false;

Anything that comes back has to be dropped and rebuilt:

DROP INDEX CONCURRENTLY idx_orders_customer_id;
-- then re-run the CREATE INDEX CONCURRENTLY

If you run migrations through automation, a failed CONCURRENTLY will not retry cleanly either, because the invalid index now occupies the name you are trying to create. Your retry fails with "relation already exists" until someone drops the corpse.

The one real exception

"Always use CONCURRENTLY in production" is a good rule with exactly one honest exception: a brand new, empty table you just created in the same migration.

CREATE TABLE audit_log (...);
CREATE INDEX idx_audit_log_created_at ON audit_log (created_at);  -- fine

Nothing is writing to audit_log yet. There are no concurrent writes to block, so the SHARE lock costs nothing, and the plain build is faster and runs cleanly inside the same transaction as the CREATE TABLE. Reaching for CONCURRENTLY here would be cargo-culting the rule past the point where it applies.

The tell is simple: if the table already has live traffic, use CONCURRENTLY. If you are indexing something that does not exist yet, do not bother.

The honest trade-off

CONCURRENTLY is not a free upgrade you flip on and forget. You trade a guaranteed-fast, guaranteed-clean, transaction-safe build for a slower one that runs outside your transaction and can fail into a mess you have to notice and clean up yourself.

For any table with real traffic, that trade is worth it every time. A slower build you have to babysit beats a multi-minute outage you cannot. But "worth it" is not "free," and pretending the invalid-index failure mode does not exist is how teams get surprised at 2am.

---

Has anyone actually gotten burned by an invalid index left behind after a failed CONCURRENTLY build? How did you find it, and do you check pg_index as part of your migration process or only after something breaks?