Can I Switch from Query-Based to Logical Replication without Problems or Re-sync?

Question

Can I switch from Query-Based to logical replication without problems or re-sync?

Environment

Connector: PostgreSQL

Answer

When you switch from Query-Based to logical replication (or vice versa), a historical sync is required for the incremental sync to work.

Cause

Query-Based and logical replication are two different methods used for incremental sync by Fivetran and are not compatible with each other. The hidden PostgreSQL system column xmin can be used to select only the new or changed rows since the last update.

The Query-Based method requires a full table scan to detect updated rows, which can slow down updates and cause significant processing overhead on your PostgreSQL server.

As a result, Fivetran only uses the Query-Based method if logical replication is not an option for you.

Logical replication is based on logical decoding of the PostgreSQL write-ahead log (WAL). We recommend this method for incremental updates because it:

• Minimizes processing overhead on your PostgreSQL server.
• Replicates row deletions for tables with primary keys.