Comment by __float
My experience has not been so smooth. Migrations are reasonable, but they're not free and "always keeps backups" sounds like you'd tolerate downtime more than I would.
Even in the best case (e.g. basic column addition), the migration itself can be "noisy neighbors" for other queries. It can cause pressure on downstream systems consuming CDC (and maybe some of those run queries too, and now your load is even higher).
Anything with state is going to be hard to get right. Couple sticky schema changes to that state and you’re looking at a lot of potential ways of things can go wrong. Downs being unnecessarily destructive, rollback corrupts data, migrations applied in wrong order. Everyone tangential to working with any sort of migrations system has a war story (or a few) of the creative way that the state got wrecked.
Here’s one of mine: Postgres change applied fine in unit and integration and dev but not prod because the shape of the data (enum) did not conform to the new constraint.
Another would be a monorepo that had 5-6 services that talk across db’s to each other caused dev to apply the wrong migration to the wrong HEAD, mixing up the db’s. That was a fun one to sort out