Внедрение эффективной системы счетчиков "непрочитанных комментариев"

Question

Я пытаюсь найти оптимальное решение для следующей проблемы: необходимо создать базу данных (на основе postgres), систему триггеров и счетчиков, которая сформирует систему для эффективного запроса, обновления и хранения информации о "сколько непрочитанных комментариев существует в каждой статье (или записи в блоге, или что-то в этом роде), отображаемой на странице".

Каждое решение, которое приходит в голову, имеет ряд серьезных недостатков, будь то запрос, хранение или обновление. Т.е. требуется слишком много памяти, слишком много обновлений или слишком дорогие запросы.

А как насчет вашего опыта? Может быть, уже есть хорошее решение для такого рода проблем?

Answer 1

Я хотел бы сохранить схему как можно более простой, поэтому запросы будут максимально простыми. Это обычно также имеет самые низкие требования к хранению. Конечно, установите индексы для поддержки этого запроса.

Следующий шаг: измерить производительность! "Измерять - значит знать". Какое время отклика? Какая нагрузка на сервер? Пока производительность приемлема, сохраняйте схему и запрос простыми. Не жертвуйте ремонтопригодностью, если это не является абсолютно необходимым: ваши преемники будут благодарить вас за это позже.

Если производительность действительно является проблемой, посмотрите на функциональность кэширования платформы, которую вы используете для своего приложения. НЕ выполнение запроса всегда быстрее, чем выполнение оптимизированного.

Answer 2

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

Answer 3

Я не верю, что типичный нормализованный подход оставит вас с неэффективными запросами. Предположим, у вас есть стол article_comments с ПК (article_id, comment_id) и другой стол comments_seen_by_user с ПК (user_id, article_id, comment_id), Все, что вам нужно сделать, для каждой статьи, перечисленной на странице:

SELECT count(*) FROM article_comments ac
WHERE article_id = ?                -- Parameter
AND NOT EXISTS (
    SELECT 1 FROM comments_seen_by_user csbu
    WHERE csbu.user_id = ?          -- Parameter
    AND   csbu.article_id = ac.article_id
    AND   csbu.comment_id = ac.comment_id
)

Если вы показываете 20 статей на странице, вы будете выполнять вышеуказанный запрос 20 раз, и каждый прогон будет использовать индекс для извлечения, скажем, 10-20 строк из article_commentsи тест подзапроса это просто еще одно сканирование индекса на comments_seen_by_userТаким образом, в целом у вас может быть 20 * (20 * 2) = 800 проиндексированных поисков, которые нужно выполнить, чтобы показать данную страницу. Это не пот, для современной БД. И я, вероятно, упускаю из виду даже лучшие планы запросов, которые может найти PostgreSQL.

Вы пробовали это, и нашли, что производительность хочет? Если это так, то я думаю, что вы не VACUUMчерез некоторое время В противном случае у меня могут быть неверные оценки количества статей на странице или комментариев на статью - пожалуйста, уточните подробности в этом случае.

Answer 4

Я буду вторым ответом j_random_hacker, только я бы не стал сохранять article_id в таблице comments_seen_by_user, так как comment_id должен быть глобально уникальным для каждого комментария. Кроме того, трехмерные (и в меньшей степени двумерные) индексы в PostgreSQL по-прежнему медленные, поэтому старайтесь их избегать.

Нет хорошего способа обойти таблицу значений user_id, comment_id для хранения информации о прочитанных комментариях, просто убедитесь, что у нее есть уникальный индекс. Несколько 10 миллионов строк в такой таблице не представляют никакой проблемы для PostgreSQL, поскольку он может хранить индекс в памяти. Вы можете отслеживать размер индекса (количество страниц 8 КБ на диске) с помощью запросов к системным таблицам:

select relname,relpages from pg_class where relname='comments_seen_by_user_pkey';

Answer 5

Я бы согласился пойти на нормализованный подход и посмотреть, сработает ли он. Обычно я должен. Тем не менее, вы также можете использовать INSERT-триггер в таблице 'comment', который обновляет счетчик комментариев в базовой (то есть статье) таблице. Это зависит от профиля использования для этого веб-сайта: если комментарии в основном читаются (по сравнению с добавлением комментариев), затраты на подход, основанные на триггере, должны быстро амортизироваться. В противном случае сайт с высокой загрузкой комментариев может снизить производительность.

Я хотел бы перейти к простой, нормализованной структуре таблиц и добавить другую оптимизацию позже, когда у вас будет разумный профиль использования.