Design pattern to sync local databases with the cloud [closed]

Question

I would like to find the best way to solve the following problem:

I need a database for a software. The software needs to use the data to populate the UI and allow the user to interact with them (edit, add, delete etc). There are multiple instances of that software (desktop, mobile, another desktop etc) all linked to the same account with the same data.

I need a way to have up-to-date databases between the instances. One way is to have the data on the cloud, and then have the software pull/push data to it and maintain their own local database.

Is there a convention in the industry of how to do this kind of things? I am using AWS and I consider either DynamoDB or traditional SQL databases.

Answer 1

This is called a "distributed database", and it is extremely hard to do well. For almost all cases you should have one database in the cloud and have all the apps use it in the normal way through an API.

If you absolutely have to support disconnected operation, you may need a distributed database. Such as if one of your clients goes off-grid for a while, needs to make changes, and then reconnect.

But then you face the central problem: what if two clients go off-grid and make different changes to the same data at the same time?

The state of the art in techniques for dealing with this is the conflict-free replicated data type.

Rolling your own technology for this will probably add a year to your project and give you at least one significant data loss incident.