How to alter `id` column type for large tables in Rails with MySQL

user_1559360 0
Viewed 243

What's the recommended way to alter a column from int(10) to bigint(8) on a table with 2,000,000,000+ records in Rails considering:

  1. Runs in production and is exposed to customers
  2. Records are continuously being added to the table
  3. As mentioned above the table is large

Here's a sample migration that illustrates what the outcome should be:

class ChangeTableNameIdType < ActiveRecord::Migration
  def change
    execute('ALTER TABLE table_name MODIFY COLUMN id BIGINT(8) NOT NULL AUTO_INCREMENT')
  end
end
migration ruby-on-rails ruby mysql
1 Answers

At Shopify we used a fork of LHM that allowed for the migration to happen while the system is up and running. Way better than inline migrations that require you to take the system offline.

It will handle billions of records but it's important to understand it will create a parallel table during the migration process and then cuts over to the new table so you'll want to ensure you have the DB space available.

To answer your points specifically:

Runs in production and is exposed to customers

Yes this will run in production and can be safely run in parallel while customers use your product.

Records are continuously being added to the table

New records are written to both the existing table and the LHM table as the LHM table is written. Once the existing table and LHM table are update to date/in-sync you can do a cutover to the LHM table thereby completing the migration.

As mentioned above the table is large

Works on 10s of billions of records. Have personal experience using it at this scale. It works.

user_2516590
Related