I want to fix my current Email Delivery system which sends email using a third-party email provider and creates a record for each email sent in RDS. Functionally this is how the system behaves -

  • A user creates Campaign to send emails to their leads. A campaign can be sent to up to 1 million users
  • A created campaign is stored in a table called campaign. The table also stores information of leads (JSON condition) to whom email needs to be sent
  • A job runs which polls campaign table and calculates actual leads from the JSON conditon and retrieves their email addresses from lead table
  • Multiple instances of this job run depending on the number of pending(queued) campaigns
  • A new thread picks the list obtained in the above step and sends the emails in bulk of 50 emails using third party email service provider API
  • For each email sent a record in Email table is created. The table stores the information of sender and receiver along with the status of the email. The table also has relation to campaign table
  • The information of email bounces are received using a webhook and status of such emails are set to "bounced" in Email table
  • The email table is used to build different kinds of reports like finding the leads to whom the email was sent using a particular campaign by some particular user
  • The columns of Email table are indexed because it is heavily read across multiple parts of the application. Due to this writes to the table have become slower
  • In case many emails are queued for processing and multiple jobs are spawned, writes on the Email table becomes a bottleneck, which causes replication lag and impacts reports at various places

My goal is to make inserts and reads on Email table faster which is not possible using RDS.

Is there any other way I can use to scale this system with the capability to send millions of emails?

I see Elastic Search as one option for storing Email table. Since Elastic Search is not a primary data store I need to build applications to sync this data to a persistent storage. Correct me if I am wrong.

  • 1
    youre saying you can send emails to a api over the internet faster than you can insert them into your database?
    – Ewan
    Commented Apr 5, 2018 at 7:27
  • 1
    @Ewan It sounds weird but yes that's what happens in this case. It is also a function of a number of records in the table.When the number of records is more than 10 million then inserting a record into it makes the indexes to be rebuilt causing page faults to increase and ultimately slowing down the inserts.
    – Jaguar
    Commented Apr 5, 2018 at 7:36
  • Consider asking this on a database SE, as this is most likely just a question of tuning your databse. Commented Nov 21, 2020 at 14:14

2 Answers 2


It sounds like its the reports that are slowing you down.

Move all reporting to a datawarehouse on a separate box.

If that doesn't solve it.

Split the app so that different campaigns are run on separate databases.

  • It is the inserts which are slowing down the system. Also splitting the campaign across multiple database won't serve the purpose as the reports are built across campaigns
    – Jaguar
    Commented Apr 5, 2018 at 7:37
  • if you didnt have the reports, how mamy indexes would you need?
    – Ewan
    Commented Apr 5, 2018 at 7:38
  • In that case, just a clustered index on the primary key would have served the purpose
    – Jaguar
    Commented Apr 5, 2018 at 7:40
  • making your inserts faster right?
    – Ewan
    Commented Apr 5, 2018 at 7:41
  • Yes, but reports will suffer..
    – Jaguar
    Commented Apr 5, 2018 at 8:07

You could be successful by distributing the work with the Email table over separate tables.

Create one table e.g. EmailWork that has all information what email is sent to whom.

Create a second table e.g. EmailStatus that is empty at first, and that takes all the writing stuff. If mail sender status information drops in, fails, bumps etc - everything goes there.

The second table could act as, so to say, "stream". Once a row have been worked, it is deleted.

EmailWorkInfo could be indexed in in order to read fast. EmailStatus could be lightweight as possible, therefore having no indizes.

Now you can control the workload. Iterate slowly through EmailStatus, take an info, search in EmailWork and update. Remove the row in EmailStatus once you have been successful. Wait updating, if it becomes too much. Wait sending further, if the second table becomes too big.

Another approach would be to have one separate table only for the report.

I cannot know which is best as I don't know the details. However, my suggestion is to check if the workload can be distributed over different tables, and then to control how they are worked.

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.