How to add columns with a default value to large tables

If you worked with a large database you know that you can't just run an alter table to add a column with a default value, maybe you tried it and it failed -- like me awhile ago 🤕 -- but what if you really need to do that? That's what I'm about to tell you in these four simple steps:

1. Add the column with no default

class AddCurrencyToAccounts < ActiveRecord::Migration[5.2]
  def change
    add_column :accounts, :currency, :string
  end
end

This is going to run this SQL:

ALTER TABLE "accounts" ADD "currency" character varying

2. When creating new records for that table, set the desired default value using your code

There are tons of ways of accomplishing it and it really depends on how you create your records, but this is good enough for the example:

account.currency ||= "USD"

3. Backfill all your table rows with the desired default value

If you have a large database you also probably already have a way of running code for all your records asynchronously to finish it really fast -- If not let me know -- but if you don't have it, a .find_each or a .find_in_batches would probably do the trick:

Account.where(currency: nil).find_each do |account|
  # .update_attributes doesn't touch updated_at
  account.update_attributes(currency: "USD")
end

Why not just run Account.where(currency: nil).update_all(currency: "USD") ?

Because this ☝️ is going to lock your table while your database updates all your rows, that's why we need to update individual records so the database locking only happens per row.

4. Finally, add the default value to your column 🎉

class AddDefaultToCurrencyOnAccounts < ActiveRecord::Migration[5.2]
  def change
    change_column :accounts, :currency, :string, default: "USD", null: false
  end
end

This is going to run this SQL

ALTER TABLE "accounts"
ALTER COLUMN "currency" TYPE character varying,
ALTER COLUMN "currency" SET DEFAULT 'USD',
ALTER "currency" SET NOT NULL

After that you can remove your code that sets the default value when creating your records from step #2 and it's done 🎉

Comments

[ShinJustinHolly3317]

you sure this works?

[lucasprag]

I've done that several times. Did you face any problems?