Adding additional columns has always been trivial. What is not is the 98% of other things migrations do. Managing the version of the schema, applying ups in order, executing downs correctly, handling fk references. It’s not necessarily the fault of the migrations frameworks themselves, of which many exist in varying degrees of quality, but rather that the underlying problem of trying to morph a schema that is dependent on the underlying shape of the data is often a difficult problem armed with many footguns.

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