Can I switch from XMIN to WAL without problems or resync?
When you switch from XMIN to WAL (or vice-versa), a historical sync is required for the incremental sync to work.
XMIN and WAL 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_modifiedcolumn. 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, we only use 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
For more details, please refer to our Documentation.