I need to synchronize tables of data between two different systems. This is a multi-master setup; data can get changed in either system. After a synchronization runs I'd like the data in each table to be the same.
I'm having a tough time coming up with an algorithm for sending only incremental changes from one system to another. Here's my current algorithm:
- In the local system make a list of the ids of the records that have changed since the last run.
- Import all changed records from the remote system, and update the "modified" timestamp of each record locally.
- Export to the remote system the list of records with the ids from step 1.
This works, but gets cumbersome when there are potentially millions of records to exchange. The list of ids can't be stored in memory without possible out-of-memory errors.
The reason I have to make the list of ids is that if I run an import first, the "modified" timestamp column will get updated locally, and then I'll lose the knowledge of which records have been changed since the last run and need to be exported.
Another way to do it would be to mark all of the records that need to be exported before the run starts. The problem with that is that it means setting a flag in millions of records, which is going to be very slow. (I'm using Postgres, which does not support in-place updates.)
Yet another way to do it would be to refrain from updating the modified timestamp when you do the import in step #2. The problem there is that I really need that timestamp updated if the record has changed, because I am synchronizing the local table against potentially more than one remote system.
Obviously, the best way to do this would be to use some kind of Change Data Capture stream that produces a log of the changes each table has seen, but that is not available in this situation.
(Let's leave aside conflict resolution for the moment. For each column in the table I can designate the "owner" of the column, either system A or system B, so that the owner can always overwrite the column without a conflict.)
Question: is there a better algorithm for this? What are the common solutions to this problem?
In response to the comments: I'm synchronizing a local CRM, with a list of contacts, with various external apps that have APIs. For example, my local list of sales leads needs to get exported to Mailchimp, and any leads that come in through Mailchimp (say, through a web form) need to get added back to the CRM. Incremental changes in the CRM (say, a change to name, address, or email) would need to get pushed to Mailchimp, and incremental email status changes (say, bounces or unsubscribes) would need to get sync'd back to the CRM.
Also -- the CRM will have to get sync'd with more than one external service. Contacts will have to get sent to Facebook for advertising, or potentially some call center app.
I can make the simplifying assumption that certain apps "own" certain fields. For example, the CRM can own name and address, and Mailchimp can own email_unsubscribed or email_bounced. Field-level conflicts can be resolved by letting the owning app get its way.
Yes, I understand that multi-master setups are difficult, but for this application it's really not optional.