If you connected a PostgreSQL read replica as a database integration and are experiencing extremely slow replication, the root cause may be the database’s standby settings.

Default Standby Settings & Stitch Queries

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 Postgres defines query conflicts, check out their documentation.

Next Steps

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 syncs. You can also increase these values in smaller increments until you find the perfect middle ground.

After the initial historical sync 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.