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
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
method must therefore be used in combination with
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.