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: