Comment by throwaway892238 | Hacker News Reader

Comment by throwaway892238 | Hacker News Reader

Parent Full thread

throwaway892238

Use Case 1. Migrations.

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.

For migrations, how would you want neon (or anything else) to handle all the writes that happened since the commit you want to return to?

It would need to revert only the changes in that commit while preserving all new changes afterwards. I don't have the foggiest how to make that happen, that's up to the ninjas at neon!

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

This is a tricky problem to solve. Reverting to the commit - that's easy and we have all the infrastructure already to allow you to do that. The tricky part is

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

This would be the killer 2050 alien feature. Git is a really good mental model on itself. Git + Neon = you invent the future!

It would be a killer feature, but the thing is that Git has a conflict resolution feature that puts the working tree in an invalid state until the user has manually fixed certain issues, so that you cannot commit until you manually fix the files and mark those as such.

Doing so for a database seems less desirable from an availability perspective, especially with high-throughput databases.

I've been wondering if it makes sense for databases to start offering CRDTs natively, so that you can perform merges that are algorithmically conflict-free.

CRDTs are pretty hard to implement at the Neon storage level - pages don’t know what’s written on them. I don’t know of a general purpose database that supports CRDTs. It would be cool though!

In a sense, a CRTD is a database. But the 'conflict-free' is only because it defines semantics for conflicting operations.

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.

But the manual intervention of conflict resolution in a Git repo is done in a working tree. The named branch's HEAD remains the same until the conflict has been resolved and the commit is merged/pushed. So nobody sees the conflict at all, they only ever see either the old code, or the merged resolved code.

Reply on news.ycombinator.com