Preventing ActiveRecord migration from making huge changes in db/structure.sql

Question

When running the ActiveRecord migration below on the test (not production) database, large changes in db/structure.sql appear. This is reproducible. The changes are:

• A small number of the expected changes based on the migration code (as desired).
• A large number of unexpected changes, which are unrelated to the migration code (not desired). All these unexpected changes rearrange the order of the tables, views and materialized views that are not mentioned in the SQL code in the migration. These rearranged tables, views and materialized views are not associated (e.g., by dependencies) with the items in the SQL code in the migration.

What causes these large unrelated differences, and, most importantly, how to prevent these large unrelated differences?

Could they be related to the mismatch between the postgresql versions on the production database server and on the machine where the migrations are executed on the test database?

These unrelated changes make git diff output much less useful. Same goes for diff results on GitHub.

In this tiny minimal example below, just a few tables are rearranged. But in the real life (larger) migration it changes the order of dozens of tables, views and matviews - a huge change.

class FixFooBarBaz < ActiveRecord::Migration[5.2]

  def up
    sql = <<~SQL
                  DROP VIEW master_foo;
                  DROP VIEW IF EXISTS bar1;
                  CREATE OR REPLACE VIEW bar1 AS
                  SELECT * FROM baz1
                  UNION ALL
                  SELECT * FROM baz2;
                  CREATE OR REPLACE VIEW master_foo AS
                  SELECT
                      *,
                      'bar1' AS bar_type
                  FROM
                      bar1
                  UNION ALL
                  ...
    SQL

    execute sql
  end

  def down
    sql = <<~SQL
                  DROP VIEW IF EXISTS master_foo;
                  DROP VIEW IF EXISTS bar1;
                  ...
    SQL

    execute sql
  end
end 

Hardware/software used:

Machine where migrations are executed:

MacBookPro, Apple M1 Max, macOS Sonoma 14.2.1
ruby 2.7.3p183 (2021-04-05 revision 6847ee089d) [arm64-darwin21]
15.3 psql (PostgreSQL) 15.3 (Homebrew)

Production database server:

psql (14.9 (Homebrew), server 13.3 (Ubuntu 13.3-1.pgdg20.04+1))

Answer 1

Yes, this can be caused due to different versions of the database engine you're using between different machines and/or configurations (like installed plugins). Example: After cloning the repo and recreating the database with the structure.sql from scratch, now some unexpected changes appears in this file: https://github.com/coopdevs/timeoverflow/pull/713/files.

Other possible cause is: Rails (Active Record) internal changes for better readability, consistency or any other reasons. Example: https://github.com/rails/rails/issues/44571.

Remember that differences in the order of tables or views in the structure.sql file (the same applies for the schema.rb approach) generally don't impact the functionality of your Rails application. But be careful, if you see other kind of changes, review them carefully (for example, in MySQL I've seen changes in the collation or the charset, which can cause actual weird problems in your app).

Regarding the "order", this can be forced by using the :before and :after options in your migrations. Example:

create_table :my_table, before: :my_other_table

Those options also work for columns:

add_column :my_table, :my_new_attribute, :integer, after: :my_other_column