This is definitely a valid concern. My plan for building migrations on top of this is to have older migrations automatically be deleted as they become unused. Preferably you wouldn't actually keep using the old schemas but rather keep them as you roll out changes to your application servers. Migrations could then be deleted (in reverse order) based on some condition, for example time since the schema was created or time since last query.
But in order to support the "add column" migration you have to add it to the base schema, right? It has to be materialized somewhere.
Now, when you remove a column, you just create a new view without it being available. But the column in the base schema must stay because: you support the old schema, and because you want to avoid the operational complexity of physically removing it, right?
Now, you can obviously split a table into a list of tables each storing the pk and some column value. But you have just invented columnar storage in a row-based rdbms :D
A different approach is to have scheduled maintenance window, for example quarterly or every 6 month when you materialize all the changes at once. The amortized operational cost is way lower :)
I think you've misunderstood - the issue is how to keep two views in place - one supporting current clients, until the last one disconnects, and another supporting new clients.
Until you have no current clients left, you can't delete the old view or remove underlying columns that that old view requires. Once they have, you can execute that phase.
