NB: This was written as a personal reminder but I thought it would be helpful to a for anyone else looking for an answer for a similar problem.

The environment

• MySQL v5.6+
• Rails 5.1+
• The existing table has a few million records.

The problem

Adding a column on an existing large table with an index on the newly added column without a default value without locking that table. This is oddly specific but that’s what I needed. Out of the box a typical Rails migration would look like this:

class AddOddlySpecificColAndIndex < ActiveRecord::Migration[5.2]
  def up
    add_column :table_name, :column_name, :data_type, index: true
  end
  def down
    remove_column :table_name, :column_name, :data_type
  end
end

In the default behavior, this migration will trigger a table lock that blocks writes until the index has been built. Depending on large the table is, that could be a significant length of time.

The solution

ActiveRecord doesn’t support this out of the box but we can easily pass an option to the SQL statement that allows us to leverage a MySQL feature in order to resolve this very issue.

class AddOddlySpecificColAndIndex < ActiveRecord::Migration[5.2]
  def up
    execute <<~SQL
      ALTER TABLE table_name
      ADD COLUMN column_name data_type,
      ADD INDEX index_table_name_on_column_name (column_name),
      ALGORITHM=INPLACE,
      LOCK=NONE;
    SQL
  end

  def down
    remove_column :table_name, :column_name, :data_type
  end
end

This statement is essentially declaring the same thing as we had previous but with the added LOCK=NONE option.

From the docs:

To avoid accidentally making the table unavailable for reads, writes, or both, specify a clause on the ALTER TABLE statement such as LOCK=NONE (permit reads and writes) or LOCK=SHARED (permit reads). The operation halts immediately if the requested level of concurrency is not available.

The option ALGORITHM=INPLACE insures that we’re not running the operation that would copy the table during this altar which often will take significantly more resources.

The conclusion.

With the a bit of added effort, we’ve accomplished adding a new column with an index without locking the table. There are a few caveats and with any important task, so be sure to do your own research/testing and check the versions of your dependencies.

As this feature builds on top of MySQL’s fast index creation, check to see if there are any limitations for your own use case. PostgreSQL has a similar feature.

If you have an even larger table, I would also look into solutions like the Large Hadron Migrator which aims to also do the same thing but with a few more safeguards in place.

Happy migrating!