Question:

The following alert appears in Fivetran: XMIN frozen. The message data is also extracted by the Fivetran Log connector.

Environment:

Connector: PostgreSQL

Cause:

This is caused by PostgreSQL's internal vacuuming process because too much time has passed since the last successful sync. Freezing can happen when:

• Your autovacuum_freeze_max_age is too low.

PostgreSQL's transaction control mechanism assigns a transaction ID to every row that's modified in the database. These IDs control the visibility of that row to other concurrent transactions.

When the age of the oldest transaction ID in any row in a table exceeds the value set for autovacuum_freeze_max_age (default value is 200 million transactions), then an autovacuum runs on the table to reclaim old transaction IDs and prevent wraparound data loss.

• vacuum freeze was run on the affected tables.

Freezing the transaction ID for all pages (regardless of if they’ve been modified or not) means that all current rows will be classified as old for all new transactions.

Freezing marks a table as not needing any autovacuum maintenance. The next sync will unfreeze it.

• You have a very high rate of transactions (large volume of data in your database).

Resolution

XMIN is used as the basis for recognizing changed rows. Incremental syncs cannot be completed when XMIN becomes frozen. As a result, a full re-sync is initiated on the affected tables.

The best way to preemptively solve this issue is to switch from XMIN to Logical Replication as the primary incremental update mechanism. If you decide to switch, this will trigger a resync of the connector.

Alternatively, you can try to increase the value of autovacuum_freeze_max_age in your server config and avoid running vacuum freeze.

There is a third option where (depending on which pricing tier you are on) you can increase the sync frequency. This would have the same effect as setting a higher value for autovacuum_freeze_max_age.