How to add columns with a default value to large tablesNovember 17, 2019 rails , database
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 🎉