Everything went smoothy, here is how I did it to squash everything until 2021.

MIX_ENV=test mix ecto.drop
MIX_ENV=test mix ecto.create
MIX_ENV=test mix ecto.migrate --to 20211223095738 # last 2021 migration
MIX_ENV=test mix ecto.dump -d priv/repo/schema-squash.sql
mkdir priv/repo/migrations_archive
for i in {2016..2021}; do mv repo/migrations/$i* priv/repo/migrations_archive; done
mix ecto.setup

And here is my mix alias in mix.exs:

defp aliases do
  [
     ...,
     "ecto.setup": [
        "ecto.create",
        "ecto.load -d priv/repo/schema-squash.sql -f --skip-if-loaded",
        "ecto.migrate"
      ]
  ]
end

EDITED after @LostKobrakai hint

We have come up with the following strategy to deal with squashing migrations on a periodic basis.

1. Run mix ecto.dump or the equivalent database command
2. Rename the generated structure.sql file to something the better communicates that this represents a snapshot of the database structure at a certain point in time, e.g. snapshot-2023-11-10.sql
3. Remove the old migrations (celebrate if you wish)
4. Create a new migration that references the dump file from above. This helps avoid frustration when developers unfamiliar with the process have to run yet-another-mix-command to bootstrap the database. They may run into problems getting the psql CLI installed, for example. You can write a simple migration that parses the dump file like this:

defmodule MyApp.LoadStructureSqlMigration do
  use Ecto.Migration

  require Logger

  def change do
    :core
    |> :code.priv_dir()
    |> Path.join("snapshot-2023-11-10.sql")
    |> File.read!()
    |> String.split(";\n")
    |> Enum.each(fn statement ->
      execute(statement)
    end)
  end
end

I have not tried this, but you can maybe dump the ecto schema using mix ecto.dump and load it using mix ecto.load and run new migrations from then onwards?

You might find this blog post interesting: https://ulisses.dev/elixir/2019/05/13/how-to-create-a-ecto-setup-pipeline-with-ecto-3-1-2.html

The reason is mostly what I would dub “housekeeping”. I’m remembering one repo in particular I worked on that had so many migrations that I had to remember not to open that folder because it had so many files it could crash the IDE. Another more common scenario arises from sloppy migrations (I’ve been guilty!) that worked for the immediate updates but somehow broke the ability to replay all the migrations from start to finish.

If historical transparency is required, I’m happy to leave that to Git. I just want to be able to reset things to a simpler representation.