Реализация эффективной системы “непрочитанных комментариев” счетчики

Я не полагаю, что типичный, нормализованный подход оставил бы Вас с неэффективными запросами. Предположим, что у Вас есть таблица article_comments с PK (article_id, comment_id) и другая таблица comments_seen_by_user с PK (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 индексируемых поисков для выполнения для показа данной страницы. Это не пот к современному DB. И я, вероятно, пропускаю еще лучшие планы запросов, которые мог бы найти PostgreSQL.

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

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

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

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

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

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

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