How does the function of deleting a message work in large social networks?

Question

Database

Let's take VKontakte as an example, when a user takes and deletes several of his messages in a dialogue with someone, what kind of SQL query is they running? (Not even VK itself, but in highly loaded projects, how best to do this)? or
DELETE FROM messages WHERE id = ?

UPDATE messages SET status = 'deleted' WHERE id = ?

The first option can be bad (I think), most likely because if the message is deleted from the table, then those messages that are on top of it will have to be moved down, which can be time consuming? How is this done in highload projects?
Although it seems to me that VK does not have a DELETE FROM. my interlocutor keeps my messages, but only me is deleted. Where is the best place to read about this topic?
PS Do I understand correctly that the table looks something like this for the same VK?
id | sender | receiver | message | status
33 | 1231 | 992123 | 'Hi' | delete_by_sender
Where sender, receiver values ​​are page IDs

Answer 1

Option 2 and there are two reasons for this:
- fragmentation is evil
- there should be no irreversible actions in principle
. As for messages in two boxes, something like this (in your terms
)
: if you have MySQL, this DBMS does not have FTS. This is where elasticsearch or sphinx will help you. If you use PostgreSQL, then google FTS and go ahead)
If the service is small, then premature optimization is useless to you. However, remember that when creating foreign keys, there must be indexes on the fields to which you refer and from which you refer.
In principle, I didn’t set foreign keys in the personal messages table on a fairly large service (more than 500 million messages): maintaining data consistency is not so important. If there are a lot of users, you can go towards denormalization: refuse to join and store the name of the sender / recipient in the message table.
At a certain stage, the issue of clustering and sharding the table will also arise, but this is already with the number of records closer to 1 billion
.

Answer 2

They don't actually delete posts, they just mark the post as deleted. This has certain bonuses - no need to rebuild indexes, etc.