Have I properly thought out the architecture of the messenger?

Question

PostgreSQL

I am developing a pet-project mobile application that has the ability to exchange real-time messages. The complexity of the messenger, in terms of functionality, is minimal, namely:

1. It is possible to communicate in private messages 1-1
2. It is possible to communicate in a group chat that the application creates. The user himself cannot create a group chat
3. The user can write and read a message.
4. The user can see that his message has been read / not read

. There are no additional features in the style of attaching files, photos, geolocations.

The stack is as follows: Go on the backend due to boxed goroutines/channels. Redis as cache + pub/sub. PostgreSQL as storage.

The database schema is as follows.



The tasks are:
1 - Get a list of all user's chats with the latest message in this chat.
2 - In real-time, update messages in the list when new messages arrive
3 - When entering a specific chat, exchange messages in real-time
4 - When scrolling a chat, load data from the database.

How I plan to do it:

1. On the backend I will make an api to get a list of all chats. When entering the application, I will send a request to /api/chats and get all the user's chats with the latest message from postgres. Save the result in the cache.

2. When entering the application, I will connect to the socket, which will listen to the channel in the radish for the appearance of new messages in all chats, in order to update the list of chats when new messages arrive. If a new message arrives, then update the cache from n1.

3. When entering a specific chat, they will connect to another socket and enter the radish channel. When sending a message, save it to the database

4. When entering a specific chat, load the last 20-30 messages from the database. When scrolling, load more.

Questions I can't figure out:

1. Is it okay to keep multiple socket connections for different purposes, such as updating the list of chats and messages in a particular chat.

2. Is the architecture I have chosen correct? What can be improved?

3. With a very large number of messages, history loading can become a bottleneck for the database

4. What is the best way to cache chats? By user ID or by chat ID or something else?

5. Even with a small number of users, such an architecture can gobble up all the memory, because you will have to store in the cache all the user's chats and history and listen on several sockets.

6. Maybe instead of a cache in a radish, store data in the device's local cache?

I searched Google for topics about creating chats, but usually the simplest examples with 1-1 communication or several people in one chat are dealt with, which is not suitable in my case.

PS in the messages table there is also an is_read field, which I added later, after drawing the schema, which is responsible for "read / not read"

Answer 1

> The user can see that his message has been read / not read.
It is not clear how to implement this requirement with such a database scheme.

Answer 2

1. You do not need to keep several connections. The results of listening to a radish can be returned through a single socket.
3. Yes, of course, but will there be a large number of users? If so, check out the Discord experience -- https://blog.discord.com/how-discord-stores-billio... . The DB will have to be changed, most likely.
4. By chat ID, if different types of chats are planned (1-1, group).
5. We must look at the available resources. The cache can be limited by memory so that it does not spread. In this case, some chats may fall out or a little bit will be cached for each, but what can you do.
6. You will have to store something in the device to show at least old messages if there is no network, for example.