DEV Community

Igor Alexandrov for JetRockets

Posted on • Originally published at jetrockets.pro

How to add index to a large working app on Rails?

Well known fact is that PostgreSQL and many other RDBMS lock write access on the table while the index is being created. It is not acceptable when your project is large enough to allow a downtime for such the small adjustment like a new index.
There is a way to avoid the write-lock though. You can create the index concurrently. But how to do it with Rails Migrations?
First of all you can pass :algorithm option:

class AddIndexOnBatchIdToFundTrades < ActiveRecord::Migration[5.0]
  def change
    add_index :fund_trades, :batch_id, algorithm: :concurrently
  end
end
Enter fullscreen mode Exit fullscreen mode

But when you try to run such the migration, you would get the following error:

PG::ActiveSqlTransaction: ERROR:  CREATE INDEX CONCURRENTLY cannot run inside a transaction block
: CREATE  INDEX CONCURRENTLY "index_fund_trades_on_batch_id" ON "fund_trades"  ("batch_id")
Enter fullscreen mode Exit fullscreen mode

That's because any migration by default is executed inside a transaction. Thankfully there is a way to pass it through - use disable_ddl_transaction! to run your migration without a transaction:

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

  def change
    add_index :fund_trades, :batch_id, algorithm: :concurrently
  end
end
Enter fullscreen mode Exit fullscreen mode

Top comments (1)

Collapse
 
codenote04 profile image
codenote04

== 20201019083817 AddIndexOnMyTransactions: migrating ======================
-- add_index(:my_transactions, :id, {:algorithm=>:concurrently})
rake aborted!
StandardError: An error has occurred, all later migrations canceled:

Algorithm must be one of the following: :default, :copy, :inplace

I get above error with mysql.