April 18, 2026 · Flavius Munteanu

CREATE INDEX CONCURRENTLY in a transaction is a silent footgun

CREATE INDEX CONCURRENTLY is the right fix for blocking index builds, but it fails inside a transaction block. Here is why that happens and how to catch it before deploy.

By Flavius Munteanu,

You write a migration. It looks safer than the default:

CREATE INDEX CONCURRENTLY idx_users_email ON users(email);

That one word matters. Without CONCURRENTLY, Postgres takes a SHARE lock on the table while it builds the index, which blocks writes for the duration of the build. On a large production table, that can be long enough to turn a normal deploy into an incident.

But then the migration runner wraps the statement in a transaction and Postgres rejects it:

ERROR: CREATE INDEX CONCURRENTLY cannot run inside a transaction block

This is one of the stranger migration footguns: the safe-looking fix is still wrong if it runs in the wrong execution context.

Why Postgres rejects it

CREATE INDEX CONCURRENTLY does not behave like a normal DDL statement.

It builds the index in phases so reads and writes can continue while the build happens. That requires multiple internal transaction boundaries. Postgres cannot do that work while your migration is already inside an explicit BEGIN block, so it refuses to run.

The same category of problem shows up with other operations that need autocommit behavior:

  • DROP INDEX CONCURRENTLY
  • REINDEX CONCURRENTLY
  • DETACH PARTITION CONCURRENTLY
  • REFRESH MATERIALIZED VIEW CONCURRENTLY

The exact lock behavior differs by statement, but the operational rule is the same: concurrent DDL needs to run outside the migration transaction.

Why ORMs make this easy to miss

Most migration tools wrap migrations in a transaction by default. That default is reasonable. If a migration fails halfway through, the database rolls back instead of keeping partial schema changes.

The problem is that atomic migrations and concurrent index builds want opposite things:

  • The migration runner wants one transaction around the whole file.
  • Postgres needs CREATE INDEX CONCURRENTLY to run outside that transaction.

So the safe pattern usually has two parts:

SET lock_timeout = '2s';
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_users_email ON users(email);

And a runner-specific opt-out:

  • TypeORM: mark the migration with transaction = false.
  • Knex: set the migration config to disable transactions for that file.
  • Rails: use disable_ddl_transaction!.
  • Any raw SQL runner: run this migration without an outer BEGIN and COMMIT.

The important part is not the syntax. The important part is that the transaction wrapper is a deployment setting, not visible from the SQL alone unless your tooling knows how the migration will run.

The invalid index trap

A failed concurrent index build can leave an invalid index behind. Invalid indexes are ignored by the planner, but they still take disk space and can confuse later migration attempts.

You can look for invalid indexes with:

SELECT
  ns.nspname AS schema_name,
  cls.relname AS index_name,
  tbl.relname AS table_name
FROM pg_index idx
JOIN pg_class cls ON cls.oid = idx.indexrelid
JOIN pg_class tbl ON tbl.oid = idx.indrelid
JOIN pg_namespace ns ON ns.oid = cls.relnamespace
WHERE idx.indisvalid = false;

Cleanup usually looks like:

DROP INDEX CONCURRENTLY IF EXISTS idx_users_email;

That cleanup statement has the same transaction restriction. If you run it inside a transaction, it fails too.

What pgfence catches

pgfence checks for concurrent operations inside explicit transactions:

BEGIN;
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);
COMMIT;

That produces a policy error:

CREATE INDEX CONCURRENTLY inside a transaction: this will fail at runtime

pgfence also understands common migration wrappers. For example, TypeORM migrations are transaction-wrapped by default, so a TypeORM migration with CREATE INDEX CONCURRENTLY is treated as unsafe unless the migration opts out with transaction = false.

The goal is simple: fail the CI check before Postgres fails the deploy.

The safer review checklist

When you see CREATE INDEX CONCURRENTLY in a migration, check four things:

  1. Is it actually using CONCURRENTLY?
  2. Is it outside the migration transaction?
  3. Does it use IF NOT EXISTS if the migration might be retried?
  4. Is lock_timeout set before the statement?

That is the difference between “we used the safe keyword” and “this migration can actually run safely.”

← All posts