Adding Indexes Concurrently in Rails

A slow endpoint needs an index on orders.customer_id. The obvious migration — add_index :orders, :customer_id — works perfectly in development, where the table has two hundred rows. In production the table has ninety million, the build takes six minutes, and PostgreSQL’s plain CREATE INDEX holds a SHARE lock that blocks every insert, update and delete on orders for all six. Rails has a built-in answer, algorithm: :concurrently, but it only works when the migration’s transaction is disabled, and a concurrent build that fails leaves debris that breaks the next attempt. This guide covers the correct migration, the retry-safe variant, removal, and the MySQL equivalent, as part of Rails Active Record Migrations.

add_index vs add_index … algorithm: :concurrently Two panels. Plain add_index: wrapped in a transaction, CREATE INDEX takes a SHARE lock, writes blocked for the entire build, rolls back cleanly on failure. Concurrent: disable_ddl_transaction!, CREATE INDEX CONCURRENTLY under SHARE UPDATE EXCLUSIVE, writes continue, slower, a failure leaves an INVALID index. add_index vs add_index … algorithm: :concurrently add_index inside the migration transaction CREATE INDEX → SHARE lock INSERT/UPDATE/DELETE blocked for the build failure rolls back cleanly blocks writes for minutes algorithm: :concurrently disable_ddl_transaction! CREATE INDEX CONCURRENTLY writes continue during the build failure leaves an INVALID index online; handle failures
The concurrent form trades a slower build and a messier failure mode for never blocking writes — the right trade for any table that serves traffic.

Symptom / Error Signatures

You are here because of one of these:

  • During a deploy, pg_stat_activity shows application sessions waiting on Lock behind CREATE INDEX "index_orders_on_customer_id" ON "orders" ("customer_id"), and write latency spikes for the build’s duration.
  • strong_migrations rejected the migration with Adding an index non-concurrently blocks writes.
  • You added algorithm: :concurrently and the migration failed: PG::ActiveSqlTransaction: ERROR: CREATE INDEX CONCURRENTLY cannot run inside a transaction block.
  • A retry after a failed concurrent build fails with PG::DuplicateTable: ERROR: relation "index_orders_on_customer_id" already exists, or succeeds silently while the index remains unusable.

Root Cause Analysis

Rails runs each PostgreSQL migration inside a transaction. CREATE INDEX CONCURRENTLY cannot run in one, because it commits internally between the phases of its build (register the index, wait for old transactions, scan, wait again, validate). disable_ddl_transaction! in the migration class removes the wrapping transaction for that migration only; algorithm: :concurrently makes add_index emit the concurrent statement.

Without the transaction, failure semantics change. If a concurrent build fails — a deadlock, a unique violation, a cancelled deploy, a statement_timeout — PostgreSQL leaves the index in place, marked INVALID. The planner ignores it, but every write still maintains it. Rails does not record the migration version (it failed), so the next deploy reruns the migration and collides with the leftover name. Rails 6.1+ if_not_exists: true avoids the error but also skips the rebuild, leaving the invalid index forever — the general problem described in cleaning up invalid indexes after a failed build.

Situation after failure What add_index does on retry What you need
invalid index exists, no if_not_exists raises DuplicateTable drop the invalid index, then rebuild
invalid index exists, if_not_exists: true does nothing drop the invalid index, then rebuild
no index exists builds it nothing extra
Where a Concurrent Build Can Fail Timeline of a concurrent index build in phases. Register the index and commit briefly; wait for transactions that started before; scan the table and build; wait again; validate. A long transaction delays the waits; a lock timeout during the first wait or a unique violation during the build leaves an invalid index. Where a Concurrent Build Can Fail Build phases reg wait 1 scan + build wait 2 validate Failure points lock timeout unique violation / cancel long txn start valid brief lock waiting on old txns working failure leaves INVALID
The build spends most of its time in the scan, but the waits are where long transactions and lock timeouts bite.

Immediate Mitigation

1. Stop a blocking build. If a plain CREATE INDEX is blocking writes now, cancel it; it rolls back completely.

-- PostgreSQL · requires pg_signal_backend · safe: plain CREATE INDEX is transactional
SELECT pid, now() - query_start AS running, left(query, 80) FROM pg_stat_activity
WHERE query LIKE 'CREATE INDEX%' AND query NOT LIKE '%CONCURRENTLY%' AND state = 'active';
SELECT pg_cancel_backend(<pid>);

2. Rewrite the migration as a concurrent build. One index per migration, nothing else in the class.

# Ruby · db/migrate/20260918101500_add_index_on_orders_customer_id.rb · PostgreSQL
# WARNING: no transaction — keep only this operation here; a failure can leave an INVALID index.
class AddIndexOnOrdersCustomerId < ActiveRecord::Migration[7.1]
  disable_ddl_transaction!

  def change
    add_index :orders, :customer_id, algorithm: :concurrently
  end
end
# ROLLBACK PATH: db:rollback runs remove_index with algorithm: :concurrently.

3. If a previous attempt failed, clean up before rerunning. Detect and drop the invalid leftover; remove_index also accepts algorithm: :concurrently.

# Ruby · migration that makes a rerun safe · PostgreSQL · disable_ddl_transaction! required
class RebuildIndexOnOrdersCustomerId < ActiveRecord::Migration[7.1]
  disable_ddl_transaction!

  def up
    invalid = select_value(<<~SQL)
      SELECT 1 FROM pg_index i JOIN pg_class c ON c.oid = i.indexrelid
      WHERE c.relname = 'index_orders_on_customer_id' AND NOT i.indisvalid
    SQL
    remove_index :orders, name: :index_orders_on_customer_id, algorithm: :concurrently if invalid
    add_index :orders, :customer_id, algorithm: :concurrently, if_not_exists: true
  end

  def down
    remove_index :orders, :customer_id, algorithm: :concurrently, if_exists: true
  end
end

Permanent Fix / Long-Term Pattern

Make the concurrent form the only form. strong_migrations enforces it for existing tables, as described in using strong_migrations to catch unsafe DDL. New tables are the exception: an index added in the same migration that creates the table is instant and belongs inside that migration’s transaction.

Give concurrent builds the right timeouts. lock_timeout should be short, because the build waits for locks at its start; statement_timeout must be long enough for the whole build, because cancelling it partway leaves an invalid index. With strong_migrations, set StrongMigrations.statement_timeout generously, or override it for one migration with execute "SET statement_timeout = 0" at the top. Check for duplicates before building a unique index, since a concurrent unique build fails at the very end if any exist. And add a post-deploy check for invalid indexes to the pipeline.

MySQL does not need any of this ceremony for most secondary indexes: InnoDB builds them online by default. Make the requirement explicit so a change that cannot run online fails instead of silently locking:

# Ruby · migration · MySQL 8.0 · online DDL, no disable_ddl_transaction! needed (DDL is not transactional)
# WARNING: on very large tables replicas replay the build as one statement; consider gh-ost.
class AddIndexOnOrdersCustomerId < ActiveRecord::Migration[7.1]
  def change
    add_index :orders, :customer_id, algorithm: :inplace, lock: :none
  end
end

A note on schema.rb: concurrent and non-concurrent indexes dump identically, so the dump does not record how an index was built — only that it exists. That is fine for new environments, where the table is empty and a plain build is instant, and it means you never need to special-case the dump.

For very large MySQL tables, replica lag during an in-place build can be severe, which is where an online schema change tool helps; see gh-ost vs pt-online-schema-change.

Deploying an Index Migration Pipeline for a Rails index migration. strong_migrations confirms concurrent form; a gate checks for duplicates if unique; the release phase runs the migration with a short lock timeout and long statement timeout; a gate checks indisvalid; application code that relies on the index ships next. Deploying an Index Migration strong_migrations concurrent form dupes? unique index Release phase lock 5 s, statement long valid? indisvalid Ship dependent code next release clean data first drop + rebuild fail
The index ships one release before the code that depends on it, and the pipeline proves it is valid before that code goes out.

Verification Checklist

Frequently Asked Questions

Why can’t Rails build a concurrent index inside the migration transaction? Because PostgreSQL forbids it: a concurrent build commits several times internally so other sessions can see the index at each stage. disable_ddl_transaction! removes Rails’ wrapping transaction for that one migration.

Is it safe to add an index concurrently on a brand-new table? It is harmless but unnecessary. On a new, empty table a plain add_index is instant, and keeping it in the table-creation migration preserves atomicity. strong_migrations does not flag indexes on tables created in the same migration.

What happens to data written during the concurrent build? It is indexed. PostgreSQL’s concurrent build performs a second pass to pick up rows written during the first scan, then waits for older transactions before marking the index valid.

How do I remove an index without blocking? Use remove_index :orders, :customer_id, algorithm: :concurrently in a migration with disable_ddl_transaction!. A plain remove_index takes ACCESS EXCLUSIVE briefly, which can queue behind long transactions like any exclusive lock.

Related Articles