---
title: How to Create Postgres Indexes Concurrently in ActiveRecord Migrations
teaser:
tags: web,postgresql,ruby
author: Dan Croak
published_on: 2013-07-30
---

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`](http://www.postgresql.org/docs/9.2/static/sql-createindex.html) that
creates the index without preventing concurrent `INSERT`s, `UPDATE`s, or
`DELETE`s on the table.

To make this option easier to use in migrations, ActiveRecord 4 introduced an
[`algorithm: :concurrently` option for
`add_index`](https://github.com/rails/rails/commit/2d33796457b139a58539c890624591c97354d334).

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](http://www.postgresql.org/docs/9.2/static/sql-createindex.html#SQL-CREATEINDEX-CONCURRENTLY).
By default, ActiveRecord migrations are run inside a transaction.

ActiveRecord 4's new
[`disable_ddl_transaction!`](https://github.com/rails/rails/commit/b337390889cb4a9f80ed08daf072a043f0e7ddf3)
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.