Здравствуйте.

Есть таблица сообщений

id author_id recipient_id message date_created

известен id текущего пользователя (допустим, 123)

необходимо получить список последних сообщений в диалогах пользователя 123 с другими людьми (они могут быть как входящие, так и исходящие) по всем людям, с которыми он общался (кому писал он или кто писал ему), с сортировкой по дате от более свежих к более старым... то есть по каждому пользователю по одному (последнему по дате) сообщению (ему или от него)...

Друзья советуют не заморачиваться с реляционными СУБД и использовать Redis, но мне хочется на PostgreSQL. Подскажите, как наиболее оптимальным образом это сделать?..

Этот запрос используется для генерации главной страницы раздела сообщений. (Для стартапа, где отправка сообщений - не самое важное, так что, заходить в этот раздел, конечно, будут, но, скорее, редко чем часто.)

Главный раздел сообщений фейсбука сделан на базе примерно аналогичного (по сути) запроса - показывается список последних диалогов пользователя. (разумеется, используются другие технологии).

Я думаю, что для начала возьму запрос попроще, на PostgreSQL, без редиса - это проще поддерживать, если потом будут проблемы с ним - оптимизирую (для начала через https://wiki.postgresql.org/wiki/Loose_indexscan , если и это не поможет - буду рассматривать другие решения, но в целом реляционные субд (и порядок данных, который они несут ) мне нравятся больше).

Вариант с Redis'ом мне не нравится из-за усложнения архитектуры проекта (будет сложнее поддерживать и тестировать), друг предлагал сделать вот так:

Большое спасибо за запрос и подсказку про https://wiki.postgresql.org/wiki/Loose_indexscan !

Друзья вот так предлагают решать проблему:

автор[15/06/14 20:54:33] atari: ведешь счетчик сообщений инкрементальный
когда пользователь А добавляет сообщение пользователю Б,
а) получаешь id поста через Incr
б) создаешь пост куда-нить в ключ post:{id}, куда кладешь в каком-то формате все, что надо
в) помещаешь в очереди сообщений каждому пользователю через zAdd id поста
[15/06/14 20:55:01] atari: г) помещаешь в очередь всех диалогов пользователя id того же поста
[15/06/14 20:55:28] atari: для пункта В ключ может выглядеть, скажем, user:messages:{A}:{B}
[15/06/14 20:55:37] atari: и user:messages:{B}:{A}
[15/06/14 20:56:03] atari: в редисе есть сортированный список
[15/06/14 20:56:27] atari: ты в него можешь положить ззначение с "весом", по которому значения и будут сортироваться
[15/06/14 20:56:56] atari: то есть, создал пост с id 666, и кладешь его в ключи из пункта в) и г) с весом, равным времени сообщения.
[15/06/14 20:57:18] atari: после чего достаточно с помощью команды zrevrange 0 30 получить список из последних 30 свежих сообщений
[15/06/14 20:57:34] atari: а, имея список айдишников, получить по ним все посты уже через get
[15/06/14 20:58:02] atari: я устал, поэтому, может, плохо формулирую, недостаточно емко, но ты, думаю, разберешься
[15/06/14 20:58:04] Alexander A. Ovchinnikov: то есть в редис как я понял принято усложнять добавление чтобы получить быстрый селект, да?
[15/06/14 20:58:24] Alexander A. Ovchinnikov: ну я примерно понял
[15/06/14 20:58:25] atari: ну да, вплоть до того, что, например, твит рассовывается ВСЕМ подписчикам в очередь
[15/06/14 20:58:34] atari: 20 тысяч записей, например
[15/06/14 20:58:38] atari: и это достаточно быстро происходит
[15/06/14 20:58:43] atari: зато они читаают в момент
[15/06/14 20:58:49] Alexander A. Ovchinnikov: это достаточно непривычно, конечно
[15/06/14 20:58:52] atari: ну да, именно
[15/06/14 20:59:02] atari: для реляционной базы этосопряжено с немалыми накладными расходами
то есть, значимые команды
http://redis.io/commands/incr - для получения id
http://redis.io/commands/zadd - для записи в сортированный список
http://redis.io/commands/zrevrange - для получения значений из списка, сортированных по убванию
http://redis.io/commands/mget - для получения всех постов при наличии id
[15/06/14 21:03:28] atari: но если решишься делать таки SQL - мне кажется, проще завести промежуточную таблицу с "последними постами"
[15/06/14 21:04:21] atari: а то, конечно, запрос возможен, конечно, SELECT max(post_id) FROM posts WHERE recipient=222 GROUP BY sender_id, потом это в качестве субселекта
[15/06/14 21:04:26] atari: но как-то дико уже
[15/06/14 21:05:11] Alexander A. Ovchinnikov: тут сложность в том
[15/06/14 21:05:19] atari: а так
CREATE TABLE last_messages (
recipient_id int,
sender_id int,
created datetime,
post_id int,
primary key (recipient_id, sender_id)
)
[15/06/14 21:05:26] atari: и обновляешь через replace при каждом сообщении
[15/06/14 21:05:27] Alexander A. Ovchinnikov: что у меня recipient=222 OR author=222
[15/06/14 21:05:36] atari: а ты можешь без OR
[15/06/14 21:05:41] atari: OR - это геморрой
[15/06/14 21:05:47] atari: я просто делаю ДВЕ копиис ообщения
[15/06/14 21:05:51] atari: у меня
[15/06/14 21:05:57] atari: recipient_id
from_id
to_id
[15/06/14 21:06:22] atari: и вот для сообщения одного from_id и to_id обозначают что и должны
[15/06/14 21:06:34] atari: а recipient_id в двух разных копиях равен from_id и to_id
[15/06/14 21:06:46] Alexander A. Ovchinnikov: мм хитро)
[15/06/14 21:06:47] atari: таким образом я могу стереть твое сообщение
[15/06/14 21:07:01] atari: а у тебя в исходящих оно сохранится
[15/06/14 21:07:09] atari: выборки получаются достаточно простыми
[15/06/14 21:07:20] atari: накладные раскходы - удвоение используемого пространства, но это это, кажется, не так страшно
[15/06/14 21:07:31] atari: потому что OR реально ебет мозг
[15/06/14 21:07:45] atari: утяжеляя запрос и отменяя все индексы
[15/06/14 21:08:13] Alexander A. Ovchinnikov: интересно это только для mysql так?
[15/06/14 21:08:21] atari: не знаю, кстати
[15/06/14 21:08:26] atari: может, другие базы справляются лучше
[15/06/14 21:08:40] atari: может, даже mysql научилась справляться лучше - у меня данные минимум двухгодичной давности
[15/06/14 21:08:42] Alexander A. Ovchinnikov: а как бы ты написал запрос с OR?
[15/06/14 21:08:48] Alexander A. Ovchinnikov: с группировкой
[15/06/14 21:08:54] Alexander A. Ovchinnikov: такое реально?
[15/06/14 21:09:03] atari: ну.. эээ
[15/06/14 21:09:14] atari: никак, наверное =)
[15/06/14 21:09:18] atari: можно через UNION =))
[15/06/14 21:09:32] atari: субселект из двух запросов через юнион
[15/06/14 21:09:32] atari: =)