This article is applicable to the following database integrations:
Symptoms
Extremely slow, intermittent replication from a PostgreSQL read replica. It will look like data is “trickling” in.
Cause
A read replica, or follower, is a database that runs a copy of an active - or master - database.
If a PostgreSQL read replica is running in hot standby mode, the default values for some of its settings may prevent Stitch from successfully completing queries. These settings define the amount of time the replica is permitted to get behind the production database before canceling the conflicting queries.
If Stitch’s queries take too long to run - which can happen when tables are extraordinarily wide or when the master attempts to update the read replica - Stitch’s queries will be interrupted. This results in a slow trickle of replicated data.
To learn more about how PostgreSQL defines query conflicts, check out the official documentation.
Solution
By default, the max_standby_streaming_delay and max_standby_archive_delay settings are set to 30 seconds.
To ensure Stitch’s queries are successful, increase these settings to 8-12 hours, especially for large historical jobs. You can also increase these values in smaller increments until you find the perfect middle ground.
After the initial historical replication completes, you can typically decrease these settings again.
Questions? Feedback?
Did this article help? If you have questions or feedback, feel free to submit a pull request with your suggestions, open an issue on GitHub, or reach out to us.