Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
How to change column type (e.g., int -> bigint) without downtime
ActiveRecord::Migration.remove_foreign_key(:current_table, :foreign_table) # no lock
ActiveRecord::Migration.add_column(:current_table, :column_bigint, :bigint) # no lock
copy_data = lambda do
CurrentTable.where(column_bigint: nil).where.not(column: nil).in_batches do |batch|
batch.update_all("column_bigint = column")
end
end
copy_data.call
ActiveRecord::Migration.remove_index :current_table, :column, algorithm: :concurrently # no lock
CurrentTable.transaction do
ActiveRecord::Migration.execute "LOCK TABLE current_table IN ACCESS EXCLUSIVE MODE"
copy_data.call # should be fast
ActiveRecord::Migration.rename_column :current_table, :column, :column_int # no lock
ActiveRecord::Migration.rename_column :current_table, :column_bigint, :column # no lock
end
ActiveRecord::Migration.add_index :current_table, :column, algorithm: :concurrently # no lock
CurrentTable.transaction do
ActiveRecord::Migration.add_foreign_key :current_table, :foreign_table, validate: false # no lock
end
CurrentTable.transaction do
ActiveRecord::Migration.validate_foreign_key :current_table, :foreign_table # row level lock
end
ActiveRecord::Migration.remove_column(:current_table, :column_int)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment