CODEWITHSUNDEEP
mysqlsqlruby-on-railsruby
Hartator
Hartator

Reputation: 5145

Make migrations painless when dealing with large user base

I wonder what's the best way to deal with slow migrations when you have a large user base?

For example, I am moving the email column of users to a user_emails table to allow multiple emails by user. So, I've a migration that look like this:

class MoveLegacyPrimaryEmailToUserEmails < ActiveRecord::Migration
  def change
    users = User.all
    users.each do |user|
      user_email = UserEmail.new
      user_email.user_id = user.id
      user_email.email = user.legacy_primary_email
      user_email.save!
      user.primary_user_email_id = user_email.id
      user.save!
    end
  end
end

According to a simulation I've done, it will take ~ 66 minutes to run. To speed things up and avoid downtime, I've transformed this into raw SQL statements:

class MoveLegacyPrimaryEmailToUserEmails < ActiveRecord::Migration
  def change
    execute <<-SQL
      INSERT INTO user_emails (email, user_id)
        SELECT legacy_primary_email, id FROM users;
    SQL
    execute <<-SQL
      UPDATE users
        INNER JOIN user_emails
        ON users.legacy_primary_email = user_emails.email
        SET users.primary_user_email_id = user_emails.id;
    SQL
  end
end

Is this the correct way of dealing with this kind of issues or I am missing something obvious?

Upvotes: 1

Views: 36

Answers (1)

James Mason
James Mason

Reputation: 4296

I usually see this tackled with a multi-stage deployment:

  1. Deploy code that uses the new database structure if it's available, and falls back to the old data if not.
  2. Migrate the data to the new structure.
  3. Deploy code that only uses the new data structure.
  4. Migrate the old structure into oblivion.

Having said that, I don't like babysitting hours-long deployments. If you can make it fast by writing raw SQL, do it. If that makes it fast enough to do the deployment in a single step, bonus!

Upvotes: 1

Related Questions