Better Solution Instead of Updating database for status callback from external api

Hi Guys!
I have a question.
In my app, I use external api for status update.
For example, When I send sms messages using external api, they sent back the status using status callback which I defined.
Normally there are 3 times status update for each message something like, sent accepted, delivered.
Whenever status update comes in from external service to my controller, I inserted first and updated.
So If I send 1000 messages, there will be 3000 database operation(insert and update) total.

I feel like this is wrong…This will be performance bottleneck in very near future…
Any suggestion for optimizing this problem?

Please Help!

3000 database operations is not that much, are you sure it’s going to be a bottleneck?

1 Like

is it not that much? I am asking because I don’t know if it is.
My app is for sending text message, So I was worried.
what if 10-20 customers send 1000 messages?

Try benchmarking your current setup, then you’d have more information. You can even use benchee to benchmark an ecto app (I assume that’s what you are using).

Here’s an example

As your app gets more and more users consider using a queue or background job atleast. That way you can control concurrent database queries. Or you can design a caching system that will flush itself after few minutes or so.

I’ll recommend former approach, its simple and flexible.

Is that (1) a likely scenario? Or is 1000 costumers sending 10-20 messages more realistic (2)? Either will generate a similar load of status updates but could be countered with different solution approaches.

  1. It might be reasonable for a process per user to cache all recent status updates, and batch update either when the final update (delivery) comes in or the oldest message status reaches some age-limit.
  2. May require a more complex solution that may have a process for each status type managed by user key which can all be concurrently queried and an entirely separate process being responsible for batching updates to the database (just as an example).

The point being - you can’t even effectively benchmark until you know the operational profile that your application will be actually experiencing in production. So until such time do the simplest thing possible but make monitoring a priority so that you can catch emerging bottlenecks early.

1 Like