Why Is My PostgreSQL Connector Using Query-Based Update Method Not Syncing?

Question

Why is my connector using the Query-Based update method not syncing data?

Environment

Connector: PostgreSQL

Answer

If you are using Query-Based as your incremental sync method and the source database has a transaction that is taking a long time to commit, the connector stalls and waits for the transaction to complete before syncing the updates. This may appear as multiple syncs that extract no data, followed by a sync that extracts a lot of data.

You can use the following queries to troubleshoot the source database and look for ongoing transactions:

SELECT txid_current_snapshot(); --Returns xmin : xmax : in-progress-txids. 
SELECT pg_xact_status(<in-progress-txids>);
SELECT * FROM pg_replication_slots;
SELECT * FROM pg_stat_activity;
SELECT * FROM pg_prepared_xacts;
SELECT * FROM pg_prepared_statements;

The XMIN value in txid_current_snapshot() should change as transactions are committed. If this value is not changing, and updates are happening on the source database tables synced in the schema tab of the connector, show this to your database administrator to find why the XMIN value is not increasing for the committed transactions. Once this value starts to change, Fivetran can continue finding new data to sync.

This can help identify the oldest running transactions in your environment. Once these transactions complete or are terminated, the xmin horizon can advance, which allows incremental syncs to continue.