Good way to handle the notification counter like Facebook does

Question

I want to build a similar notification mechanism that Facebook handles.

I actually store the user's notification in an SQL table (actually it's a graph database but everybody master RDBMS terms).

I'm interested in the case where the user clicks on the icon: The counter should get a value of 0 internally and visually disappears.

How the clearing of this counter should be made?

• On clicking, call a Rest API that flags each notification as being read (table's column READ)?
  Drawback would be that it would take some time if there was huge amount of notifications to flag in order to get the counter to 0 and making it disappear.
• Having a kind of column for each user called HAS_NEW_NOTIFICATION that is valued as false each time the icon is clicked (through a REST API call), if the counter was present.
  Thus, the counter would be present only if the flag value is true.

What would be a good practice?

Answer 1

The best practice is to store a datetime stamp in the user's table that indicates the last pull by the browser for notifications. Any notifications created after this datetime stamp would be counted as new and unread.

The database can index notifications by their created datetime stamp efficiently, and adding a flag if read shouldn't impact performance. Since you are always searching for notifications after a give datetime the performance should be okay.

You just need to update the user's table of what the oldest unread datetime stamp is. That gives you a place to query the notifications for new and unread items.

If there are no unread items then the user's timestamp is updated to the last time the browser pulled for a check.

Answer 2

I cannot fully agree with previous answer, though the main idea is the same.

Last read timestamp

First of all you have to store last pull timestamp for every user if there is only personal messages, or / and last pull timestamp for every user-channel (chat, stream, room, topic, etc. whatever) pair to keep track of unread messages separately.

Storing flag for every user-notification pair may be a solution too, but often it becomes harder to catch the event of "reading" for each notification in UI (in case this is not emails box) and generally leads to bad user experience.

Query over interval

Every time you get all total unread notifications count (not the difference)

Imaging you have website where people see such counter and you update it with some interval, let's say 30 seconds, and there are 10_000 of visitors reading some articles simultaneously. Then you get 20k rmp or 333 rps only for counter. For 10 second updates you get 60k rmp 1k rps respectively.

The only situation when it' ok is when you are an administer of the website and you have the only table called "notifications" or "news" that are addressed to all its users. It is not supposed to be a lot of rows here.

Caching count

Keep unread messages count for each user-room pair

So in addition to keeping last pull timestamp you often have to cache unread messages count somewhere, otherwise indices won't help you. Count is quite expensive operation (e.g. in postgres). Depending on messages / posts / notifications table size quite soon it will become a problem.

You may cache counters in memory or in database as well.

In general you can say to whom a message is addressed (if a user authenticated), so at the moment of creating a message you can increment your counters for the user (say, in chat application).

Your cache should be invalided (or set to zero) each time after user reads messages.

Caching messages (capped lists)

Cache last N messages for each room along with last read timestamp

What is the maximum amount of messages you would like to be shown as unread? Suppose your counter has limit, then you can keep track of last N (e.g. 10) messages in each room in memory (Aerospike, Redis, Memcached), then complexity of finding messages created at later than last read timestamp will be CONST (O(N) actually, but N is const) and not depending on database size / indices at all. Make sure you gave enough memory to your cache. Everything exceeding N is flushed.

Subscriptions

You get total at the first time, and then difference on updates

You can subscribe your client using pub/sub mechanism via websocket or another protocol. Then when opening page user should get count of unread notifications and then you push these notifications to a client, increment counters, and your client implementation should increase counter on the page.

Using message brokers such as Kafka you can also connect and grab all unread messages automatically (with some restrictions, of course)

• It won't work unless you web app is SPA, in case it's classic page navigation it's almost equal to caching count.*

UI tricks

Hide the count just show label

Don't underestimate what is written on label. It may be 1 unread or 500 unread, but it doesn't really matter. Think first whether blank label (or just with 1), label 10 or label 100 make any difference. Instead of counting them you may use EXISTS clause in Postgres and just show that there are some of messages. So by developing good UI you can significantly reduce load on database.

Answer 3

A more efficient way..((O(N)[receiver notifications]) on first pull for a one to many notifications system.

storing and updating in your users table is good if it is a one to one relationship. For example, if you just want to see how many messages were sent to you by ALL users rather than ONE user, this approach would be perfect.

HOWEVER, for a one to many relationship going both ways, a notifications table directly correlated with the messages table(or whatever) could work well.

For example, for every message sent, update a record in the notifications table with count++ where the sender is you and the receiver is the recipient. AND instead of selecting messages, select from notifications WHERE RECEIVER is you and display the SENDER AND THE COUNT on your client.

When you click on the sender(random person) with the count next to them, update that row in notification where receiver is you and sender is recipient back to zero and display all of the messages sent and received from the messages table between both of you.

This is the easiest way of seeing updates. In all, just select from notifications where you are the receiver. When click on the sender, update notifications where you are receiver is you back to zero, then select messages, then start typing(sockets).

What if you send a message first though? It wont display right? Yeah, it won't because you are only selecting the receiver. So when you init a conversation you insert two rows. one with a sender as you and another as them... and they just update that value..

and if they want to destroy the conversation they just delete the two rows.

1. select users

2. click user and init conversation by inserting two rows in db ([sender, receiver, count] <- yours and their side)

3. when you init you will simultaneously go to their messages feed where you can begin typing.. make sure message column values are same as notification column values.

4. When you or they pull NOTIFICATIONS, they can see the amount of messages you sent and you can on your side as well when you select from notifications where you are the RECEIVER. All that needs to be done is update count where sender and receiver in notifications every time you or they send a message.

5. when a conversation is destroyed by either side... destroy messages then destroy notifications which is only two rows for that specific person(only 2 rows).

6. as far as performance. When storing two rows for each pair of people speaking to each other also store a timestamp. use a cron and remove the row values where the time stamp is passed say a week or so. And also allow to destroy conversations. But nonetheless the time complexity is actually very low for this.

as far as seeing read vs unread messages.. when you click on the button that inits the ui to display messages... mark all unread with read. update where receiver is me and make it read so they can see you read the conversation... should only be a couple updates if it is only a two way conversation.