change_table with Bulk Option Combines Multiple alter-table Statements

Today I learned that when doing database-migration on Rails, change_table :#{table_name}, bulk: true let us combine multiple alter-table statements and it could reduce the cost of the whole alteration. That is, instead of executing multiple alter-table separately,

def change
  add_column :users, :first_name, :string, null: false
  add_column :users, :last_name, :string, null: false
end

we can run a single alter-table statement by change_table like as follows.

def change
  change_table :users, bulk: true do |t|
    t.column :first_name, :string, null: false
    t.column :last_name, :string, null: false
  end
end

However, why should we do that? What are the differences between them?

According to PostgreSQL's official document, it reduces the cost of table alterations especially with large tables because by combining multiple table alterations, the number of table scans and rewrites can be reduced.

The main reason for providing the option to specify multiple changes in a single ALTER TABLE is that multiple table scans or rewrites can thereby be combined into a single pass over the table.

A similar explanation can be found on MariaDB's official document. To be precise, it is mentioned only once in the ALGORITHM=COPY section. So as for MariaDB, maybe the effectivity depends on the algorithm selected for the alter-table.

If multiple ALTER TABLE operations are required that each require the table to be rebuilt, then it is best to specify all operations in a single ALTER TABLE statement, so that the table is only rebuilt once.

Note that combining multiple alter-tables is only supported on PostgreSQL, MariaDB, and MySQL.

See Also

Gentaro "hibariya" Terada

Otaka-no-mori, Chiba, Japan
Email me

Loves Ruby, Internet, and Programming.