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

Question

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

Environment

Connector: PostgreSQL

Answer

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

Cause

XMIN 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 XMIN method has the following disadvantages:

• Cannot replicate row deletions, so there is no way to tell which rows in the destination are no longer present in the source database. XMIN can't track row deletions because it relies on a hidden system column in PostgreSQL tables that is effectively a last_modified column. When a row is deleted, it doesn't appear as being recently modified because it no longer exists.
• 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 XMIN 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.