/ rails

Adding new column with default value to high volume database table

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


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.