4 ms·

Get all entities whose updated_at is larger than the max updated_at in your local database.

BatteryMountain 5y ago
Then what if the schema changed? Columns renamed, dropped or new columns added that is non-nullable, or new foreign keys with non-null constraints? The only place I've seen "offline sync" work is where MongoDB was used server-side for the sync, which then gets synced with postgres - in this case the models are forced to be the same. But then you still have the problem of which side to sync first: postgres to mongo or mongo to postgres? What if a conflict arises? And having a device turned on after being off for a month WILL cause issues. I don't think there is a real solution here yet. At least not one that is perfectly automatable.
- renke1 5y ago
  At least for my pet project I am considering to add a version field to my documents so that I can migrate the document to the most recent version before persisting in to the server's database. When sending documents to the client you may have to force it to update to the latest application version though.
  - BatteryMountain 5y ago
    True. For simple apps with not much logic, you can get away with different front-ends/offline-syncs adding their own rows, and then only respecting the latest row in the main db, usually based on timestamp. But if you have something more complex where multiple workflows get triggered as side effects, then you may run into trouble when the latest row is in fact not the truth of the reality that we wanted, so there is still some risk ending up with states or workflows based on a false reality. This can become a nightmare if you a have few scheduled events/tasks/crons that does things periodically, thus the only way to mitigate that is to fully embrace eventual consistency and idempotency, and the "easiest" way to get there is to embrace the actor model paradigm (see erlang, akka/akka.net, Orleans framework, F# mailboxes, go-routines, etc). Point being, comparing two versions of applications against each other is not enough - you may need to version your data too and use timestamps to make a final decision on which version is truth. You may also need to set or build a tolerance system to say it will only sync "old" data if within x amount of days, lets say less than 7 days old. And so on.
renke1 5y ago
That's a really simple solution. It doesn't work for all kinds of data though. For some data you might want to have a more elaborate conflict resolution (e.g. manual merge or using smart data structures like CRDT).
- tehbeard 5y ago
  I've briefly looked into CRDTs, but I have to ask, beyond a toy-implementation of a TODO list.... how much do they balloon the size of the data stored? Particularly for a complex document like a report with hundreds of fields and arbitrary sized lists for comments/observations?
  - renke1 5y ago
    I think the most common CRDT libraries try hard to reduce the overhead of CRDTs. I am not expert but I could imagine that you could also remove some of the historical data if you know that all clients are reasonably up to date and if they aren't they have to discard their changes that are too old.
- samwillis 5y ago
  Earlier this year I experimented [1][2] with combining CRDTs using the incredible yJS[3] with PouchDB. It worked really well, completely magic syncing with full automatic handling of sync conflicts! (Although I have concerns about the sustainability of PouchDB, see my other comment[4]) 1: https://gist.github.com/samwillis/1465da23194d1ad480a5548458864077 https://gist.github.com/samwillis/1465da23194d1ad480a5548458... 2: https://discuss.yjs.dev/t/distributed-offline-editing-with-couch-pouchdb/340/7 https://discuss.yjs.dev/t/distributed-offline-editing-with-c... 3: https://yjs.dev https://yjs.dev 4: https://news.ycombinator.com/item?id=28690886 https://news.ycombinator.com/item?id=28690886
  - renke1 5y ago
    I think my main problem with PouchDB and by extension CouchDB was that it seemed hard to add validation in the backend (including authentication/authorization). I remember having to build some kind of proxy that hooks into the CouchDB protocol to deny certain requests. I am pretty sure that's solved by now (or I was just asking the wrong questions back then).

Then what if the schema changed? Columns renamed, dropped or new columns added that is non-nullable, or new foreign keys with non-null constraints? The only place I've seen "offline sync" work is where MongoDB was used server-side for the sync, which then gets synced with postgres - in this case the models are forced to be the same. But then you still have the problem of which side to sync first: postgres to mongo or mongo to postgres? What if a conflict arises? And having a device turned on after being off for a month WILL cause issues. I don't think there is a real solution here yet. At least not one that is perfectly automatable.

renke1 5y ago
At least for my pet project I am considering to add a version field to my documents so that I can migrate the document to the most recent version before persisting in to the server's database. When sending documents to the client you may have to force it to update to the latest application version though.
- BatteryMountain 5y ago
  True. For simple apps with not much logic, you can get away with different front-ends/offline-syncs adding their own rows, and then only respecting the latest row in the main db, usually based on timestamp. But if you have something more complex where multiple workflows get triggered as side effects, then you may run into trouble when the latest row is in fact not the truth of the reality that we wanted, so there is still some risk ending up with states or workflows based on a false reality. This can become a nightmare if you a have few scheduled events/tasks/crons that does things periodically, thus the only way to mitigate that is to fully embrace eventual consistency and idempotency, and the "easiest" way to get there is to embrace the actor model paradigm (see erlang, akka/akka.net, Orleans framework, F# mailboxes, go-routines, etc). Point being, comparing two versions of applications against each other is not enough - you may need to version your data too and use timestamps to make a final decision on which version is truth. You may also need to set or build a tolerance system to say it will only sync "old" data if within x amount of days, lets say less than 7 days old. And so on.

renke1 5y ago

That's a really simple solution. It doesn't work for all kinds of data though. For some data you might want to have a more elaborate conflict resolution (e.g. manual merge or using smart data structures like CRDT).

tehbeard 5y ago
I've briefly looked into CRDTs, but I have to ask, beyond a toy-implementation of a TODO list.... how much do they balloon the size of the data stored? Particularly for a complex document like a report with hundreds of fields and arbitrary sized lists for comments/observations?
- renke1 5y ago
  I think the most common CRDT libraries try hard to reduce the overhead of CRDTs. I am not expert but I could imagine that you could also remove some of the historical data if you know that all clients are reasonably up to date and if they aren't they have to discard their changes that are too old.
samwillis 5y ago
Earlier this year I experimented [1][2] with combining CRDTs using the incredible yJS[3] with PouchDB. It worked really well, completely magic syncing with full automatic handling of sync conflicts! (Although I have concerns about the sustainability of PouchDB, see my other comment[4]) 1: https://gist.github.com/samwillis/1465da23194d1ad480a5548458864077 https://gist.github.com/samwillis/1465da23194d1ad480a5548458... 2: https://discuss.yjs.dev/t/distributed-offline-editing-with-couch-pouchdb/340/7 https://discuss.yjs.dev/t/distributed-offline-editing-with-c... 3: https://yjs.dev https://yjs.dev 4: https://news.ycombinator.com/item?id=28690886 https://news.ycombinator.com/item?id=28690886
- renke1 5y ago
  I think my main problem with PouchDB and by extension CouchDB was that it seemed hard to add validation in the backend (including authentication/authorization). I remember having to build some kind of proxy that hooks into the CouchDB protocol to deny certain requests. I am pretty sure that's solved by now (or I was just asking the wrong questions back then).