Структура БД для хранения комментариев?

Question

[Николай Кокоулин]

Дано:
650 млн коментариев (одна таблица)
Id, text, time, rating, related_img, related_link связь с пользователем(20млн записей), статьей(140тыс записей), категорией(50 записей), родительским комментарием(650 млн)
Пиковая нагрузка:
Чтение 50 rps
Запись 7rps
Возможность оставить комментарий к новости исчизает спустя 1 месяц

Задача: сделать нормальную структуру бд для хранения этого

Стек:
pgsql
Symfony 3.2
Doctrine
Redis

Comments

[BoShurik]

Связь со статьей и категорий - это возможность оставлять комментарии либо к тому, либо к другому?

[Николай Кокоулин]

BoShurik, связь это foreign key

[Максим Федоров]

Николай Кокоулин, имеют в виду категория такая же как пост, то есть комментарии к ним наравне?

[Николай Кокоулин]

Максим Федоров, нет, комментарий возможен только к новости (связь коммента напрямую к категории нужна, сделать комvент->новость->категория нельзя)

Answer 1

[Николай Кокоулин]

Всем спасибо за ответы, принято решение хранить комменты в редисе свяжие целиком, старые только указание на место хранения

Comments

[xmoonlight]

непонятно: чем это поможет в скорости выборки/записи и как это связано с вопросом?!

[AlikDex]

xmoonlight, если сайт новостной статейник, то в основном будут читать новости 1-3 дня с текущего момента. Остальные просто балластом висят и дуют индексы просто так.

Сам посчитай, 650млн запсией индекс по целочисленному иду (4 байта) примерно 2.5гб. При скорости чтения жесткого в 200-250мб\с это будет 10-12 секунд. Но насколько я понял у них там используются полиморфные связи в таблицах, а значит индекс составной, т.е. еще больше размером. К томуже есть джойны(древовидные комменты). И вот вопрос, зачем постоянно гонять такой объем данных?

[Николай Кокоулин]

xmoonlight, выборка из редиса сильно быстрее чем из классической бд в виду того, что все в оперативной памяти, по этому за последнюю неделю храним в Редисе, далее переносим в постгрес таблицу по номеру недели и храним в рэдисе только id и имя таблицы

[xmoonlight]

Николай Кокоулин, AlikDex, вырубят энергию и все новые комменты - уничтожатся, т.к. они были только в памяти.
PS: как работает редис - я знаю...
Проще, как я уже писал в своём ответе, поделить огромный массив данных на таблицы-кластеры: по дням, неделям, категориям.
Как лучше кластеризировать - смотреть исходя из бизнес-логики и запрашиваемых данных.
Выборка и запись - будет происходить быстрее, а новые комменты - всегда будут помещены на носитель.
Кратко:
Запись/изменение в базе - редис+хранилище(DB)
Чтение и выборка - редис. По отсутствию в кеше - тянем из хранилища и кешируем на редисе, чтобы повторный подобный - в базу не лезть.

[Николай Кокоулин]

xmoonlight, при логике которую мы начали писать в случае отключения энергии сразу в 2 разных ДЦ мы потеряем данные за 1 секунду (10-14 комментариев) в худшем случае

[xmoonlight]

Николай Кокоулин, а-а.. ну если не критично - тогда ок.

[Николай Кокоулин]

xmoonlight, вероятность потери минимальна, а при таком выигрыше в скорости это не столь критично

[xmoonlight]

Николай Кокоулин, ну логично, да.

Answer 2

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

Выглядит как заказ на работу

Вопрос конкретно в чем?
- В связке родителя/потомка? parent_id
- В выборке дерева? with recursive
- Хотите отделить записи по времени? шардинг с условием

UPD:
раз
два
три

Comments

[Николай Кокоулин]

Хочу ускорить выборку.
Связи есть
Выборка дерева тоже есть
Отделение по времени не ускорит выборку в сравнении с грамотным реплецированием

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

Николай Кокоулин, шардинг вполне себе ускорит. А причем тут простите реплецирование?

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

раз
два
три

Answer 3

[AlikDex]

разбить на 2 таблицы? 1 с новостями месячной давности, остальные в архивную.

Comments

[Николай Кокоулин]

Звучит хорошо, за исключением того, что нужно для начала индифицировать когда оставлен комментарий

[Антон Середний]

Николай Кокоулин, поле time - не подходит?)

[Николай Кокоулин]

Антон Середний, как узнать из какой таблицы тянуть комментарий из старой или новой

[AlikDex]

Николай Кокоулин, по дате статьи? вроде же согласно возрасту статьи запрет на комменты идет?
Поидее комментарии таких статей можно хранить в каком-нибудь готовом жсон дереве. Т.е. ид статьи | и дерево комментов в жсоне.
Если в комментарии гарантированно ничего добавляться\изменяться не будет и они просто хранятся.

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

[Николай Кокоулин]

AlikDex, хранить комментарии в жсоне это какой-то изврат честно говоря, я этот жсон парсить буду кучу времени

дата статьи 05.01.17
дата первого коммента 05.01.17
дата последнего коммента 05.02.17
первый коммент попадает в таблицу хранения коментов для января
последний попадает в таблицу хранения для февраля

[AlikDex]

Николай Кокоулин, м.б. не понятно объяснил.
Готовое дерево комментов хранится в жсоне. Кто смотрит старую статью просто подтягивает это дерево целиком и там рендерится либо хтмлка, либо на стороне клиента. Без лишних телодвижений по базе.

id статьи | json коменты, 2 колонки в базе. Но это только для старых, куда гарантированно никто больше комментарий не напишет. Т.е. просто хранение.

[Николай Кокоулин]

AlikDex, а, да, так лучше, концепт интересный, но увы коменты спустя месяц могут быть изменены: рейтинг, редактирование для модераторов, удаление

Answer 4

[xmoonlight]

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

3. Можно добавить кросс-связи, если выборка комментариев должна быть не только со стороны поста, но и со стороны конкретного пользователя, категории и т.д.

Comments

[Николай Кокоулин]

Сейчас так и реализовано, это не оптимально

[xmoonlight]

Николай Кокоулин, сделайте таблицы-кластеры по 100-500к комментов в каждой или по каждому разделу (1-2 уровня) - свою отдельную таблицу.

[Николай Кокоулин]

xmoonlight, вариант неплох спасибо