1

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.

Improve this question
3
  • 1
    youre saying you can send emails to a api over the internet faster than you can insert them into your database? – Ewan Apr 5 '18 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 Apr 5 '18 at 7:36
  • Consider asking this on a database SE, as this is most likely just a question of tuning your databse. – Thorbjørn Ravn Andersen Nov 21 '20 at 14:14
0

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.

Improve this answer
7
  • 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 Apr 5 '18 at 7:37
  • if you didnt have the reports, how mamy indexes would you need? – Ewan Apr 5 '18 at 7:38
  • In that case, just a clustered index on the primary key would have served the purpose – Jaguar Apr 5 '18 at 7:40
  • making your inserts faster right? – Ewan Apr 5 '18 at 7:41
  • Yes, but reports will suffer.. – Jaguar Apr 5 '18 at 8:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.