Isometric illustration of a large database table next to an index tree being built alongside it

A PostgreSQL concurrent index lets you add an index to a busy table without blocking inserts, updates and deletes. In Rails, that’s add_index :orders, :account_id, algorithm: :concurrently inside a migration with disable_ddl_transaction!. It’s the right default for any table with real traffic. The plain add_index holds a lock that blocks every write for as long as the build takes, which can be minutes on a large table. Concurrent builds have their own failure modes, though: they’re killed by statement timeouts, they can leave an invalid index behind, and they can outlast your deploy timeout. This guide covers the migration code, the timeouts, unique and partial indexes, monitoring a build in progress, and cleaning up when one fails.

TL;DR

  • Use algorithm: :concurrently with disable_ddl_transaction! for every new index on a table that receives writes. A plain add_index takes a SHARE lock that blocks all writes during the build.
  • Concurrent builds scan the table twice and wait for running transactions, so they take longer. Disable statement_timeout for the migration, or the build is cancelled midway.
  • A failed concurrent build leaves an INVALID index that slows writes and isn’t used for reads. Find it in pg_index and drop it with remove_index ..., algorithm: :concurrently.
  • Watch progress with pg_stat_progress_create_index, and make sure your deploy tool’s timeout is longer than the build, or run long index builds outside the deploy.
  • remove_index, add_reference and REINDEX all have concurrent forms. Use them on production tables too.

Table of Contents

What is a concurrent index in PostgreSQL?

A concurrent index is built with CREATE INDEX CONCURRENTLY, which takes a lock that allows normal reads and writes to continue during the build. PostgreSQL does extra work to keep the new index consistent with rows that change while it’s being built.

CREATE INDEX blocks writes for the whole build; CREATE INDEX CONCURRENTLY scans twice and waits for transactions, but writes keep working

  CREATE INDEX CREATE INDEX CONCURRENTLY
Rails add_index :t, :col add_index :t, :col, algorithm: :concurrently
Lock SHARE: reads OK, writes blocked SHARE UPDATE EXCLUSIVE: reads and writes OK
Table scans One Two, plus waits for running transactions
Duration Shorter Significantly longer
Inside a transaction Yes No (needs disable_ddl_transaction!)
On failure Rolled back cleanly Leaves an INVALID index
Two at once on the same table Possible Only one concurrent build per table at a time

The build goes through phases you can watch (Step 5): wait for transactions that might modify the table, build the index from a first scan, wait again, validate with a second scan, then wait for remaining older snapshots before marking the index valid.

When should you use algorithm: :concurrently?

Use it for any index on a table that receives writes in production while the migration runs. Skip it only for tables you’re creating in the same migration, or tiny tables where a lock of a few milliseconds is harmless.

Situation Concurrently?
New index on an existing table with traffic Yes
Index on a table created in the same migration No (the table is empty and unused)
Small lookup table (hundreds of rows) Optional
Unique index on existing data Yes, plus duplicate cleanup first
add_reference / add_belongs_to on an existing table Yes, index: { algorithm: :concurrently }
Removing an index from a busy table Yes, remove_index ..., algorithm: :concurrently

strong_migrations enforces this automatically: it rejects a non-concurrent add_index on an existing table and shows the safe version. The wider set of safe migration patterns is in the zero-downtime Rails migrations playbook.

Prerequisites

  • Rails 7.1+ (examples use Rails 8.0 and Ruby 3.3) on PostgreSQL 13+. REINDEX CONCURRENTLY needs PostgreSQL 12+.
  • Access to psql on production, or a console that can run SQL, to monitor builds.
  • Optional: the strong_migrations gem.

Step 1: Write the concurrent index migration

Put each concurrent index in its own migration, disable the transaction, and pass algorithm: :concurrently.

# db/migrate/20261029090000_add_index_on_orders_account_id.rb
class AddIndexOnOrdersAccountId < ActiveRecord::Migration[8.0]
  disable_ddl_transaction!

  def change
    add_index :orders, :account_id, algorithm: :concurrently, if_not_exists: true
  end
end

Why each part matters:

  • disable_ddl_transaction! is required because CREATE INDEX CONCURRENTLY can’t run inside a transaction block. Rails wraps every migration in one unless you disable it.
  • One index per migration. Without a transaction, a failure halfway through a migration with three statements leaves the first ones applied and the migration not recorded as run. Rerunning it then fails on the parts that already exist.
  • if_not_exists: true makes a rerun safe after a partial failure: if the index was created and valid, the migration simply succeeds.

The migration is reversible: remove_index is derived automatically, and Rails also applies algorithm: :concurrently to it on rollback.

Step 2: Set timeouts that let the build finish

A concurrent build on a large table can run for minutes, so the statement_timeout you set for web requests would cancel it. At the same time, you still want a short lock_timeout so the build doesn’t queue behind a long transaction.

If you set statement_timeout for all connections in database.yml, as recommended in the Rails request timeout guide, override it inside index migrations:

# db/migrate/20261029090000_add_index_on_orders_account_id.rb
class AddIndexOnOrdersAccountId < ActiveRecord::Migration[8.0]
  disable_ddl_transaction!

  def up
    execute "SET statement_timeout = 0"     # let the build run as long as it needs
    execute "SET lock_timeout = '10s'"      # but don't wait forever for the initial lock
    add_index :orders, :account_id, algorithm: :concurrently, if_not_exists: true
  ensure
    execute "RESET statement_timeout"
    execute "RESET lock_timeout"
  end

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

With strong_migrations, set these once instead:

# config/initializers/strong_migrations.rb
StrongMigrations.statement_timeout = 1.hour
StrongMigrations.lock_timeout = 10.seconds

Two other settings make builds faster:

-- per session, for the migration connection (PostgreSQL 11+ builds btree indexes in parallel)
SET maintenance_work_mem = '1GB';
SET max_parallel_maintenance_workers = 2;

Only raise maintenance_work_mem if the database server has the memory to spare. It’s allocated per build.

Step 3: Unique, partial, expression and reference indexes

Every index type Rails supports can be built concurrently. Each one has one detail to get right.

Unique indexes. The build fails if duplicates exist, and leaves an invalid index. Find and clean duplicates first:

SELECT lower(email), count(*) FROM users GROUP BY lower(email) HAVING count(*) > 1;

# db/migrate/20261029091000_add_unique_index_on_users_email.rb
class AddUniqueIndexOnUsersEmail < ActiveRecord::Migration[8.0]
  disable_ddl_transaction!

  def change
    add_index :users, "lower(email)", unique: true, name: "index_users_on_lower_email",
              algorithm: :concurrently, if_not_exists: true
  end
end

Keep a model validation (validates :email, uniqueness: { case_sensitive: false }) for friendly error messages. The index is what actually guarantees uniqueness under concurrent writes.

Partial indexes index only the rows you query, so they’re smaller and faster to build:

add_index :orders, :created_at, where: "status = 'pending'", name: "index_orders_pending_on_created_at",
          algorithm: :concurrently

Composite indexes. Put equality columns first and the sort or range column last, matching how you query:

add_index :orders, [:account_id, :status, :created_at], algorithm: :concurrently

References. add_reference adds a column and an index in one step. Make the index concurrent:

# db/migrate/20261029092000_add_coupon_to_orders.rb
class AddCouponToOrders < ActiveRecord::Migration[8.0]
  disable_ddl_transaction!

  def change
    add_reference :orders, :coupon, index: { algorithm: :concurrently }
  end
end

Add the foreign key in a separate migration with validate: false, then validate it, as shown in the zero-downtime migrations guide.

Step 4: Remove and rebuild indexes concurrently

Dropping an index also takes a lock that blocks writes, briefly but sometimes behind a long query. Use the concurrent form.

# db/migrate/20261029093000_remove_unused_index_on_orders_legacy_ref.rb
class RemoveUnusedIndexOnOrdersLegacyRef < ActiveRecord::Migration[8.0]
  disable_ddl_transaction!

  def change
    remove_index :orders, :legacy_ref, algorithm: :concurrently, if_exists: true
  end
end

Find unused indexes before removing anything:

SELECT s.relname AS table, s.indexrelname AS index,
       s.idx_scan AS scans, pg_size_pretty(pg_relation_size(s.indexrelid)) AS size
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0 AND NOT i.indisunique AND NOT i.indisprimary
ORDER BY pg_relation_size(s.indexrelid) DESC;

Check the statistics on every replica too, since an index unused on the primary may serve reads on a replica. Statistics reset on restart.

Rebuild a bloated index without locking writes (PostgreSQL 12+):

# db/migrate/20261029094000_reindex_orders_account_id.rb
class ReindexOrdersAccountId < ActiveRecord::Migration[8.0]
  disable_ddl_transaction!

  def up
    execute "SET statement_timeout = 0"
    execute "REINDEX INDEX CONCURRENTLY index_orders_on_account_id"
  end

  def down; end
end

Step 5: Monitor the build and verify it works

Watch a running build from a second session, then confirm the finished index is valid and used by the queries it was meant for.

-- Progress of running index builds
SELECT p.pid,
       c.relname AS table,
       p.phase,
       round(100.0 * p.blocks_done / nullif(p.blocks_total, 0), 1) AS pct_blocks,
       p.tuples_done, p.tuples_total
FROM pg_stat_progress_create_index p
JOIN pg_class c ON c.oid = p.relid;

Phases you’ll see include building index: scanning table, waiting for old snapshots and index validation: scanning table. A build that sits in a “waiting” phase is blocked by a long-running transaction. Find it:

SELECT pid, state, now() - xact_start AS xact_age, left(query, 70) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start
LIMIT 5;

After the migration finishes:

-- Valid and ready?
SELECT indexrelid::regclass AS index, indisvalid, indisready
FROM pg_index WHERE indrelid = 'orders'::regclass;

-- Used by the query?
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE account_id = 42 ORDER BY created_at DESC LIMIT 20;
-- Index Scan using index_orders_on_account_id_and_status_and_created_at on orders ...

Then check that writes kept flowing during the build. Your APM’s write latency or pg_stat_activity should show no lock waits on that table while the index was being built.

How Heroku, Render, Fly.io, Upsun and Enkihost run long index migrations

The database side of a concurrent build is the same everywhere. What differs is how long each platform lets a migration run before it gives up on the deploy, and a slow index build is often the first migration that hits that limit.

Platform Where migrations run Time limit What happens if the build runs longer
Heroku release: phase in Procfile 1 hour, not extendable Release fails, old release keeps running, index left INVALID if the build was interrupted
Render Pre-deploy command 30 minutes Deploy fails, previous deploy keeps serving
Fly.io release_command on a temporary Machine Configurable timeout Deploy aborted
Upsun deploy hook No documented hard limit Idempotent requests are held while the hook runs, so a long build shows up as slow page loads
Kamal 2 Docker entrypoint db:prepare deploy_timeout (30 s default) New container never turns healthy; deploy fails while the migration may still be running
Enkihost db:prepare in the new container’s entrypoint Not documented Not documented; run long builds from the console with bin/rails db:migrate before deploying

The Kamal row deserves attention. A 3-minute concurrent index build in the entrypoint will fail a deploy with the default 30-second deploy_timeout, even though the build itself is fine. For long builds, either raise deploy_timeout, or run the migration before deploying:

kamal build push
kamal app exec --primary --version=$(git rev-parse HEAD) "bin/rails db:migrate"
kamal deploy --skip-push

On Upsun, because requests are held during the deploy hook, consider running very long index builds from a one-off SSH session (upsun ssh -- bin/rails db:migrate) at a quiet time instead of inside the hook.

Enkihost does zero-downtime deploys, so the old release serves traffic while the new one prepares. Concurrent indexes are what make that safe: the old release keeps writing to the table while the index builds.

Troubleshooting concurrent index builds

CREATE INDEX CONCURRENTLY cannot run inside a transaction block

PG::ActiveSqlTransaction: ERROR:  CREATE INDEX CONCURRENTLY cannot run inside a transaction block

Add disable_ddl_transaction! to the migration class. If you use structure.sql and load it in tests, that’s unaffected. The restriction only applies to the migration.

canceling statement due to statement timeout

ActiveRecord::QueryCanceled: PG::QueryCanceled: ERROR:  canceling statement due to statement timeout

The build ran longer than statement_timeout. Set it to 0 inside the migration (Step 2), drop the invalid index left behind, and run the migration again.

An INVALID index after a failed build

SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid;
-- index_orders_on_account_id

The build was interrupted (timeout, deploy cancelled, duplicate key, lost connection). The invalid index is still updated on every write but never used by queries, so it only costs you. Drop it concurrently and retry:

# db/migrate/20261029095000_retry_index_on_orders_account_id.rb
class RetryIndexOnOrdersAccountId < ActiveRecord::Migration[8.0]
  disable_ddl_transaction!

  def up
    execute "SET statement_timeout = 0"
    remove_index :orders, :account_id, algorithm: :concurrently, if_exists: true
    add_index :orders, :account_id, algorithm: :concurrently
  end

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

Note that if_not_exists: true won’t help here: an invalid index exists, so the add is skipped. Always check indisvalid after a failure.

could not create unique index ... Key (...) is duplicated

PG::UniqueViolation: ERROR:  could not create unique index "index_users_on_lower_email"
DETAIL:  Key (lower(email::text))=(ana@example.com) is duplicated.

Duplicates exist, or were inserted during the build because the app doesn’t enforce uniqueness yet. Clean them up, deploy a validation so no new ones appear, drop the invalid index, and build again.

The build is stuck in waiting for old snapshots

A long-running transaction, often an idle in transaction session from a console or a stuck job, is older than the build. The build waits for it to finish. Find it with the pg_stat_activity query in Step 5 and end it with SELECT pg_terminate_backend(<pid>); if it’s safe to do so.

Author Perspective: always concurrently, always one per migration

I write every index migration the same way now: disable_ddl_transaction!, one add_index ... algorithm: :concurrently, statement_timeout off and a short lock_timeout. It’s a few extra lines, and in exchange I never have to think about whether a table is “big enough” to need it, which is the question that bites you on the table that grew while nobody was looking. The other habit is checking pg_index for NOT indisvalid after any failed deploy. Invalid indexes are silent. They don’t error, they just make writes a little slower, forever, until someone looks.

PostgreSQL migrations on Enkihost

Concurrent indexes are what let schema changes ship during normal traffic. On Enkihost:

Enkihost

  • Zero-downtime deploys: the current release keeps serving while the new one migrates, and concurrent index builds keep its writes flowing.
  • PostgreSQL add-on that injects DATABASE_URL, so the timeouts in database.yml variables and the per-migration overrides above apply exactly as written.
  • Per-app resource isolation, so an index build in your app’s database work doesn’t compete with other apps’ web traffic on the app side.
  • Redis add-on that injects REDIS_URL for apps that keep Sidekiq or caching off PostgreSQL during heavy migration windows.

Rails and Sinatra apps run on Ignite (9 EUR per month after a 14-day free trial, with PostgreSQL and Redis included) or Blaze (29 EUR per month, with high availability and autoscaling). The free Spark plan is for Jekyll sites. See enkihost.com.

FAQ

Does add_index lock the table in Rails?

Yes. A plain add_index runs CREATE INDEX, which takes a SHARE lock that allows reads but blocks inserts, updates and deletes until the index is built. On large tables that can mean minutes of blocked writes. Use algorithm: :concurrently with disable_ddl_transaction! to keep writes working.

Why does algorithm: :concurrently need disable_ddl_transaction!?

PostgreSQL does not allow CREATE INDEX CONCURRENTLY inside a transaction block, because the build commits in several steps and waits for other transactions in between. Rails wraps migrations in a transaction by default, so you have to turn that off for the migration that builds the index.

How long does a concurrent index take to build?

It depends on table size, the number of columns, server resources and concurrent load. It takes significantly longer than a normal index because PostgreSQL scans the table twice and waits for running transactions. Monitor progress with pg_stat_progress_create_index to estimate the remaining time.

What happens if a concurrent index build fails?

PostgreSQL leaves the index in an INVALID state. It is maintained on every write but not used for queries. Find invalid indexes with SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid, drop them with DROP INDEX CONCURRENTLY, and run the build again after fixing the cause.

Can I create several concurrent indexes at once?

You can build concurrent indexes on different tables at the same time, but only one concurrent index build can run on a given table at a time. In Rails, put each index in its own migration, so a failure leaves a clear, rerunnable state.

Sources