Subscriptions - How to handle large content and subscriptions amount?

Question

Hello,

I am sending notification to my users depending on what kind of subscription each user has.

For example:

• User A subscribed to all news articles
• User B subscribed to all comments
• User C subscribed to everything that is new on the site

I have a script running every 5 minutes (unless the script is still running) who does the following:

1. Get new "content" (comments, news articles, etc) posted since the last run
2. For each result, fetch each users who subscribed to "content"
3. For each users, sends a notification

What I'm concerned with is, if I have a 1,000 new "content" and my users are subscribe to 50% of it, my script will take forever to finish or will crash my server.

What I come up with is to select only 100 new "content" per run and notify the users. I still have the problem of the amount of users that can be subscribed to it.

Than I thought, I could limit the amount of users I select also to 100, than iterate until I reach all users.

Is this the right approach? Is there an easier way to do this? How does large websites handle their users notifications?

Answer 1

It's hard to know how to answer this without knowing anything about how your database is set up. With the correct setup, it should be possible to write a single query that gives you all users that need to be notified about new content.

Something like:

SELECT u.user_email 
FROM user_table AS u
LEFT JOIN subscription_table AS s
ON s.user_id = u.user_id
LEFT JOIN content_table AS c
ON c.content_type = s.content_type
WHERE c.add_time > LAST_RUN_TIME

Should hopefully get you started.

Answer 2

Being able to process (and send mails to) batches of users could be handy in terms of architecture as if the work became too much for a single process/server you could have multiple "works" all working on their own batches.

That said I'm not sure about your concerns of the script taking a long time to run or crashing the server. I think that's an issue for you to work through in the code. Profiling the code and the db queries you're doing are a must.

If you do go the route of batches you'll end up having to try to keep track of what notifications you've sent out to which users. I'd suggest strongly that you'd don't keep a table with a row for each user-content pair. If you simply store the time of the last event (article or comment publish time) that was sent to the user it will be easy to work out what you still need to send them and you won't end up with a huge table.

To re-iterate I'd suggest profiling the code and queries as a research task and work out the most effective method through that route.