Understanding Data Replication with InstantdbSync
What is live data event replication?
There are two mainstream ways to replicate data between databases:
Take a snapshot of the source database and duplicate the data into the target database. This is usually done in a batch or on-demand environment.
Capture changes as they happen in the source database and perform the exact same changes on the target database. This is usually done in a live data or transaction-oriented environment.
Live data updates are an evolution of the snapshot idea. You first capture an image of the source data and create target data. Then you watch the source data to see if there are any changes. You perform the same changes you found on the source database against the target database.
Why should you want to perform live data event replication?
Live data event replication is the most efficient way to move information as it changes from the source database to the target database.
When you first capture an image of the source database, and create the target database, the system must read and write every row of data in the entire database. From that point, live data event replication only accesses information in the original database that has been altered. If an organization has tens of millions of rows of data, only a very small fraction of the data typically changes during daily operations. Even the most efficient snapshot operations must still examine every row of data from the source every time they synchronize the source and target databases. Live data event replication consumes less computing resources than the other database replication alternatives.
Live data event replication data does not differ from reality in the same way snapshots of data differ.
If you take weekly snapshots of your database system to create your target database, you will have an excellent image of your current business situation in both the target and source databases right after the snapshot is taken. But as time passes, the source database starts to diverge from the last snapshot. Transactions applied to the source database will not show up in your aging target database until you take another snapshot. Eventually, the duplicated data loses any value, so you are forced to update the target database; usually by creating a new snapshot of the source data.
Live data event replication does not suffer from this defect. As transactions are performed on the source database, the information is immediately moved into the target database and the source and target databases stay current.
Companies may need multiple database formats for business reasons. A particular group may need to analyze some facet of the financial picture such as current inventory levels, or the status of accounts receivable, but the group may not know how to use the source database system. This problem is solved by having a live image of the source database system in a format that the group knows how to access.
Badly formed queries can negatively impact critical databases. Users may not be authorized to perform ad-hoc queries against the source database. Target databases containing current images of all or part of the source database can allow ad-hoc queries without any danger of impacting the performance of the source database.
Is live data event replication always the best solution?
Live data event replication is not appropriate in every situation. The method of choice (to make a snapshot or to use live data event replication) depends on the eventual use of the data.
There are times when it makes sense to use a snapshot of the source database. For example, you do not want the financial picture to give you new answers every time you query it for month-end reporting.
How does InstantdbSync perform live data event replication?
Step 1 - Initial State
To start live data event replication, you must take the source database and then create a snapshot that duplicates all of the data at a single point in time. In InstantdbSync, this step is called the "Initial State".
Performing an initial state operation moves a copy of the source database into the target database.
Step 2 - Queuing
After you have created the initial state, you will have an exact image of the database as it looked at a single point of time - the beginning of the "Initial State" step. But between the time when the initial state operation started and the time when it finished, it is possible that some updates have occurred against the source database. You don't want to lose these changes. The updates are stored in a message queue as they occur and at the end of the initial state, you apply the changes to the target database so it is current again. As new changes come in, you add them to the bottom of the message queue. You continue to use the message queue to update the target database. After a change has been applied to the target database, it is removed from the queue.
The message queue allows you to preserve the state of the target database, even if the target database is taken offline. You simply add new data changes to the message queue and when the target database is ready to be brought online, you restart the target server and update the target database with the stored changes until the queue is exhausted.
Step 3 - "ACID"
"ACID" stands for Atomicity, Consistency, Isolation, and Durability. For a database to be reliable, the system must preserve the "ACID" of the data transactions.
Atomicity means that a group of operations is performed as a unit. InstantdbSync processes the transactions in the exact time order in which they occurred and the target database process is designed to preserve the atomicity of the updates.
Consistency means that your database must be in a fully functional state when the transaction begins and when it ends. The target database system's transactions ensure the system retains database integrity.
Isolation means that the database changes happen as a group so that outside users of the system do not have to worry about the individual updates. Although moving money from one account to another account requires the money to be removed from the source account before it is added to the target account, an outside observer will see the accounts either in the original state or with both the addition and the subtraction. They will not see just the addition or just the subtraction. If the transaction in the target database does not succeed, InstantdbSync retains the transaction on the message queue and generates a problem notification message. The transaction is replayed when the problem is corrected. Partial transactions are not committed.
Durability guarantees (not just promises) that a transaction has been fully and correctly completed, and the state of the changed records will not be partly done or somehow undone. InstantdbSync transactions are durable because of the target database's relational properties; uncommitted transactions are retained in a durable format in the message queue until they are successfully completed.
Step 4 - Problem Resolution
Because the changes have been stored once the transaction completes, you know that the changes are permanent and cannot be lost due to distressing events such as power outage. However complications can occur in the live data event replication process. Two possible problems are:
The source database may be damaged by hardware failure and have to be restored from backup.
Resources needed to store and forward a transaction may not be available. The system detects this and similar problems and automatically performs an initial state (if required) to create a new snapshot at a feasible time.
Certain kinds of damage can occur in a way that the replication system won't be aware of the problem but the system users will be. For example, data can be accidentally deleted from a target table. Because InstantdbSync monitors only changes on the source database, InstantdbSync will not be aware of this damage. However, the system users can simply request a new initial state, and the target system will once again be synchronized with a true picture of the current data.