Huge speed gain migrating large tables in Rails

Have you ever needed to make multiple column adds/removes/changes to a large table in Rails in a single migration? If so, you've probably noticed that each of these individual changes issues a single ALTER TABLE command to the database, which can take a long time on a large table. At least with MySQL, combining these all into a single ALTER TABLE dramatically reduces the amount of time it takes for your migration to run.

Rather than write custom SQL for this, the folks at XING have created a very cool Rails plugin called alter_table. Their blog post, Alter Table Rails Plugin, gives more background on this. Check it out, it's a good, quick read.

One thing they didn't mention in their post was what kind of performance gains they observed. I was curious, so I did a quick-and-dirty experiment.

Consider an existing table named "songs" that has 330K records in it. First, I tried just adding a column named "foo" using the normal migration style we're all accustomed to:

On my MacBook Pro, this is what the timings for this looked like:

Now, after rolling that back, installing the alter_table plugin (as per their instructions), I rewrote this migration to use the new alter_table method:

Now, on the same machine, this migration takes roughly half the time because it does all the alterations in a single pass. Here are the new timings:

In real life, I had a table with almost 10 million rows that I needed to do 10 alterations to. Using this plugin, I cut my migration time down by almost 10x! If you find yourself doing more than one alteration on the same table in a single migration, I highly recommend checking out alter_table. You can find their source code at:

http://github.com/xing/alter_table