PostgreSQL index with NO lock on Rails

Rails allow us to create a PostgreSQL index that it would not lock the table for writing meanwhile it's being calculated.

That's usually handful if a table has millions of rows and this operation could take hours to release the INSERT/UPDATE/DELETE lock. Maybe the lock downtime is critical to the application.

There are caveats to this approach so please read the PostgreSQL documentation.

In order to do that you'll need to add { algorithm: :concurrently } to the add_index on a Rails migration. Additionally PostgreSQL uses DB transactions to manage this new index creation so we need to disable the Rails migration transaction. Take this example:

class AddIndexOnEventsName < ActiveRecord::Migration[5.0]
  disable_ddl_transaction!

  def change
    add_index(:events, :name, algorithm: :concurrently)
  end
end

In this case I'm using disable_ddl_transaction! to disable the DB transaction that wraps each Rails migrations and I am using algorithm: :concurrently.

This option it will generate an index like that:

CREATE INDEX CONCURRENTLY "index_events_on_name" ON "events" ("name"));