I think we need more details to answer this question. Can you describe your workflow with more details? Maybe even with some code examples ?
Everybody today knows that if you're gonna change your database [schema], you need a system to migrate the DDL changes intelligently so you don't break something, and then run that in lower environments to test, then promote it up to higher envs and run it, then deploy your apps that use the changes. But of course it may be impossible to revert those changes after that point, requiring an entire database snapshot restore. So not only are there serious operational concerns, making any operations around this time-consuming and frought with peril, but you need to set up a migration solution (language-specific or framework-specific or agnostic) and make sure you architect your application to only make changes in a specific way. (all of this, by the way, is only necessary because the database is one big mutable state machine)
...whereas if it worked more like version control, you could make any change, commit it, and get a commit ID. If that causes problems, if you could just `cloudpg revert $change_id`, then there would be no need to carefully architect the app, changes wouldn't be fraught with peril, and we could be more agile with database-driven design. The database would obviously need to be intelligent enough to figure out how to revert any change, which is why this has to be a database-specific feature and not just a git revert.
Use Case 2. Merging Changes
This sort of follows on the above (making changes more agile). If you have branches, and 4 different devs are working on 4 different database changes, how do you merge and deploy those all safely, and handle reversions safely? Well if all database changes had versions, and we could diff the changes between versions, then we could treat the database like a Git repo and merge/rebase all the changes to the database together at the same time as the code. Again, no need to go back and refactor migration scripts or the app design, because the database is essentially just version-controlled code.
The same things would apply to upgrading/downgrading database versions, bringing up or restoring new servers, possibly even making replication easier, maybe other things we haven't thought of yet.
There would probably need to be a fallback mechanism if, for example, a new column was created and new data was entered into it, and then the revert removes the column. Probably it could keep pointers to such things ("there is a database D with a table T with a column C and rows [a,b,c,d]"), so that if the change is re-reverted later, an extra merge instruction could pop the reference back into place like nothing happened. Somebody with an actual CS background must have better ideas than me :)
* Make a schema change
* push into prod
* accumulate some new data
* revert just the schema change
In our discussions we call this separating schema and data and allow you to have different schemas on the same data.
It's tricky to do in Neon due to the fact that storage knows nothing about schemas. It stores page with no idea what's on them.
But we have some ideas how to do this with logical replication where we will run a transform on top of logical replication stream to keep two branches in sync. Not this year though.
Doing so for a database seems less desirable from an availability perspective, especially with high-throughput databases.
But, because SQL has conflict resolution by cancellation of one of the two conflicting modifications, I don't think that it is reasonable (or even possible) to merge 2 divergent databases in a single way that always conforms to the needs of the developer and/or application.