0

We have a Heroku Postgress setup which has contact object data of our sfdc org synching every 5 mins ( bidirectional ). I have a use case where I need to manipulate approx. 20 million contact recs. Since, in start method I can query up to 50K, running the batch for every 50K recs would kill the org health. So, we are thinking of going with Bulk API 2.0. Querying those 20Mil recs, having it in csv & using Bulk API would also need manual intervention.

So, the question is, does anyone has better solution to tackle this using Heroku ? if yes, can you pls let me know how I can query them and processing it in Salesforce.

My thought process is to have an integration where I send a query to Heroku, it will take it's own time query recs and once the query has returned values, use some middleware to process in chunks... something of this sort.

-P.s, I am very new to Heroku. Any suggestions would be really helpful.

Improve this question
1
  • By syncing, are you talking about Heroku Connect ? If so, it will do this for you. Postgres can certainly handle a 20 million rows update. HC writes the changes back to SF org via Bulk API. It'd be an interesting test to see if it can handle this kind of update. Commented Feb 11, 2022 at 17:38

1 Answer 1

Reset to default
2

Apex Batchable classes can handle 50,000,000 records in a single query. However, there's no way you can get this to run every five minutes, not from Salesforce or Heroku. You're basically suggesting trying to perform DML on 5,760,000,000+ records per day, resulting in terabytes of data being transferred.

Even if you could somehow handle that much data, you'd be paying significantly in Heroku CPU time. As far as Apex is concerned, you'd have to break down the callouts to just 100 per batch execute method, meaning you could barely synchronize the entire database once per day without hitting governor limits.

What you need to do is use a delta update algorithm. In Apex code, you can use the Database.getUpdated and Database.getDeleted methods, which will return all records updated or deleted in a given time window. Similarly, your Heroku app should query for records in the Postgres database that have been updated/deleted since the last successful synchronization.

Additional logic should be put in place to make sure that actual changes occurred to avoid infinite loops. You can do this by comparing all non-audit fields to see if any changes have occurred. This is pretty easy to implement, but you'll need to do some engineering to handle the sheer amount of data involved.

Improve this answer
2
  • Thanks, there is 1 additional column which is Heroku associated to each contact table populated by external system needs to sent to Salesforce. On a daily basis, it will have around 10-20K updates in Heroku. ( sorry for being not clear in previous post. 20M data is a historical data from 2021-2010 ). The plan is to go like 10 days data in each batch, schedule once a day. Qty may come around 50-80K. So, In order for me to query data from heroku connect & send it over to Salesforce, I need to have a middle where which pushes the data every 100 recs at a time correct ? be it aws or mule ? Commented Feb 14, 2022 at 5:21
  • 1
    @LWC_Beginner That sounds like it should work. Unfortunately, I only have limited experience with Heroku connect. Once you get the data in sync, it should be able to handle the small delta updates for you. Commented Feb 14, 2022 at 6:02

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.