What happens after I add an index to a large table in a production Rails app and then migrate?

Question

Rails 3.2 app running on Heroku with Postgres.

I added an index add_index :lines, :event_id

Events have_many Lines. There are about about 2 million Lines and a million Events.

I pushed to Heroku and migrated.

Does it take time? Does it slow things down at first?

Answer 1

It blocks insert, update and delete operations on your lines table until the index build is finished. So yes, if you haven't added your index concurrently then it may have severe effect on your heroku database.

For zero downtime migrations create indicies concurrently. In ActiveRecord 4 or higher it can be done as follows:

class AddIndexToAsksActive < ActiveRecord::Migration
  # By default, ActiveRecord migrations are run inside a transaction.
  # In order to pass it through, we need to use the following method
  # to run our migration without a transaction.
  disable_ddl_transaction!

  def change
    add_index :asks, :active, algorithm: :concurrently
  end
end

Here is a good article on the subject