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 fromlead
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 tocampaign
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.