Rails add_index algorithm: :concurrently still causes database lock up during migration

Question

In order to prevent database transaction errors during our migrations to the production site, we followed the advice outlined in https://github.com/LendingHome/zero_downtime_migrations (specifically outlined by https://robots.thoughtbot.com/how-to-create-postgres-indexes-concurrently-in), but during the creation of an index on a particularly large table, even the "concurrent" method of index creation locked the table and caused any ActiveRecord creates or updates on that table caused their respective transactions to fail with PG::InFailedSqlTransaction exceptions.

Here is what the migration looked like we're running Rails 4.2 (with ActiveRecord 4.2.7.1):

class AddTypeIndexToModel < ActiveRecord::Migration
  disable_ddl_transaction!

  def change
    add_index :model, :model_type_id, algorithm: :concurrently
  end
end

Answer 1

It turns out the issue was not related to that migration, but one prior that ran in the same batch that was adding a new column to the same table.

Because we added a column to the table, we hit this bug in Active Record https://github.com/rails/rails/issues/12330 which essentially causes all ActiveRecord operations in a transaction to fail due to a stale PreparedStatement until the server is restarted.

We will use the workarounds described in that issue from now on in our migrations.