CREATE INDEX on a live Postgres table locks writes. CONCURRENTLY fixes that, if you keep it out of a transaction

Here is the failure.

You ship a migration that adds an index on a busy table:

CREATE INDEX idx_orders_customer_id ON orders (customer_id);

Postgres builds the index with a SHARE lock. Other sessions can still SELECT. They cannot INSERT, UPDATE, or DELETE until the build finishes. On a large table that can be minutes or hours of blocked writes. The PostgreSQL CREATE INDEX docs say so outright: a standard index build "locks out writes (but not reads) on the table until it's done."

The fix is also in those docs:

CREATE INDEX CONCURRENTLY idx_orders_customer_id ON orders (customer_id);

CONCURRENTLY builds without taking locks that block concurrent inserts, updates, or deletes. It does more work (two table scans, waiting on open transactions), and it has one hard rule that trips most migration tools:

a regular CREATE INDEX command can be performed within a transaction block, but CREATE INDEX CONCURRENTLY cannot.

If your migrator wraps the file in a transaction, you get:

ERROR: CREATE INDEX CONCURRENTLY cannot run inside a transaction block

That exact error shows up when people put CONCURRENTLY into a Flyway (or similar) migration that always runs inside a transaction. See How do I create indexes concurrently with flyway in postgres?: 25k+ views, same Postgres error message.

What to do instead

1. Put the concurrent index in its own migration with nothing else in it.
2. Tell the tool not to wrap that migration in a transaction (Flyway detects CREATE INDEX CONCURRENTLY and can run it non-transactionally; Rails has disable_ddl_transaction!; other tools have equivalent flags).
3. Set a short lock_timeout first so a blocked lock fails fast instead of waiting forever:

SET lock_timeout = '5s';
CREATE INDEX CONCURRENTLY idx_orders_customer_id ON orders (customer_id);

1. After it finishes, check the index is valid. A failed concurrent build can leave an INVALID index that still costs write overhead:

SELECT c.relname, i.indisvalid
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
WHERE c.relname = 'idx_orders_customer_id';

If indisvalid is false, drop it and retry CREATE INDEX CONCURRENTLY.

Why agents make this worse

An agent writing a "quick" migration often emits plain CREATE INDEX because that form works inside a transaction and looks like every other DDL statement. Or it puts CREATE INDEX CONCURRENTLY next to an ALTER TABLE in the same file, which many tools still wrap as one transaction. So Postgres rejects the concurrent build and the agent "fixes" it by dropping CONCURRENTLY.

If you use Cursor (or another agent) on migration SQL, give it a standing rule: Postgres indexes on live tables use CREATE INDEX CONCURRENTLY alone in their own migration, with lock_timeout set first. That is exactly what the lock-safe section of Schema Migration Guard tells the agent before it writes the file.

A free, copy-paste Cursor hook that blocks remote prisma migrate reset and drizzle-kit push --force (a small subset of the same pack) is here: https://site-export-rescue.pages.dev/cursor-prisma-drizzle-guard/?utm_source=devto&utm_medium=article&utm_campaign=postgres-create-index-concurrently-outside-transaction. It does not rewrite your SQL for you; the rules side is what steers the index pattern.

Drafted with AI assistance from the PostgreSQL CREATE INDEX documentation, Stack Overflow question 20350501, and Schema Migration Guard's lock-safe-migrations rule, and checked against them.