How to Create Postgres Indexes Concurrently in ActiveRecord Migrations

By default, Postgres locks writes (but not reads) to a table while creating an index on it. That can result in unacceptable downtime during a production deploy. On a large table, indexing can take hours.

However, Postgres has a CONCURRENTLY option for CREATE INDEX that creates the index without preventing concurrent INSERTs, UPDATEs, or DELETEs on the table.

To make this option easier to use in migrations, ActiveRecord 4 introduced an algorithm: :concurrently option for add_index.

Here’s an example:

class AddIndexToAsksActive < ActiveRecord::Migration
  disable_ddl_transaction!

  def change
    add_index :asks, :active, algorithm: :concurrently
  end
end

The caveat is that concurrent indexes must be created outside a transaction. By default, ActiveRecord migrations are run inside a transaction.

ActiveRecord 4’s new disable_ddl_transaction! method must therefore be used in combination with algorithm: :concurrently migrations (as seen above).

The disable_ddl_transaction! method applies only to that migration file. Adjacent migrations still run in their own transactions and roll back automatically if they fail. Therefore, it’s a good idea to isolate concurrent index migrations to their own migration files.

Use this technique with ActiveRecord 4 to create Postgres indexes concurrently and avoid accidental downtime caused by long, write-blocking database indexing.

Pair with one of our expert developers to level up your skills with Coaching by thoughtbot. Save time learning best practices and techniques for reducing technical debt in Ember, Ruby, Haskell, and Go in 1-on-1 sessions tailored to your goals.