How to store count of unread messages?

General
21

Every time a message is inserted we execute this query:

    UPDATE dialogs SET unread_count=unread_count + 1 WHERE dialog_id=%s;
    UPDATE categories_unread SET unread_count=unread_count+1 WHERE 
    (SELECT unread_count FROM dialogs WHERE dialog_id=%s AND unread_count=1)=1 AND categories_unread=%s AND user_id=%s;

The query above, will increment categories_unread.unread_count ONLY when dialogs.unread_count went from 0 to 1. While dialogs.unread_count will always increment.

Does this work for you ?

1 Like
22

I didn’t try it but it looks good.

  1. Should I use a transaction?
  2. Where did the dialog_id and categories_unread columns come from?
23

And what is the query when we will read the dialog? I have to do dialogs.unread_count = 0 and categories_unread.unread_count - 1 if dialogs.unread_count >= 1.

24

Yes. You can run both queries as a single string like I did.

Just added dialog_id as a primary key.

Yes. Something like:

    UPDATE categories_unread SET unread_count=unread_count-1 WHERE 
    (SELECT unread_count FROM dialogs where dialog_id=1 LIMIT 1)>=1;
    UPDATE dialogs SET unread_count=0 WHERE dialog_id=1 AND unread_count>=1;
25

Thanks! Can you show how to do with a transaction?

26

1 way is to set the connection to autocommit and send 1 query with all the statements inside.

Another way is to use normal transactions with BEGIN and COMMIT. In the case above, begin/commit is implicit.

1 Like
27

In python code with autocommit it’s something like this:

    conn = psycopg2.connect("dbname=ivan host=127.0.0.1 user=postgres port=5433")
    conn.autocommit = True
    cur = conn.cursor()
    cur.execute("full query here")
1 Like
28

+1:

I checked it out. It works! I replaced it:
WHERE (SELECT unread_count FROM dialogs WHERE dialog_id=%s AND unread_count=1)=1
on:
WHERE (SELECT unread_count FROM dialogs WHERE dialog_id=%s)=1.
What do you think about it?

-1:

And it works! I replaced it:
UPDATE dialogs SET unread_count=0 WHERE dialog_id=1 AND unread_count>=1;
on
UPDATE dialogs SET unread_count=0 WHERE dialog_id=1;
What do you think about it?

And you use ‘LIMIT 1’ in the 2 example but not use in the 1 example. Why?
When should I use it? It works with him and without.

29

And one more thing:

  1. Maybe it’s better to remove a row from categories_unread when categories_unread.unread_count=0? It’ll speed up the reading.
  2. And how to determine when INSERT to categories_unread and UPDATE? This can be when we send a message and not receive a new one.
30

Yes it’s more clear.

I use my query because I don’t want to update the row when I don’t have to.
Setting unread_count=0 when it’s already 0 is slower because it writes the row (while mine doesn’t).

I used LIMIT 1 because the subquery needs to return 1 value. But later I added the dialog_id and which will return 1 row maximum.

Maybe. It will depend on the concurrency of increments/decrements.
I think keep it simple for now and just filter where unread_counts>0.

By doing an UPSERT using ON CONFLICT clause.

31

Thank you!

It really works.
Do you think this method is faster than counting the amount for each page request?

And can you show me an example with the “ON Conflict clause”?

And what indexes do you advise to use at the start?

32

This will depend. If it really is (few users/chat) + (few new messages/second) + (high chats/user) then doing increments should be faster and the opposite with reads.

You can even do a combination of keeping unread_counts only for categories and do counts on page-view for chats.

You should always set a primary key.
In categories_unread, you just query by WHERE user_id=x to get all categories. It’s the fastest way because it’s reading from the primary-key. No index needed.

Does it make sense to keep unread_counts in categories table instead of categories_unread table ? Why do we need this additional table ?

Please show me the full schema and relationships for dialogs,categories,category_unread and how they are connected/relationships to user ?

How many categories and how many chats will this show in average ?

Please don’t do the delete/upsert for now. This would be good only when you have a high number of categories which change very rarely. While you have low number of categories which change often.
Just filter by unread_count>0 when selecting.
The query would be something like this (didn’t test it!):

INSERT INTO categories_unread(user_id,category_id, unread_counts) VALUES (1,1,1) 
WHERE (SELECT unread_count FROM dialogs WHERE dialog_id=%s)=1
ON CONFLICT(user_id,category_id) DO UPDATE
SET unread_counts=unread_counts+1;
33

Yes, I was thinking about it. Thank you.

I’ll tell you more. We have work spaces.
And the user can be in several at once.
And there can be several users in the workspace.

Each space has its own categories. And we have to display unreads for each of the users.
So we need a separate category_unread table.

I got it. That’s not what I was saying.
Originally, the categories_unread table was empty. And we need to create a row when we get an unread dialog. And then we need to update it, not create a row.
So we need a query that tries to update the row first, and if it doesn’t find it, it will create a row.

What do you mean?

34

In a pageview, you disaply “x” number of chats. If we don’t keep an unread_count, then you have to make queries for each of them to get unread_count. How many there will be ? Meaning how many chats a category will have ?

Ok makes sense. Will there be the same for chats ? Each user has to keep his own unread_count for each chat/dialog messages too ?

Ok makes sense. Did you try my upsert query ?

On a chat, we can keep “last_message_user_id” column. This will hold the user_id which posted the last message. If the current_user is the last one, we don’t have to do a count. Makes sense ?

35

At least 40 on one page. There is also pagination and usually more than 200 chat rooms in one category.

No, I think we should try to update first, and if there’s no row, insert. I think the record is likely to be created and it is better to try to update it first. What do you think about it? Can you show me a query (update and insert if not)?

The counter will be stored directly in the dialog. There is no need to store it in a separate table. It may be necessary in the future, but not yet.

Yes. But still, I think it’s better to keep the counter right in the dialogue.

36

That’s the only way to do upserts. Internally it is the same:
Update or Insert = read + insert/update
Insert or Update = read + insert/update

37

@ivan_s
We have to make some adjustments to our queries above to have correct results on concurrent updates. On selects, we have to add FOR UPDATE to guard against this:

UPDATE categories_unread SET unread_count=unread_count-1 WHERE (SELECT unread_count FROM dialogs where dialog_id=1 *FOR UPDATE*)>=1;
UPDATE dialogs SET unread_count=0 WHERE dialog_id=1 AND unread_count>=1;

The same in:

UPDATE dialogs SET unread_count=unread_count + 1 WHERE dialog_id=%s;
UPDATE categories_unread SET unread_count=unread_count+1 WHERE (SELECT unread_count FROM dialogs WHERE dialog_id=%s FOR UPDATE)=1 AND categories_unread=%s AND user_id=%s;

And in the upsert query:

INSERT INTO categories_unread(user_id,category_id, unread_counts) VALUES (1,1,1) 
WHERE (SELECT unread_count FROM dialogs WHERE dialog_id=%s FOR UPDATE)=1
ON CONFLICT(user_id,category_id) DO UPDATE
SET unread_counts=unread_counts+1;
38

Hi. Thank you for sharing everything. It was helpful to me. But I forgot to thank you. It was a long time ago, but I thank you very much for your help. You are very customer oriented.

39

@dorian_yugabyte Hi. Thank you for sharing everything. It was helpful to me. But I forgot to thank you. It was a long time ago, but I thank you very much for your help. You are very customer oriented.

40

@ivan_s thank you for the kind words! Did you end up using YugabyteDB in your project ?