Building a scalable Email Delivery system

Question

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.

Answer 1

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.

Answer 2

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.