Create Postgres 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
INSERT
s, UPDATE
s, or DELETE
s 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.