Why Does a relfilenode Change Trigger a Historical Re-Sync?

Question

Why does a change to my PostgreSQL table's relfilenode trigger a historical re-sync when my connection uses the Query-Based sync method, even when the table has a primary key?

Environment

• Connector: PostgreSQL
• Incremental sync method: Query-Based

Answer

When your connection uses the Query-Based sync method, Fivetran detects row-level changes using the xmin and ctid system columns. When a table's relfilenode changes, PostgreSQL rewrites the table file on disk. This can happen after operations such as VACUUM FULL, CLUSTER, or certain ALTER TABLE operations.

During the rewrite, PostgreSQL assigns new xmin and ctid values to every row. As a result, Fivetran treats all rows as changed and triggers a historical table re-sync.

A primary key doesn't prevent this behavior. Primary keys help us to identify rows, but they don't determine how we detect changes in Query-Based replication. After a table rewrite, we can't use the changed xmin and ctid values to distinguish truly modified rows from rewritten rows.

To reduce historical re-syncs, identify and limit operations that change relfilenode, such as VACUUM FULL, CLUSTER, or table-rewriting ALTER TABLE statements. When these operations are unavoidable, schedule them during maintenance windows.

You can also consider configuring your connection to use the logical replication method, which tracks changes from the transaction log and isn't affected by table rewrites in the same way.

To change an existing connection's update method in Fivetran, do the following:

1. Go to your PostgreSQL connection page.
2. Select the Settings tab.
3. Click Edit connection.
4. In the left menu, click Incremental sync.
5. Update the Incremental sync method.
6. Click Save & Test.