PostgreSQL Log Based Replication

PostgreSQL Log Based Replication

In the past, the only method of capturing data changes in the PostgreSQL database by SymmetricDS was to install triggers on each application table that was configured to synchronize to a target database. One trigger would be installed for each Data Manipulation Language (DML) statement type, including Insert, Update, and Delete. The trigger would gather the old data for deletes and updates, and would gather the new data for updates and inserts. Then this data would be written to another table (the data change table) for further processing (routing to the correct target nodes, extraction of the data, and delivery of the data to the target nodes). This implementation of capturing data changes in this way put the functionality of the execution of the trigger in line with the application that was changing the data, and the capturing of the data became part of the application database transaction.

Most of the time, the overhead of capturing data changes in this way does not slow down the application to an unacceptable level. But sometimes the execution of the trigger to capture the data and write to the data change table can slow down the application that was changing the data to a level that causes slow response times of user interaction with the application or slow response times of the batch processes that are executing data changes. 

PostgreSQL Transaction Mining

PostgreSQL Transaction Mining is now available as another option to capture data changes in the PostgreSQL database for SymmetricDS synchronization, as of version 3.13. Instead of using installed triggers to write the data changes to the data capture table, SymmetricDS uses the PostgreSQL Logical Decoding feature that was introduced in PostgreSQL Version 9.4 to retrieve data changes that occur in the PostgreSQL database and writes these data changes to the data capture table. Using this method, all of the SymmetricDS configurations can be done outside of the application database and uses only database-provided functionality to capture changes.

Because the SymmetricDS configuration and the execution of triggers have been removed from the source application database, this eliminates any possibility of SymmetricDS functionality slowing down applications. It also eliminates the possibility of the vendor of this product voiding the warranty and stopping support because of objects being installed in the database that is not a part of the vendor’s product.