change_table with Bulk Option Combines Multiple alter-table Statements
Posted on by Gentaro "hibariya" Terada
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.
