Question:
You are experiencing a ‘XMIN got frozen’ alert in the Fivetran UI or as message data extracted by the Fivetran Log Connector. This is caused by PostgreSQL's internal vacuuming process because too much time has passed since the last successful sync.
Environment:
PostgreSQL
Answer:
Freezing can happen when either:
- 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.
- Somebody ran `VACUUM FREEZE` 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)
XMIN is used as the basis for recognizing changed rows. Incremental syncs cannot not be completed when XMIN becomes frozen. Therefore, a full re-sync had been initiated on the affected tables.
The best way to preemptively solve this issue is switching from XMIN to Logical Replication as the primary incremental update mechanism. If you do 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'.