The architecture of the private messaging system?

Question

PHP

Hello.
On the site you visit, you need to create a system of personal messages (text + pictures) for users from scratch (much like in VK, but much simpler). Please share your experience on how to do this more correctly from the very beginning, so that you do not have to redo it later on real users. The main task is to keep a large load well + correctly store large amounts of data (user messages) + scalability.
Should I use MongoDB/MySQL for this purpose? Store all messages in one table as text? If we split the data into shards, then what to do with the correspondence between users on different shards? What do you think about Node.js + Websockets for these purposes? How to properly store user messages on different servers?
Thank you.

Answer 1

Should I use MongoDB/MySQL for this purpose?

PostgreSQL or MySQL, but by no means NoSQL, which looks like a panacea only at first.
Proper design of the database structure is the key to performance and normal development.
Errors in the structure of the database are bombs planted under the development of the project and further development.
All messages in one table. But in the database you will eventually end up with 10-20 or more tables with different metadata, without which the message texts have no meaning.
You don't have to do this.
Node.js - those who started their programming career with the front-end should be kept out of the way of making architectural decisions in large projects. Architecture for architects, js for front-end developers.
Websockets - chat involves pushing data from the server to the client, and for this task websockets has almost no real alternatives today. That is, all alternatives are crutches from the time before websockets.
First you need to decide why you need it. Then try to get rid of this dangerous idea.
You still have to redo it. This is the harsh reality of life.

Answer 2

It is better to use PostgreSQL or MySQL for storage, but no one forbids you to use NoSQl, you do not need to store it in it, but use it as a cache
. What to write on?
Whatever you know how to do and write
The table structure is something like this
chat
id|from_user|to_user
message
id|chat_id|date
content
id|message_id|text
attached
id|message_id|path|ext

Answer 3

The key question is: are group chats possible in such a system?
If so, then the following tables are needed:
thread (session/topic)
thread_user (list of users, status of each, number of messages, including unread ones, date when the user read messages in this session for the last time, etc.)
thread_post - the messages themselves (so that it is stored in one instance, and not one for each user)
thread_links - a bunch of messages from thread_post and users (so that you can delete messages without affecting other users).
Otherwise, I agree with nirvimel , you need to use the SQL solution and write on what is well known.

Answer 4

Briefly: by channel (one session = one channel) with a breakdown into types.