This is not my experience, it only happens rarely. I’d like to see an analysis of what causes schema changes that require nontrivial migrations.
On a relatively neat and well modelled DB, large migrations are usually when relationships change. E.g. One to many becomes a many to many.
Really the biggest hurdle is managing the change control to ensure it aligns with you application. But that's a big problem with NoSQL DB deployments too.
At this point I don't even want to hear what kind of crazy magic and "weird default and fallback" behavior the schema less NoSQL crowd employs. My pessimistic take is they just expose the DB onto GraphQL and make it front ends problem.
I'm gonna take a wild guess here that you have never worked in unfamiliar domains (like lets say deep cargo shiping or subpremium loans) where your so called subject matter experts provided by client werent the sharpest people you could hope for and actually did not understand what they where doing for most of the time?
Because I on the other hand am very familiar with such projects and doing schema overhaul third time in a row for production system is bread and butter for me.
Schemaless systems is the only reason I'm still developer and not lumberjack.
Same for the database that was about 8-9 years old at my last company. Migrations are fine. It's the relationship-changing that is painful.