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
that creates the index without preventing concurrent
DELETEs on the table.
To make this option easier to use in migrations, ActiveRecord 4 introduced an
algorithm: :concurrently option for
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
must therefore be used in combination with
algorithm: :concurrently migrations (as seen above).
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.