10

Background

I am working on an app for a client that includes some social networking features. I was originally developing the mobile front-end, but circumstances have left me in charge of developing the back end as well.

As a general background, our system allows users to follow other users and receive notifications about those that they are following, as you'd expect from a social network. A caveat is that only a small subset (at most a few hundred) users will be followable, with the expectation that most of the user base will be following at least one of these individuals.

On the UI side, we will have a notification button with a number on it, and clicking the button will take you to the notification screen.

The problem

I've been researching strategies for implementing notifications and most resources I have found point to creating one or more notification tables in the database. (An example I like is the accepted answer here: https://stackoverflow.com/questions/9735578/building-a-notification-system ).

The thing that is throwing me off is that most database-driven strategies for notifications require inserting a row for each notification for each follower. So if a thousand people are following Sally, we insert a thousand rows into the corresponding table. Is that scalable? What happens if we get to the point where tens or hundreds of thousands of users are following Sally and she's making a few dozen posts per day?

My original idea had been to handle everything with queries: the number on the notification button would be obtained by requesting row-counts on content posted more recently than the last time you visited the notification screen, while individual notifications would be generated from more detailed queries when you visited the notification screen. This approach would require no writes or extra storage, but is inflexible and would probably hammer the server pretty hard.

SETUP

The backend (as established by the previous developer) uses CodeIgniter and a MySQL database. It is currently running on a crappy GoDaddy shared hosting account, but I assume (hope?) this will be upgraded before we go into production and the hosting package will be scaled with user growth.

Currently our only front-end is a mobile app, but we plan to later build a website as well. I am not concerned at this time with obtaining real-time push updates from the server about the notifications.

ADDENDUM

I do not specialize in backends and I'm in over my head in that department. The client knows it, and I've done my best to try to explain the scope of a project of this nature, but they have made it clear that at this point they will not trust anyone else to work on the project. We probably have another month of work to do before we can start adding testers and I can get any kind of performance metrics. I really can't estimate how many users we might have or what hardware we might be on in the next 5 years, but I think the client is hoping for hundreds of thousands of users or more.

I hope this is specific enough of a problem to be posted here; I can refine it if need be. Please ask if you have any questions or I've omitted important details.

tl;dr

  • Does a database-driven notification system have negative implications for long-term scalability when all of the users are only following some of the same few hundred people?
  • Is there a way to make the notifications database-driven without needing a separate notification row for each notification for each follower?
  • Would an entirely query-driven notification system be scalable, or have any advantages besides not writing any data to the DB?
  • Am I overthinking this too early? Should I just build something that works for now and we can worry about optimizing it if it becomes a problem, given that the client has a limited budget and we don't know yet if the final product will be popular?
  • Can you expire notifications? For example, delete anything over 2 weeks old. That should more or less balance out the size of the table used as the site matures. – GrandmasterB May 1 '15 at 3:35
  • That won't be a problem, I was more concerned with the performance implications of locking up the database writing 50,000 entries into the notifications table every time a popular user makes a post. – user45623 May 1 '15 at 4:19
  • I worked on a project with a similar (but smaller) notification system. I had a background process that looked at a queue of new posts and handled the notifications (which in this case was actually inserting an email into a second queue for sending). It wasn't real time, but it generally handled everything within a couple minutes. – GrandmasterB May 1 '15 at 4:34
10

So if a thousand people are following Sally, we insert a thousand rows into the corresponding table. Is that scalable?

Yes, provided the database tables are properly indexed.

What happens if we get to the point where tens or hundreds of thousands of users are following Sally and she's making a few dozen posts per day?

You'll generate a few dozen tens or hundreds of thousands of notification records per day for Sally, assuming you want to keep track of every notification in perpetuity. The percentage of users like Sally with that kind of traffic is always very small.

My original idea had been to handle everything with queries: the number on the notification button would be obtained by requesting row-counts on content posted more recently than the last time you visited the notification screen, while individual notifications would be generated from more detailed queries when you visited the notification screen.

This seems unnecessarily complicated. If you need detailed statistics about notifications, just store the notifications.

Does a database-driven notification system have negative implications for long-term scalability when all of the users are only following some of the same few hundred people?

That's why it works... a small number of people always generate the vast majority of the traffic.

Is there a way to make the notifications database-driven without needing a separate notification row for each notification for each follower?

Yes... Don't store the notifications; just send the notification emails, in fire-and-forget style. Or, store the notifications for a certain period of time, and then discard them. Or, discard each notification after it has been read.

Would an entirely query-driven notification system be scalable, or have any advantages besides not writing any data to the DB?

I'm not sure what you mean by this. If you want to query notifications, you have to store them in the database. Otherwise, there is nothing to query.

Am I overthinking this too early?

Talk to someone who can help you design a properly normalized, indexed database with the correct tables in it. I see no reason why such a database couldn't effectively handle the scenarios you describe.

A real-life example

As far as I know, Stack Exchange stores everything in perpetuity, including all notifications. They use database technology similar to MySql, and some caching technologies. While their hardware and storage space is substantial, the amount of traffic they get is a good problem.

  • Wow, you addressed friggin' everything! Thanks, Robert! The database is normalized but I haven't looked at the indexing yet. Unfortunately, I can't "talk to someone who can help me", as the terms are strict that I can't discuss specific details of the project with anyone, and the client has gotten to the point that they won't trust anyone but me on the project... Well, I should be able to do some research on indexing. Thanks! – user45623 May 1 '15 at 4:17
  • 1
    General rules of thumb for indexing: every Foreign Key should be indexed with duplicates possible. Every Primary Key should already be indexed. Fields that you will need to search on or apply a WHERE clause to should be indexed; those should be few. – Robert Harvey May 1 '15 at 4:31
  • 1
    This is incorrect. This is NOT scalable. For every "Sally" you're generating N rows where N is your number of users. This is going to become an issue fast if you have any reasonable number of users. 100 "Sallys" posting 10 times to 10,000 users is 10 million rows a day - doesn't sound too good eh? What you actually want to do is invert this and create one row per "Sally" post and have all users following Sally grab these instead of their own personal copy. Of course this is going to cause issues if you need user-specific logic (e.g. aggregation)... – Ben Mar 15 '17 at 0:57
  • 1
    ... the "avoid a row per post" explanation here is obviously a straw man as most systems will require these posts to stick around. Also, you don't avoid queries "because they're complicated", you avoid them because they will cause unsustainable overhead as the system scales. – Ben Mar 15 '17 at 0:59

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.