MVCC – the part of PostgreSQL we hate the most (2023)

My problem with PG is it just doesn't seem to help much with my situation. I want to write apps that work offline and sync data across devices using the 'cloud'. I think that means Sqlite on the client, but ?? for the server. I have yet to find a good book explaining techniques for this kind of replication/syncing.

CouchDB?

At risk of saying something you know, CRDT’s may be a good fit for your use case.

The problem is of course making changes offline that the user assumes are permanent but then later, when sync time comes, then turn out to conflict with changes made in the meantime. So changes can’t be made permanent. Either that requires difficult UX to reconcile or something that always will give you something consistent, like a crdt.

PouchDB

I’ve never used this, but I know people who work here [0]. Might be useful for you?

[0]: https://ditto.live/

Looks interesting, but I don't see anything for how to build it into an iOS or Android app. https://docs.couchdb.org/en/stable/install/index.html

Well usually you use a datastore on a server as a master, then you pull/push based on timestamps.

Firebase and cognito/appsync work this way, basically.

You can use any data store you want on the server to do that. You could theoretically push a local sqlite db up to s3 as a sync mechanism, I suppose, if you do the locking correctly.

There was a spinoff a while ago when they merged with Membase, I haven't used Couch in a few years but maybe this will work: https://docs.couchbase.com/couchbase-lite/current/swift/quic...