Rails 7 optimizes remove_columns to use a single SQL statement when supported

ActiveRecord Migrations provides neat ways to handle migrations in databases. This includes adding, updating, and removing columns in databases. Some of these operations can take a lot of time, depending on the translated SQL queries.

One of these slow operations is the remove_columns operations with multiple columns. It is because individual SQL queries are triggered to delete each column. Since, both MySQL and PostgreSQL support dropping multiple columns in a single SQL statement, Rails 7 will now generate a single SQL statement to remove all the columns together if the database supports it.

Since SQLite3 doesn’t support dropping multiple columns, it will still generate multiple SQL queries.

Let’s understand this with the help of an example.

Let’s assume we have a Post model with attributes :content, :description, :date_created_at and :date_published_at.

Due to requirement changes, we now want to remove :description, :date_created_at and :date_published_at

To do this, we would generate a migration, which would look something like this:

class RemoveColumnsFromPosts < ActiveRecord::Migration[6.1]
  def change
    remove_columns :posts, :description, :date_created_at, :date_published_at
  end
end

Before

Before Rails 7, the given migration will generate three separate SQL statements.

PostgreSQL example for the above migration:

  ALTER TABLE "posts" DROP COLUMN "description"
  ALTER TABLE "posts" DROP COLUMN "date_created_at"
  ALTER TABLE "posts" DROP COLUMN "date_published_at"

After

In Rails 7, the above migration will generate only one SQL statement

  ALTER TABLE "posts" DROP COLUMN "description", DROP COLUMN "date_created_at", DROP COLUMN "date_published_at"

It is a good improvement, as regardless of the number of columns to be dropped, we will only be executing one ALTER_TABLE query instead of multiple ALTER_TABLE queries.

There is also a performance improvement to it, as there is only one call to the database. But more importantly, we’ll be making all the changes in one copy operation in the database.