I have a system which hits a API on daily basis and collects data. This data is divided into logical groups, with a logical group defined as all data belonging to a particular user. A user has 30 subaccounts and for each account I hit the API and insert data into the database. After each hit, I perform an insert so I have 30 hits for this ex.

Now sometimes the API throws some temporary error, say at hit 20, so if I put that in try catch data for that particular hit is skipped which essentially corrupts the database with malformed data for that particular day.

I want to insert all the data only if all the hits are successful and I cannot store the result for each hit in memory as that would too much memory intensive as each hit contains lots of data.

How should I design the system so that if an error occurred in between nothing is inserted?

This can be achieved via transactions at DB level, but I have a restriction that I use Elasticsearch and I cannot find transactional inserts in it. Somehow I need to handle it at the application level.

  • 1
    If the error is only temporary you can wait and try to fetch the data again after some time. Alternatively, have you thought about storing the results of the "API hits" to files and then only inserting after you cached the results locally?
    – Mael
    Commented Mar 6, 2018 at 6:52

1 Answer 1


Use a temporary table.

Or, rather than muck around with real temporary tables, have a staging table.

insert the rows into the staging table against a jobId which you hold in memory.

If everything goes fine, do a single transaction at the end to copy the rows into the real table, then clear them out of the staging table.

If something breaks, you have only affected the staging table, so your db is fine. You can clear up the staging data if you like, but because you use a new jobId each time its not urgent.

You can possibly improve the overall process by keeping the staging data around and re running only the failed bits, depending on the details of your case.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.