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.