How we sped up our rails migration setup in 90%

This post is also available in Portuguese.

When using rails, everyone had to create, delete or rollback migrations, as they're a useful resource for structuring your database when you build your application. However, migrations suffer from a problem in terms of scalability: when you add more migrations to your project, they must be run to reflect the database changes in your schema. Moreover, if you switch between branches and run some migrations, your schema.rb may have garbage from other branches, which may force you to rebuild your entire database. This process is not particularly problematic when you have a small project, but in our case, with 6 years of migrations, rebuilding the database was taking a lot of time. Also, as we use a lot of databases that have the same schema, creating new ones was becoming slow, which caused our process of creating new environments to be more painful than it should.

When we think about solutions to this problem, the first thought may be that maybe it's possible to optimize the migration code in some way or that an easy solution may not exist. In parts, optimizing the migrations' code could help, but it wouldn't be able to speed it up significantly.

First of all, we need to consider that, by default, every migration in rails creates a new transaction. It makes sense. You don't want to deal with half migration run in your productive system. However, this approach creates performance problems, as having to execute migrations sequentially with a different transaction for every single one of them may become really slow with the amount of migrations that you have. In our case, we need to ship new environments frequently, and those new environments have to be migrated and seeded from zero. Moreover, a lot of times developers may switch from one branch to another and they have to rebuild the entire database if they don't want to commit garbage in the schema.rb.

Second, our migrations, particularly, had a lot of code that was there for historical reasons. There was lots of code like User.update_all(some_condition: true) that, once run in the productive environments that existed at the time of the migration, had no reason to be there anymore.

We noticed that, even though our migrations were significatively slow, our schema:load was fast, lots of times faster than our migration process. However, the schema:load task was not helpful to run in productive environments. schema:load is not capable of creating views, nor executing Ruby code present in migrations. For example, if we had a SQL code creating a view, it wouldn't be created in schema:load. Moreover, some migrations were executing Ruby code to create records (User.create!(name: 'John'), for instance), and it'd be risky to skip those creations without mapping if they were present in the seeds. Besides the transaction question, when we run a migration, rails adds its timestamp to the table schema_migrations. To check if a migration was previously executed or not, rails will get the timestamp of the migration being considered to run, and then try to find the same timestamp in this table. If rails is able to find the timestamp, then the migration is skipped. Otherwise, it will be executed. Notice that rails will check the timestamp of a migration against the contents of the table, but the inverse does not occur. As a conclusion, there is no problem in this table having entries that do not exist in the migrate folder, and we can add a timestamp to this table if we don't want to run a migration in a specific environment.

Our challenge was to find a way to load the schema and still be able to create those records that only migrations were capable of.

Warning: keep in mind that this solution will remove the advantage of being database-agnostic that schema.rb brings. When you generate the .sql file, you're essentially generating a .sql file that works for your current database adapter, not necessarily for other ones. Also, this solution may not be recommended if you don't have a big problem with migrations being slow, especially because rails doesn't support this way of speeding your migrations up, and keeping the changes history and reversibility may be better in most situations.

Immediately, the first thing that came to our minds was to create a .sql file that does the same as the migrations and then load it. We then thought about using structure.sql as a solution to our problem, as schema.rb is not capable of reflecting creation of views, but structure.sql does. We also needed to place a limit on which migrations would be transformed into plain sql, because we wanted to be able to easily revert the recent ones if needed, and we wanted to guarantee that all the environments we had had already run the migrations that were going to be transformed into plain sql. As we noticed that all of our environments had already run all 2024's migrations, we decided to keep every migration until 2024 as a baseline. Every migration from 2025 onwards would not be converted into plain SQL and would still run normally.

However, this solution had a problem: structure.sql doesn't reflect created or updated records during migrations. Even though it's expected that the developers always create a copy of updates/creates on seed.rb when they are necessary in new environments, we had no guarantee that every create/update that should be ported to new environments was reflected in the seeds file. We then needed to track which migrations had this problem, but we had (literally) thousands of migrations and reading them one by one was simply not viable and very error-prone. Considering this, we needed to automatically track which migrations insert or update a record, so we could at least exclude from our search all the migrations that didn't create any record, which was most of them. We then created an initializer that would only run in the migration process, and it logs which migrations create any operation of this type. Please notice that this initializer is AI generated and is not suited for best coding practices, but it was used once and not kept in our codebase, so it's fine.

config/initializers/migration_data_logger.rb

if defined?(ActiveRecord::Migration)
  module MigrationDataLogger
    class << self
      attr_accessor :enabled

      def start_logging
        @enabled = true
        @log_file = File.open(Rails.root.join('log/migration_data_changes.log'), 'a')
        @current_migration = nil
        puts 'Migration logging started - writing to log/migration_data_changes.log'
      end

      def stop_logging
        @enabled = false
        @log_file&.close
        puts 'Migration logging stopped'
      end

      def log(message)
        return unless @enabled

        @log_file.puts "[#{Time.current}] #{@current_migration}: #{message}"
        @log_file.flush
      end

      def set_migration(migration)
        @current_migration = "#{migration.version} - #{migration.name}"
      end
    end
  end

  ActiveSupport::Notifications.subscribe('sql.active_record') do |_, started, finished, _, payload|
    MigrationDataLogger.log("#{payload[:sql]}") if MigrationDataLogger.enabled && payload[:sql] =~ /^(INSERT|UPDATE|DELETE)/i
  end

  ActiveRecord::Migration.prepend(Module.new do
    def migrate(direction)
      MigrationDataLogger.start_logging
      MigrationDataLogger.set_migration(self)
      super
      MigrationDataLogger.stop_logging
    end
  end)
end

Having these migration names in hand, we reduced thousands that we'd need to analyze to just 70. It was a good number to analyze manually, so we didn't go further with the script, but a more automated way of getting the migrations would be to find some way of getting all the executed sql and its return, and discard the ones that affected zero rows. After this, we moved all the code that was in migrations but acted as a seed to db/seeds.rb, and we could finally get rid of the old migrations without having to pay attention to the records that were created.

After successfully mapping what should be moved to the seeds, we started our process of getting the pre-2025 SQL file. The first step was to add the following line to application.rb:

config.active_record.schema_format = :sql

We then moved all post-2024 migrations to another folder (so they're not run in the migrations), and rebuilt the database. This way, we got a SQL file that reflected all pre-2025 migrations and could be easily loaded. This new file, called structure.sql, was then renamed to structure_baseline.sql.

After doing this, we deleted all pre-2025 migrations and got all post-2024 ones back to our migrate folder.

We then manually generated a migration file with 2024 timestamp that would be run post our last 2024 migration. For example, if our last migration had the 20241230xxxx timestamp, we'd generate 20241231xxxx timestamp. We then inserted its timestamp in all our existing environments by directly editing the schema_migrations table. This way, we could have one migration that loads all the content of the database, but is never executed in environments that already exist. Remember that, for it to work, you have to guarantee that all your environments have already run all the migrations that are being transformed into plain sql.

With these preparations handled, we created the following migration file:

class LoadBaselineSchema < ActiveRecord::Migration[7.0]
  disable_ddl_transaction!

  def change
    ActiveRecord::Base.connection.execute(File.read(Rails.root.join('db/structure_baseline.sql')))
  end
end

Warning: please notice that the disable_ddl_transaction! makes the migration run without a transaction in the database and it may be a problem depending on your context. You'll probably want to remove this line of your code (or at least change it to disable_ddl_transaction! if Rails.env.local?). We chose to use it because it significantly speeds up the .sql loading speed and it's only going to be run in new environments. If some problem occurs in the process of loading the .sql file, we can just recreate the database, since it's a new environment.

After doing this, we then removed the application.rb's added line, since changing the final schema.rb to structure.sql was going to be held in another pull request so we are able to review the changes properly. You can explicitly tell application.rb that you want to use ruby instead of sql if you want to.

config.active_record.schema_format = :ruby

We then wanted to run the migrations so we could regenerate the schema.rb. However, when rails tried to load the structure_baseline.sql, it was trying to recreate the default tables from rails - ar_internal_metadata and schema_migrations. We then manually modified the structure.sql lines CREATE TABLE public.ar_internal_metadata and CREATE TABLE public.schema_migrations to include an IF NOT EXISTS clause.
We also had problems with the indexes of these tables, and we added conditions to only add the indexes if they don't exist.

ALTER TABLE ONLY public.ar_internal_metadata
    ADD CONSTRAINT ar_internal_metadata_pkey PRIMARY KEY (key);
ALTER TABLE ONLY public.schema_migrations
    ADD CONSTRAINT schema_migrations_pkey PRIMARY KEY (version);

became

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint
        WHERE conname = 'ar_internal_metadata_pkey'
    ) THEN
        ALTER TABLE ONLY public.ar_internal_metadata
            ADD CONSTRAINT ar_internal_metadata_pkey PRIMARY KEY (key);
    END IF;
END
$$;

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint
        WHERE conname = 'schema_migrations_pkey'
    ) THEN
        ALTER TABLE ONLY public.schema_migrations
            ADD CONSTRAINT schema_migrations_pkey PRIMARY KEY (version);
    END IF;
END
$$;

Deleting these table creations and index adding may be a better solution as rails automatically creates them, but we didn't test if it works. From now on, the db:migrate task should work.

You may want to delete the line COMMENT ON EXTENSION pg_trgm IS 'text similarity measurement and index searching based on trigrams'; too if you're running with a user that does not have privilege to comment on the extension.

With these modifications, our db:migrate task was significantly sped up, showing results around 90% optimization.