CODEWITHSUNDEEP
mysqlruby-on-railsactiverecorddatabase-migrationrails-migrations
kn330
kn330

Reputation: 532

Suggestion for running ALTER migrations on large tables

We need to add a set of columns to a table that is sufficiently large (2 GB). Running a migration adding just 1 column (add_column) took 690s. Now adding 6 columns will lock the Table for almost 1 hour which is not desirable.

Any suggestions about how to get this done gracefully? [Switching from MySQL to Postgres or any other platform is not an option.]

Upvotes: 3

Views: 407

Answers (2)

Kaka Ruto
Kaka Ruto

Reputation: 5125

Now you can tell MySQL not to lock the table during this operation, and also use the INPLACE algorithm so the entire operation is faster.

class YourMigration < ActiveRecord::Migration[7.0]
  def up
    execute <<-SQL.squish
      ALTER TABLE your_table
      ADD COLUMN investor_id bigint,
      ALGORITHM=INPLACE, LOCK=NONE;
    SQL
  end

  def down
    execute <<-SQL.squish
      ALTER TABLE your_table
      DROP COLUMN investor_id,
      ALGORITHM=INPLACE, LOCK=NONE;
    SQL
  end
end

Upvotes: 0

markets
markets

Reputation: 7033

There is a gem developed by SoundCloud called Large Hadron Migrator (LHM) that simplifies these types of migrations through the use of a copy table.

I hope it can help you.

Upvotes: 2

Related Questions