Issue
Your connection has a high number pending writes to be synced to Salesforce via Heroku Connect and sync is processing very slowly.
Resolution
Syncing data from Heroku Postgres to Salesforce can slow down due to a variety of reasons, the most common being:
- Non-contiguous changes to your records are causing the data to be synced via SOAP API instead of the Bulk API: See: "Why isn't my connection using the Bulk API to write to Salesforce?".
- Salesforce is taking a long time to process the changes being synced by Heroku Connect, often due to performance issues with triggers or custom workflows running on Salesforce when the data is getting synced.
- Errors syncing records to Salesforce - see "Heroku Connect Write Errors".
- Performance issues on the Heroku Postgres database can slow down the queries that Heroku Connect runs against the Trigger log table to know which data it has to sync to Salesforce and to write the sync results back to it.
- Heroku Connect processes the Trigger Log table in a single thread. Because of this, a large number of changes to one object can hinder writes to one or more other objects, as Heroku Connect will process all of them sequentially.
You can check the Heroku Connect logs to better understand how Heroku Connect is syncing the data. Search for logs containing "↑SALESFORCE" to see how the pending writes are being synced to Salesforce:
2022-06-02 10:35:32.613 1 Account INSERT ↑SALESFORCE 1 rows total (0.19 secs) [SOAP]
2022-06-02 10:35:32.384 1 Account UPDATE ↑SALESFORCE 1 rows total (0.13 secs) [SOAP]
2022-06-02 10:35:32.226 16 Account DELETE ↑SALESFORCE 16 rows total (0.29 secs) [SOAP]
2022-06-02 10:35:31.901 200 Account DELETE ↑SALESFORCE 200 rows total (2.24 secs) [SOAP]
These logs show us the amount of records that were synced at a specific time, the amount of time it took Salesforce to process those changes, measured in seconds, and if the sync process happened via SOAP or Bulk API. (See also: "Common log messages").
- If you see a series of logs that show that only a few records for a specific mapping and operation (insert/update/delete) are being synced on each batch, this could indicate that your database write pattern does not result in contiguous changes and this doesn't allow Heroku Connect to use the Bulk API. To speed this up, ensure that your database write patterns meet the Bulk API requirements.
- If you see very high Salesforce processing times for your requests (higher than a few hundred milliseconds per record), check the performance of any possible triggers or workflows in Salesforce affecting the objects that are being synced.
- If you see a big delay between each of the log lines/batches, this could indicate that your database is experiencing performance issues and Heroku Connect can't query the Trigger log tables fast enough. Follow "Monitoring Heroku Postgres" and run heroku pg:diagnose to check the health of your database. Use heroku pg:bloat and heroku pg:table-size to check the size and bloat on your Trigger log tables - high values could explain performance issues with those tables.
You can also inspect the Trigger log table to directly check the number of pending writes for your connection:
SELECT COUNT(*), state FROM salesforce._trigger_log WHERE state IN ('NEW', 'PENDING', 'IGNORE', 'BULKSENT') group by state;
If this query is taking more than a few seconds to return a result, it could be an indication of degraded database performance that would be affecting your Heroku Connect sync processes too.