Как работает функционал удаления сообщения в больших социальных сетях?

Question

[Twitt]

Берем в пример ВКонтакте, когда пользователь берет и удаляет несколько своих сообщений в диалоге с кем-либо, какой у них проходит SQL запрос? (Даже не сам ВК, а в высоконагруженных проектах как это лучше делать)?
DELETE FROM messages WHERE id = ?
или

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

Первый вариант может быть плох (как я думаю), скорее всего, потому что если сообщение удалится с таблицы, то те сообщения которые сверху от него, придется их опускать вниз, что может быть затратно по времени? Как такое делается в хайлоад проектах?
Хотя мне кажется у вк не происходит DELETE FROM т.к. у собеседника мои сообщения остаются, а удаляются только у меня. И где о такой теме лучше стоит почитать?

P.S. Правильно ли я понимаю, что таблица выглядит примерно таким образом у того же вк?
id | sender | receiver | message | status
33 | 1231 | 992123 | 'Привет' | delete_by_sender
Где значения sender, receiver - айдишники страниц

Answer 1

[Вячеслав Успенский]

Вариант 2 и тому две причины:
- фрагментация есть зло
- необратимых действий быть не должно в принципе

Что касаемо сообщений в двух ящиках то примерно так (в ваших терминах):
message_id, is_deleted, keeper_id, sender_id, receiver_id, message

Индекс по message строить беcполезно если у вас MySQL, у это СУБД нет FTS. Тут вам поможет elasticsearch или sphinx. Если же вы юзаете PostgreSQL то гуглите FTS и вперед)

Если сервис маленький то преждевременная оптимизация вам ни к чему. Однако помните что при создании внешних ключей должны быть индексы по полям на которые ссылаетесь и с которых ссылаетесь.
Я в таблице личных сообщений на достаточно крупном сервисе (более 500 млн сообщений) внешние ключи не ставил в принципе: сохранение консистентности данных не так важно. Если пользователей очень много можно пойти в сторону денормализации: отказаться от join и хранить имя отправителя/получателя в таблице сообщений.
На определенном этапе так же возникнет вопрос кластеризации и шардинга таблицы, но это уже при количестве записей ближе к 1 млрд

Если у вас будут объемы >1 млрд сообщений то я думаю тут уже будет разумен найм DBA, а пока YAGNI

Comments

[Twitt]

спасибо за внятное объяснение!
единственное - что такое keeper_id? Я думал это получатель, но receiver_id вы также указали

[Вячеслав Успенский]

Igor, на каждое сообщение заводим записи
От кого, кому, у кого храним.

Если просто то
Kwisatz, Twitt, Kwisatz
Kwisatz, Twitt, Twitt

Answer 2

[Дмитрий Дарт]

Они не удаляют сообщения на самом деле, они ставят пометку у сообщения что оно удалено. Это имеет определённые бонусы - не надо перестраивать индексы и т.п.

Comments

[Twitt]

а почему у собеседника остаются сообщения? как этого добиваются?

[Twitt]

MaxKorz, ну я думаю, что примерно так выглядит структура таблицы сообщений:

id | sender | receiver | message | status
33 | 1231 | 992123 | 'Привет' | delete_by_sender

Так что ли это выглядит, если отправитель удалил сообщение с id = 33? Значения sender и receiver конечно же ID страниц получателя и отправителя сообщения

[Дмитрий Дарт]

Igor, зашёл сейчас в ВК, не вижу там возможности удалить сообщения, есть возможность удалить весь диалог, или сущности типа постов, записей

[Twitt]

Дмитрий,
кликните на сообщение любое, и сверху будет такое меню

[Дмитрий Дарт]

Igor, да, всё верно, у одного удаляется, у другого остаётся

33 | 1231 | 992123 | 'Привет' | delete_by_sender

не думаю, потому что есть чаты на 100 человек и что тогда? Тогда что бы так было у каждого пользователя должен быть поток входящих и отправленных сообщений. И если я своё отправленное удаляю, то у всех остальных во входящих-то оно остаётся. С письмом по электронной почте полная аналогия получается, я могу удалить но только из своего ящика. Выходит что копия сообщения вероятно хранится у каждого...

[Twitt]

MaxKorz, Ну тут непонятно, у вас стоит is_deleted bool.
Что будет значить is_deleted если удалено сообщение отправителем? true? А если удалено получателем? false тут не поставим же

[Сергей Соколов]

Индекс как раз пересроить придётся: по полю is_deleted индекс нужен, т.к. это ускоряет выборку сообщений.

[Дмитрий Дарт]

MaxKorz, такая табличка не даёт ответа на вопрос о коллективных чатах

[Дмитрий Дарт]

Сергей Соколов, думаю индекс перестраивается но не в общей таблице а в виртуальной таблице конкретного пользователя или вообще в конкретном диалоге

[Twitt]

Сергей Соколов, а индекс в данной таблице не нужно делать по полю message? По нему в "Поиск сообщений" очень часто ищут.

[Twitt]

Дмитрий, и вопрос, foreign keys должны быть два таких:

foreign key (receiver_id) references on users(id),
foreign key (senter_id) references on users(id)

Верно?

[Дмитрий Дарт]

Igor, сложно сказать на таких больших объёмах вообще другие принципы построения баз могут быть, например от foreign key могут отказаться из-за производительности, потому что пока база сама проверяет на консистентность всё это с миллиардом записей там умрёт уж всё давно