Alert: XMIN Frozen

Issue

The following alert appears in Fivetran:

XMIN frozen

The message data is also extracted by the Fivetran Platform 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:

1. 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.

2. 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.

3. 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 sync method. If you decide to switch, this will trigger a re-sync 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.