Create Postgres Indexes Concurrently

By default, Postgres CREATE INDEX locks writes (but not reads) to a table. That can result in unacceptable downtime during a production deploy. On a large table, indexing can take hours.

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

ActiveRecord migrations

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.