create-postgres-indexes-concurrently.md

Create Indexes Concurrently

By default, Postgres' CREATE INDEX locks writes (but not reads) to a table. That can be unacceptable 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.

So, ActiveRecord 4's disable_ddl_transaction! method must be used in combination with algorithm: :concurrently migrations.

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.