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

enter image description here

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?


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.

  • Nice answer. But what if a notification is stored while the datetime stamp about the pull is about to be inserted (traversing the network). Would I lose it? Imagine the update to be: XXh:XXm:XXs:12ms and the notification arrives at XXh:XXm:XXs:11ms.. leading to a lost unread item in the future pull. – Mik378 Jul 12 '14 at 23:03
  • @Mik378 that's more of a database issue then technique. I would use NOW() as in a SQL expression and use transactions to ensure things are executed in the correct order. In MySQL you can perform a SELECT and UPDATE as one transaction. I forget the syntax, but there are ways to ensure this is done safely. – Reactgular Jul 13 '14 at 0:46
  • You just need to update the user's table of what the oldest unread datetime stamp is -> Could you please clarify this sentence? What do you mean? Update the user's last pull by the brower timestamp to the timestamp of the newest unread notification? Then why you used the word oldest? – user3019105 Aug 20 '19 at 14:15

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.


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.

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.