Why is there a delay in data ingestion for my Pipeline using a PostgreSQL read replica?

When your Pipeline is configured with a PostgreSQL read replica, Hevo relies on the replica instance to capture incremental changes made on the primary database instance. If the replica is unable to receive and apply the latest changes from the primary database, replication lag increases. As a result, the replica may not contain the most recent data, and Hevo cannot access those updates immediately. This can lead to delays in data ingestion.

You can verify this behavior by checking whether recent updates in the Source are reflected in the Destination. If the updates are not visible, it means the read replica has not yet received or applied those updates from the primary database.

Potential Causes of Replication Lag

• Long-running transactions on the primary database
  If a transaction is running on the primary database instance for a long time, PostgreSQL must retain the older Write-Ahead Log (WAL) records required by that transaction. As these records cannot be removed until the transaction completes, newer WAL records may not be made immediately available for replication. This can increase replication lag.

  Long-running operations can include:

  • Bulk data updates or deletes

  • Database maintenance tasks

  • Reporting or analytics queries

  • Custom scripts that keep transactions open

• Query conflicts on the read replica
  Queries running on the read replica can sometimes conflict with changes coming from the primary database. For example, conflicts can occur when:

  • A query accesses a table that is being dropped or updated on the primary database.

  • A read query holds locks on rows that the read replica needs to update or delete while applying changes from the primary database.

  When such conflicts occur, replication may be delayed while PostgreSQL resolves them.

Ingestion Behavior when Replication Lag Exists

During ingestion, if replication lag exists and Hevo attempts to read incremental data from the read replica:

• PostgreSQL allows the read operation to continue only for a limited duration defined by the max_standby_streaming_delay parameter.

• If the read replica applies the required changes within this duration, Hevo continues ingesting data normally. If the delay exceeds this limit, the current ingestion job completes without ingesting new data. The next job resumes ingestion from the last successfully processed position.

  Note: No data loss occurs. Once the read replica applies the pending changes from the primary database, those changes are ingested in subsequent Pipeline runs.

Recommended Actions to Prevent Ingestion Delays

To prevent delays in data ingestion for the Pipeline, perform the following actions:

• Check the primary database instance for transactions that have been running for a long time and ensure they are completed or optimized.

• Ensure that the following PostgreSQL parameters are configured as follows:

  Parameter	Value	Description
  hot_standby_feedback	1	Allows the standby server to inform the primary server about the queries running on the standby. This prevents the primary server from removing WAL data required by those queries. Set this value to 1 to avoid query interruptions caused by replication conflicts. However, enabling it causes the primary server to retain older WAL data longer, which can increase storage usage.
  max_standby_streaming_delay	15000-25000	Specifies the maximum time (in milliseconds) the standby server waits while applying changes from the primary database before canceling conflicting queries. Set this parameter to allow a delay of 15–25 seconds so that the queries can complete before being canceled. However, this may increase replication lag and affect the availability of current data on the standby server, particularly when the replica acts as a backup for the primary database. Adjust this value based on your query workload and the replication lag you can allow on the standby server.

  Refer to the Configure the replication parameters section for the steps to update the values of these parameters.

Revision History