How to add columns default values to large tables

November 17, 2019 rails

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

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")

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

This is going to run this SQL

ALTER TABLE "accounts"
ALTER COLUMN "currency" TYPE character varying,

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

This post is also available on DEV.