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.

Context

Consider a User table with 15 million rows.

User
- first_name
- last_name
- email

Goal

We want to add a locked flag to the table to restrict access to users without deleting them.

Technical Plan

Attempt 1

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 false.

> 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.

Attempt 2

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 from and to with Rails 5. Otherwise, we need to implement both up and down methods.

Deploy

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

Takeaway

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 blog@hocnest.com.

Adding a column to a large table