Read more

Bulk-change multiple table rows in a migration

Arne Hartherz
November 29, 2011Software engineer at makandra GmbH

Using rename_column, remove_column, etc. more than once in a migration makes that migration run slower than it should. Use change_table instead.

Illustration UI/UX Design

UI/UX Design by makandra brand

We make sure that your target audience has the best possible experience with your digital product. You get:

  • Design tailored to your audience
  • Proven processes customized to your needs
  • An expert team of experienced designers
Read more Show archive.org snapshot

Consider this migration:

add_column :users, :name, :string
remove_column :users, :first_name
remove_column :users, :last_name
rename_column :users, :cool, :awesome

Migrating in this case means that all those commands are processed step by step, causing 4 SQL statements to change the table. In turn, your database needs to modify the table structure 4 times. When working on huge tables this can cause migrations to run considerably slower than necessary.

The following will produce only one (= faster) ALTER TABLE statement:

change_table :users, :bulk => true do |t|
  t.column_name, :string
  t.remove :first_name
  t.remove :last_name
  t.rename :cool, :awesome
end

Rails 2 caveats

  • Rails 2 does not understand the extra options hash but seems to build one ALTER statement directly.
  • That one statement is only created when you rename columns.
  • For other operations such as add and remove Rails 2 will still give you single ALTER statements.

More

See RobinWu's APIdock comment Show archive.org snapshot for all methods you can use inside a change_table block.

Posted by Arne Hartherz to makandra dev (2011-11-29 12:18)