Synchronization replication with the log-based method v7

In PostgreSQL 9.4, a feature was introduced called logical decoding (also called logical replication or changeset extraction). Logical decoding lets you extract data manipulation language (DML) changes from the write-ahead log segments (WAL files) in a readable format.

For information on logical decoding see the PostgreSQL core documentation.

With logical decoding, you can capture data changes to the publication tables without impacting the online transaction processing rate against these tables that occurs when using the trigger-based method. The trigger-based method results in firing row-level triggers whenever data changes occur and then inserting these data changes into shadow tables for temporary storage. It then applies the changes to the target databases.

Thus, extracting data changes using logical decoding can help improve database server throughput and replication latency.

However, the logical decoding interface streams changes for all tables in a given database, which can have a performance overhead associated with it. For example, if a database contains 100 tables, and you are interested in replicating only a small subset of these tables (for example, only 20 tables in a single publication), the logical decoding protocol streams changes for all 100 tables to the publication server. The publication server eventually filters out the changes for the irrelevant 80 tables. However, this results in network overhead caused by the additional changeset load that isn't required by the replication system.

Using logical decoding to extract changes from a publication database during EPRS synchronization replication is referred to as the log-based method.