Almost all Ruby on Rails developers might come across scenario where they need to add a new column with a default value to one of the database tables. Most of us (including me) would write following migration statement -

add_column :table_name, :column_name, :boolean, default: false

This is a good practice but would cause downtime if the table has large number of records. It took 3 secs when I ran the migration for a table having 50k records.

-- add_column(:table_name, :column_name, :boolean, {:default=>false})
   -> 3.3695s

3 secs is a long time for production applications that cant afford downtime for a single second.

One of the faster way is to separate single migration statement into two statements.

add_column :table_name, :column_name, :boolean, default: false

to

add_column :table_name, :column_name, :boolean
change_column_default :table_name, :column_name, false

Migration took 0.24 sec to complete on same table having 50k records.

-- add_column(:table_name, :column_name, :boolean)
   -> 0.0014s
-- change_column_default(:table_name, :column_name, false)
   -> 0.0220s

Super fast - 12.5x faster than previous method.

Note - This method will not update default values of existing records. To update existing records, you need to write a background job/rake task that will simply execute single SQL query to update all existing records with default value.

3 Comments

  • Be63bd9064cc4e0c57a4788ae0b210e2

    Karan Valecha 29-Mar-2017 · Reply

    Note - Don't use change as the migration will be Irreversible.

  • B75c20f7d0a8843e8fb97b122534a510

    Varun Lalan 29-Mar-2017 · Reply

    Karan - you can add up and down methods to make migration reversible.

  • E8a35961cc9ac2aca01c3a173f5c8d4a

    Rohan Daxini 30-Mar-2017 · Reply

    Indeed very nice tip Varun, and an important one.

Your comment

*

*