Why is there a 25 minute sync when there are no changes at the source?
Note: Using WAL replication method only.
The issue arises when there is no data on the WAL (Write Ahead Logging). This prevents Fivetran (the WAL reader) from knowing if there are changes, which prevents us from progressing the WAL. The connector has an internal 25-minute read timeout and will read for changes for this full timeout or until changes have been read.
This is a known limitation of Postgres. More info can be found at: Postgres Logical Replication and Idle Databases.
- Can the connector "read timeout" be reduced?
The downside of reducing the connector read timeout is that changes may be missed before they are read. This timeout value has been decided based on past cases of changes missed from the WAL read.
- Is there a solution to prevent this?
On the source database side, you can implement a heartbeat mechanism in that the same row is updated every 5 minutes (or less). This simulates a change on the DB and will allow the connector to progress the WAL and thus finish the extraction phase.
More info can be found in the Heartbeats for Heartbeats section of Postgres Logical Replication and Idle Databases.