keeping databases in sync (after write/update) across regions/zones

Question

I have to write a webservice in php to serve at three different zones/(cities or countries). Each zone will have its own machine to run this web service instance behind every webservice is a database which is exact clone/copy in each region, web service serves the clients with data from db. Main reason for multiples instances of web service is to distribute client load.

The clients can make read and write calls via web service APIs. Write calls will modify the database for that instance but this change has to be applied as soon as possible to all databases in other zones also as all the databases in each zone are clones and exact copies, so changes in one db must be synced in all the databases in other zones.

I presume the write calls must go to some kind of master server which coordinates among all the web services etc. But I am sure this pattern is quite common and some solution is already out there.

Please advise if there is any database or application level technique which would keep the databases in sync when there are write calls so that modification or addition is reflected in all instances of db ? I can choose the database of my choice but primary choice would be mysql server or postgres, but can change to other database which can solve this issue.

Answer 1

You're right, this pattern is quite common and there is a name for it - Synchronous Master-Master replication. Most modern RDBMS support it:

• PosgreSQL supports it thru pg_cluster https://wiki.postgresql.org/wiki/PgCluster
• MySQL https://www.howtoforge.com/mysql_master_master_replication

But before implementing it straight away I'd recommend reading more about different types of replication, their pros and cons:

• https://wiki.postgresql.org/wiki/Replication,_Clustering,_and_Connection_Pooling
• https://dev.mysql.com/doc/refman/8.0/en/replication.html

Synchronous Master-Master replication will be quite slow, especially in a multi-zone scenario, so you might consider other techniques:

• Asynchronous replication
• Sharding/Partitioning
• A mix of sharding and replication

There is a very good book on different distributed techniques(including sharding and replication) - "Designing Data Intensive Applications" by Martin Kleppmann.

Answer 2

Replication techniques are definitely worth looking at, but there can be a certain amount of technical overhead and cost to replication. I work for a company called Redactics (https://www.redactics.com), and we came up with a simpler solution that is sort of a near realtime replication based on delta updates using a pure SQL approach.

There are certainly pros and cons to both approaches, I'm not trying to push Redactics hard if this is not the most appropriate solution for your needs, but Redactics simply tracks the most recent primary keys and uses modification timestamps to find new and changed records, and then copies them over. You can run the sync pretty often without a lot of load since it is just a delta update. Obviously any workflow can break, but repairing broken replication can be tricky, so we like this approach and running these sync workflows within your own infrastructure.