DEV Community: Augusto Pagnossim Frigo

Como aceleramos em 90% a execução das nossas migrações em Rails

Augusto Pagnossim Frigo — Wed, 03 Sep 2025 21:04:09 +0000

Nota: Este post também está disponível em inglês.

Ao usar Rails, todos precisamos criar, deletar ou reverter migrations, já que são um recurso necessário para estruturar o banco de dados ao construir sua aplicação. Migrations, no entanto, enfrentam um problema de escalabilidade: quanto mais migrations são adicionadas, mais tempo leva para executá-las para refletir as mudanças no schema do banco de dados. Além disso, se você troca de branch e executa alguma migration, seu schema.rb pode ficar com resquícios de outras branches, o que pode forçar você a recriar todo o banco de dados. Esse processo não é particularmente problemático em projetos pequenos, mas no nosso caso, com 6 anos de migrations, reconstruir o banco demorava muito. Além disso, como usamos muitos bancos de dados diferentes com o mesmo schema, criar novos bancos estava ficando lento, tornando a criação de novos ambientes mais trabalhosa do que deveria.

Ao buscar soluções para esse problema, a primeira ideia pode ser tentar otimizar o código das migrations de alguma forma. Otimizar o código das migrations ajuda em parte, mas não traria uma melhoria significativa na velocidade.

Primeiro, precisamos considerar que, por padrão, cada migration no Rails é executada em uma nova transaction, o que faz sentido. Você não quer lidar com uma migration pela metade em seu sistema de produção. No entanto, essa abordagem cria problemas de performance, já que executar migrations sequencialmente, cada uma em sua própria transaction, pode ficar muito lento conforme o número de migrations aumenta. No nosso caso, precisamos criar novos ambientes frequentemente, e esses novos ambientes precisam ser migrados e populados do zero. Além disso, muitas vezes os desenvolvedores podem precisar trocar de uma branch para outra e eles precisam reconstruir todo o banco de dados se não quiserem incluir resquícios indesejados no schema.rb.

Além da questão da transaction, quando rodamos uma migration, o Rails adiciona seu timestamp na tabela schema_migrations. Para verificar se uma migration já foi executada, o Rails pega o timestamp da migration e tenta encontrar esse timestamp na tabela. Se encontrar, a migration é pulada; caso contrário, ela é executada. Note que o Rails verifica o timestamp da migration em relação ao conteúdo da tabela, mas o inverso não ocorre. Ou seja, não há problema em ter entradas na tabela que não existem na pasta migrate, e podemos adicionar um timestamp manualmente se não quisermos rodar uma migration em um ambiente específico.

Percebemos que, embora nossas migrations fossem muito lentas, o schema:load era rápido, muito mais rápido que executar as migrations. Porém, a task schema:load não era adequada para ambientes de produção. O schema:load não é capaz de criar views nem executar código Ruby presente nas migrations. Por exemplo, se tivéssemos um código SQL criando uma view, ela não seria criada no schema:load. Além disso, algumas migrations executavam código Ruby para criar registros (User.create!(name: 'John'), por exemplo), e seria arriscado ignorar essas criações sem garantir que estavam presentes no seeds.
O desafio, então, era carregar o schema e ainda assim criar esses registros que só as migrations conseguiam. Trouxemos, assim, uma solução que engloba o melhor dos dois lados.

Atenção: considere que essa solução remove a vantagem de ser agnóstico em relação ao banco de dados que o schema.rb oferece. Ao gerar o arquivo .sql, você está criando um arquivo que funciona para o seu adaptador de banco atual, não necessariamente para outros. Além disso, essa solução pode não ser recomendada se você não tem um grande problema com migrations lentas, especialmente porque o Rails não possui suporte oficial a esse tipo de otimização, e manter o histórico e a reversibilidade das migrations pode ser melhor na maioria dos casos.

A primeira ideia foi criar um arquivo .sql que fizesse o mesmo que as migrations e então carregá-lo. Pensamos em usar o structure.sql como solução, já que o schema.rb não reflete a criação de views, mas o structure.sql sim. Também precisávamos limitar quais migrations seriam transformadas em SQL puro, pois queríamos poder reverter facilmente as mais recentes se necessário, e garantir que todos os ambientes já tivessem rodado as migrations que seriam convertidas. Como percebemos que todos os ambientes já tinham rodado todas as migrations de 2024, decidimos manter todas até 2024 como baseline. Todas as migrations de 2025 em diante não seriam convertidas para SQL puro e continuariam sendo executadas normalmente.

No entanto, essa solução tinha um problema: o structure.sql não reflete registros criados ou atualizados durante as migrations. Embora seja esperado que os desenvolvedores sempre criem uma cópia dos updates/creates no seeds.rb quando necessário em novos ambientes, não havia garantia de que todo create/update que deveria ser portado estava refletido no seeds. Precisávamos então rastrear quais migrations tinham esse problema, mas tínhamos literalmente milhares de migrations e ler uma a uma não era viável nem seguro. Por isso, precisávamos rastrear automaticamente quais migrations faziam insert ou update, para pelo menos excluir da análise as que não criavam registros, que eram a maioria. Criamos então um initializer que só roda durante o processo de migration e loga quais migrations fazem esse tipo de operação. Note que esse initializer foi gerado por IA e não segue as melhores práticas, mas foi usado uma vez e removido do código, então tudo bem.

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

Com os nomes dessas migrations em mãos, reduzimos de milhares para apenas 70 o número de migrations que precisaríamos analisar manualmente. Foi um número bom para análise manual, então não avançamos mais com o script, mas uma forma mais automatizada seria capturar todos os SQLs executados e descartar os que afetaram zero linhas. Depois disso, movemos todo o código que estava em migrations mas funcionava como seed para o db/seeds.rb, e finalmente pudemos nos livrar das migrations antigas sem nos preocupar com os registros criados.

Após mapear o que deveria ser movido para o seeds, começamos o processo de obter o SQL pré-2025. O primeiro passo foi adicionar a seguinte linha ao application.rb:

config.active_record.schema_format = :sql

Depois, movemos todas as migrations pós-2024 para outra pasta (para não serem executadas), e reconstruímos o banco. Assim, obtivemos um arquivo SQL que refletia todas as migrations pré-2025 e podia ser facilmente carregado. Esse novo arquivo, chamado structure.sql, foi renomeado para structure_baseline.sql.
Depois disso, deletamos todas as migrations pré-2025 e devolvemos as pós-2024 para a pasta migrate.
Em seguida, geramos manualmente uma migration com timestamp do final de 2024, que seria executada após a última migration de 2024. Por exemplo, se a última migration tinha timestamp 20241230xxxxx, geraríamos uma com timestamp 20241231xxxx. Inserimos esse timestamp em todos os ambientes existentes editando diretamente a tabela schema_migrations. Assim, conseguimos ter uma migration que carrega todo o conteúdo do banco, mas nunca é executada em ambientes já existentes. Lembre-se: para funcionar, é preciso garantir que todos os ambientes já rodaram todas as migrations que estão sendo transformadas em SQL puro.
Com esses preparativos, criamos a seguinte migration:

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

Atenção: repare que o disable_ddl_transaction! faz a migration rodar sem transação no banco, o que pode ser um problema dependendo do seu contexto. Provavelmente você vai querer remover essa linha (ou pelo menos condicionar para rodar só em ambiente local). Optamos por usar porque acelera bastante o carregamento do .sql e só vai rodar em ambientes novos. Se algum problema acontecer durante o carregamento, basta recriar o banco, já que é um ambiente novo.
Depois disso, removemos a linha adicionada no application.rb, já que a mudança definitiva do schema.rb para structure.sql seria feita em outro pull request para revisão adequada. Você pode explicitar no application.rb que quer usar ruby ao invés de sql, se preferir:

config.active_record.schema_format = :ruby

Queríamos então rodar as migrations para gerar novamente o schema.rb. Porém, ao tentar carregar o structure_baseline.sql, o Rails tentava recriar as tabelas padrão — ar_internal_metadata e schema_migrations. Modificamos manualmente as linhas do structure.sql
CREATE TABLE public.ar_internal_metadata e CREATE TABLE public.schema_migrations para incluir um IF NOT EXISTS.
Também tivemos problemas com os índices dessas tabelas, e adicionamos condições para só criar os índices se eles não existirem.

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);

se tornou

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
$$;

Deletar essas criações de tabela e índices pode ser uma solução melhor, já que o Rails as cria automaticamente, mas não testamos essa abordagem. A partir daí, o db:migrate deve funcionar.
Você também pode precisar deletar a linha COMMENT ON EXTENSION pg_trgm IS 'text similarity measurement and index searching based on trigrams'; também caso esteja rodando com um usuário sem permissão para comentar extensões.

Com essas modificações, nosso comando db:migrate ficou muito mais rápido, mostrando resultados de cerca de 90% de otimização.

How we sped up our rails migration setup in 90%

Augusto Pagnossim Frigo — Wed, 03 Sep 2025 21:03:43 +0000

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.