Adding a Column in Production
Adding a column default to an existing field can be a simple change. Although, that can be proven untrue if the respected table has many rows of data. It may result in a long-running migration causing unforeseen side effects, errors, or even downtime. We will look at few common approaches below.
User table with 15 million rows.
User - first_name - last_name - email
We want to add a
locked flag to the table to restrict access to users without deleting them.
First, we generate the migration like so:
> rails generate migration AddLockedToUsers locked:boolean
Now, we can add a default value of
false so initialized users are active. So let’s update the pending migration we just created.
add_column :users, :locked, :boolean, default: false
Lastly, run the migration.
> rails db:migrate
If you open a rails console you will see the default is working as expected. New and existing users will both have
locked set to
> User.new => #<User:0x00007fec71e4d508 id: nil, first_name: nil, last_name: nil, locked: false> > User.first => #<User:0x00007fec71e4d508 id: 1, first_name: 'Joe', last_name: 'Davidson', locked: false>
Problem with Attempt 1
Our User table has over 15 million records. We do not want our database locking up or other dependent code being affected during deployment.
Timing is an issue. We need the new column added quickly. Let’s try another solution.
This time, we are going to break up this feature into two deploys.
Create the new column
The first deployment will include two migrations. One: add the column, and two: add the default.
> rails generate migration AddLockedToUsers locked:boolean > rails db:migrate
Add the default
As a separate unit of work, we will alter the existing column to include a default. Adding a default to an already created column will not populate the old values in the migration. However, new users will have the default.
For data integrity, I will ensure every
User has a value set.
Let’s generate a new migration adding the default.
> rails generate migration ChangeLockedDefaultForUsers
# Rails 4 migration def up change_column_default(:users, :locked, false) end def down change_column_default(:users, :locked, nil) end
# Rails 5 migration def change change_column_default(:users, :locked, from: nil, to: false) end
Notice the difference between rails 4 and 5? We want to make sure each migration is reversible. You can use
to with Rails 5. Otherwise, we need to implement both
Deployment is quick and easy as we did not need to update 15 million records.
Update existing users
For our send phase of this approach, we are going to update all the existing records. Let’s create a rake task that updates the users in batches.
User.where(locked: nil).in_batches.update_all(locked: false)
Problem with Attempt 2
Attempt 2 does not ensure the presence of
locked. The database still always nil values.
> user = User.first > user.locked = nil > user.valid? => true > user.save => true > user => #<User:0x00007fec71e4d508 id: 1, first_name: 'Joe', last_name: 'Davidson', locked: nil>
Adding a null constraint
Once every User has a value for
locked then we add the null constraint. The migration will not pass if values are
nil. That is important to keep in mind because we don’t want to block developers from running their migrations. After all, they didn’t know to run the rake task.
def up change_column_null(:users, :locked, false) end def down change_column_null(:users, :locked, nil) end
Always consider scale when working on a production application. In our example, we discussed how adding a column with a default value and spitting up its migration will avoid production issues.
Did you like this article? Check out these too.
Found this useful? Have a suggestion? Get in touch at