# How to store count of unread messages?

**URL:** <https://forum.yugabyte.com/t/how-to-store-count-of-unread-messages/662>\
**Category:** General\
**Created:** [March 23, 2020, 8:17am UTC](https://forum.yugabyte.com/t/how-to-store-count-of-unread-messages/662 "2020-03-23T08:17:52Z")\
**Posts on this page:** 20\
**Page:** 2

<div class="post-metadata">

**Author:** ![dorian\_yugabyte](https://yyz1.discourse-cdn.com/flex027/user_avatar/forum.yugabyte.com/dorian_yugabyte/32/206_2.png) [@dorian\_yugabyte](https://forum.yugabyte.com/u/dorian_yugabyte)\
**Post date:** [March 27, 2020, 11:27am UTC](https://forum.yugabyte.com/t/how-to-store-count-of-unread-messages/662/21 "2020-03-27T11:27:35Z")

</div>

Every time a message is inserted we execute this query:

```auto
    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 ?

---

<div class="post-metadata">

**Author:** ![ivan\_s](https://yyz1.discourse-cdn.com/flex027/user_avatar/forum.yugabyte.com/ivan_s/32/163_2.png) [@ivan\_s](https://forum.yugabyte.com/u/ivan_s)\
**Post date:** [March 27, 2020, 12:39pm UTC](https://forum.yugabyte.com/t/how-to-store-count-of-unread-messages/662/22 "2020-03-27T12:39:55Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![ivan\_s](https://yyz1.discourse-cdn.com/flex027/user_avatar/forum.yugabyte.com/ivan_s/32/163_2.png) [@ivan\_s](https://forum.yugabyte.com/u/ivan_s)\
**Post date:** [March 27, 2020, 12:46pm UTC](https://forum.yugabyte.com/t/how-to-store-count-of-unread-messages/662/23 "2020-03-27T12:46:19Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![dorian\_yugabyte](https://yyz1.discourse-cdn.com/flex027/user_avatar/forum.yugabyte.com/dorian_yugabyte/32/206_2.png) [@dorian\_yugabyte](https://forum.yugabyte.com/u/dorian_yugabyte)\
**Post date:** [March 27, 2020, 1:02pm UTC](https://forum.yugabyte.com/t/how-to-store-count-of-unread-messages/662/24 "2020-03-27T13:02:16Z")

</div>

> [@ivan\_s](#):
>
> Should I use a transaction?

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

> [@ivan\_s](#):
>
> Where did the dialog\_id and categories\_unread columns come from?

Just added `dialog_id` as a primary key.

> [@ivan\_s](#):
>
> And what is the query when we will read the dialog?

Yes. Something like:

```auto
    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;

```

---

<div class="post-metadata">

**Author:** ![ivan\_s](https://yyz1.discourse-cdn.com/flex027/user_avatar/forum.yugabyte.com/ivan_s/32/163_2.png) [@ivan\_s](https://forum.yugabyte.com/u/ivan_s)\
**Post date:** [March 27, 2020, 1:11pm UTC](https://forum.yugabyte.com/t/how-to-store-count-of-unread-messages/662/25 "2020-03-27T13:11:16Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![dorian\_yugabyte](https://yyz1.discourse-cdn.com/flex027/user_avatar/forum.yugabyte.com/dorian_yugabyte/32/206_2.png) [@dorian\_yugabyte](https://forum.yugabyte.com/u/dorian_yugabyte)\
**Post date:** [March 27, 2020, 1:21pm UTC](https://forum.yugabyte.com/t/how-to-store-count-of-unread-messages/662/26 "2020-03-27T13:21:00Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![dorian\_yugabyte](https://yyz1.discourse-cdn.com/flex027/user_avatar/forum.yugabyte.com/dorian_yugabyte/32/206_2.png) [@dorian\_yugabyte](https://forum.yugabyte.com/u/dorian_yugabyte)\
**Post date:** [March 27, 2020, 1:50pm UTC](https://forum.yugabyte.com/t/how-to-store-count-of-unread-messages/662/27 "2020-03-27T13:50:03Z")

</div>

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

```python
    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")

```

---

<div class="post-metadata">

**Author:** ![ivan\_s](https://yyz1.discourse-cdn.com/flex027/user_avatar/forum.yugabyte.com/ivan_s/32/163_2.png) [@ivan\_s](https://forum.yugabyte.com/u/ivan_s)\
**Post date:** [March 27, 2020, 2:40pm UTC](https://forum.yugabyte.com/t/how-to-store-count-of-unread-messages/662/28 "2020-03-27T14:40:02Z")

</div>

+1:

> [@dorian\_yugabyte](#):
>
> 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;

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:

> [@dorian\_yugabyte](#):
>
> 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;

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.

---

<div class="post-metadata">

**Author:** ![ivan\_s](https://yyz1.discourse-cdn.com/flex027/user_avatar/forum.yugabyte.com/ivan_s/32/163_2.png) [@ivan\_s](https://forum.yugabyte.com/u/ivan_s)\
**Post date:** [March 27, 2020, 2:59pm UTC](https://forum.yugabyte.com/t/how-to-store-count-of-unread-messages/662/29 "2020-03-27T14:59:41Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![dorian\_yugabyte](https://yyz1.discourse-cdn.com/flex027/user_avatar/forum.yugabyte.com/dorian_yugabyte/32/206_2.png) [@dorian\_yugabyte](https://forum.yugabyte.com/u/dorian_yugabyte)\
**Post date:** [March 27, 2020, 3:27pm UTC](https://forum.yugabyte.com/t/how-to-store-count-of-unread-messages/662/30 "2020-03-27T15:27:35Z")

</div>

> [@ivan\_s](#):
>
> What do you think about it?

Yes it’s more clear.

> [@ivan\_s](#):
>
> `UPDATE dialogs SET unread_count=0 WHERE dialog_id=1;`

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).

> [@ivan\_s](#):
>
> And you use ‘LIMIT 1’ in the 2 example but not use in the 1 example. Why?

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.

> [@ivan\_s](#):
>
> Maybe it’s better to remove a row from categories\_unread when categories\_unread.unread\_count=0? It’ll speed up the reading.

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

> [@ivan\_s](#):
>
> And how to determine when INSERT to categories\_unread and UPDATE?

By doing an `UPSERT` using [`ON CONFLICT` clause](https://docs.yugabyte.com/latest/api/ysql/commands/dml_insert/#on-conflict-clause).

---

<div class="post-metadata">

**Author:** ![ivan\_s](https://yyz1.discourse-cdn.com/flex027/user_avatar/forum.yugabyte.com/ivan_s/32/163_2.png) [@ivan\_s](https://forum.yugabyte.com/u/ivan_s)\
**Post date:** [March 28, 2020, 6:49am UTC](https://forum.yugabyte.com/t/how-to-store-count-of-unread-messages/662/31 "2020-03-28T06:49:49Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![dorian\_yugabyte](https://yyz1.discourse-cdn.com/flex027/user_avatar/forum.yugabyte.com/dorian_yugabyte/32/206_2.png) [@dorian\_yugabyte](https://forum.yugabyte.com/u/dorian_yugabyte)\
**Post date:** [March 30, 2020, 9:18am UTC](https://forum.yugabyte.com/t/how-to-store-count-of-unread-messages/662/32 "2020-03-30T09:18:56Z")

</div>

> [@ivan\_s](#):
>
> Do you think this method is faster than counting the amount for each page request?

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.

> [@ivan\_s](#):
>
> And what indexes do you advise to use at the start?

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` ?**

> [@ivan\_s](#):
>
> I got it. Here’s a detailed diagram of the application:
> 
> ![Снимок экрана от 2020-03-25 13-09-19](https://canada1.discourse-cdn.com/flex027/uploads/yugabyte/original/1X/e881806255ba5d1ffa6e16ad911c0f4d8af78aa8.png)

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

> [@ivan\_s](#):
>
> And can you show me an example with the “ON Conflict clause”?

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!):

```postgresql
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;

```

---

<div class="post-metadata">

**Author:** ![ivan\_s](https://yyz1.discourse-cdn.com/flex027/user_avatar/forum.yugabyte.com/ivan_s/32/163_2.png) [@ivan\_s](https://forum.yugabyte.com/u/ivan_s)\
**Post date:** [April 2, 2020, 9:45am UTC](https://forum.yugabyte.com/t/how-to-store-count-of-unread-messages/662/33 "2020-04-02T09:45:53Z")

</div>

> [@dorian\_yugabyte](#):
>
> You can even do a combination of keeping `unread_counts` only for categories and do counts on page-view for chats.

Yes, I was thinking about it. Thank you.

> [@dorian\_yugabyte](#):
>
> Why do we need this additional table ?

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.

> [@dorian\_yugabyte](#):
>
> Please don’t do the delete/upsert for now.

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.

> [@dorian\_yugabyte](#):
>
> How many `categories` and how many `chats` will this show in average ?

What do you mean?

---

<div class="post-metadata">

**Author:** ![dorian\_yugabyte](https://yyz1.discourse-cdn.com/flex027/user_avatar/forum.yugabyte.com/dorian_yugabyte/32/206_2.png) [@dorian\_yugabyte](https://forum.yugabyte.com/u/dorian_yugabyte)\
**Post date:** [April 2, 2020, 12:10pm UTC](https://forum.yugabyte.com/t/how-to-store-count-of-unread-messages/662/34 "2020-04-02T12:10:42Z")

</div>

> [@ivan\_s](#):
>
> What do you mean?

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 ?

> [@ivan\_s](#):
>
> 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.

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 ?

> [@ivan\_s](#):
>
> 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.

Ok makes sense. Did you try my upsert query ?

> [@ivan\_s](#):
>
> Yes, I was thinking about it. Thank you.

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 ?

---

<div class="post-metadata">

**Author:** ![ivan\_s](https://yyz1.discourse-cdn.com/flex027/user_avatar/forum.yugabyte.com/ivan_s/32/163_2.png) [@ivan\_s](https://forum.yugabyte.com/u/ivan_s)\
**Post date:** [April 2, 2020, 1:52pm UTC](https://forum.yugabyte.com/t/how-to-store-count-of-unread-messages/662/35 "2020-04-02T13:52:11Z")

</div>

> [@dorian\_yugabyte](#):
>
> 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 ?

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

> [@dorian\_yugabyte](#):
>
> Ok makes sense. Did you try my upsert query ?

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)?

> [@dorian\_yugabyte](#):
>
> 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 ?

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.

> [@dorian\_yugabyte](#):
>
> 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 ?

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

---

<div class="post-metadata">

**Author:** ![dorian\_yugabyte](https://yyz1.discourse-cdn.com/flex027/user_avatar/forum.yugabyte.com/dorian_yugabyte/32/206_2.png) [@dorian\_yugabyte](https://forum.yugabyte.com/u/dorian_yugabyte)\
**Post date:** [April 2, 2020, 3:06pm UTC](https://forum.yugabyte.com/t/how-to-store-count-of-unread-messages/662/36 "2020-04-02T15:06:21Z")

</div>

> [@ivan\_s](#):
>
> 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)?

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

---

<div class="post-metadata">

**Author:** ![dorian\_yugabyte](https://yyz1.discourse-cdn.com/flex027/user_avatar/forum.yugabyte.com/dorian_yugabyte/32/206_2.png) [@dorian\_yugabyte](https://forum.yugabyte.com/u/dorian_yugabyte)\
**Post date:** [April 3, 2020, 11:24am UTC](https://forum.yugabyte.com/t/how-to-store-count-of-unread-messages/662/37 "2020-04-03T11:24:25Z")

</div>

@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:

```auto
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:

```auto
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:

```auto
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;

```

---

<div class="post-metadata">

**Author:** ![ivan\_s](https://yyz1.discourse-cdn.com/flex027/user_avatar/forum.yugabyte.com/ivan_s/32/163_2.png) [@ivan\_s](https://forum.yugabyte.com/u/ivan_s)\
**Post date:** [July 13, 2021, 1:52pm UTC](https://forum.yugabyte.com/t/how-to-store-count-of-unread-messages/662/38 "2021-07-13T13:52:23Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![ivan\_s](https://yyz1.discourse-cdn.com/flex027/user_avatar/forum.yugabyte.com/ivan_s/32/163_2.png) [@ivan\_s](https://forum.yugabyte.com/u/ivan_s)\
**Post date:** [July 13, 2021, 1:52pm UTC](https://forum.yugabyte.com/t/how-to-store-count-of-unread-messages/662/39 "2021-07-13T13:52:57Z")

</div>

@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.

---

<div class="post-metadata">

**Author:** ![dorian\_yugabyte](https://yyz1.discourse-cdn.com/flex027/user_avatar/forum.yugabyte.com/dorian_yugabyte/32/206_2.png) [@dorian\_yugabyte](https://forum.yugabyte.com/u/dorian_yugabyte)\
**Post date:** [July 15, 2021, 3:28pm UTC](https://forum.yugabyte.com/t/how-to-store-count-of-unread-messages/662/40 "2021-07-15T15:28:29Z")

</div>

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

[Previous page](https://forum.yugabyte.com/t/how-to-store-count-of-unread-messages/662.md?page=1)