create table users (
 id serial primary key,
 unread_message_counts jsonb not null default '{}', -- { chat_id: count }

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

Легко... Миллион пользователей, каждый пишет по 100символьному сообщению каждые десять секунд.

Пример следующий. У чата есть 1000 сообщений. У пользователя "A" прочитано 900. Пользователь "B" удаляет сообщение под номером 700, к примеру, у чата становится 999 сообщений. Заходит пользователь и видит 99 новых сообщений, хотя их по факту 100, так удаленное сообщение уже было прочитано, но общее количество сообщений чата было уменьшено при удалении уже прочитанного сообщения

Что ищет? у вас и так бек постоянно работает с select ... from users

как будут проблемы - поле выносится в key/value в пару строк кода

Вы меня с топик стартером путаете. И читайте внимательно, там слово пишет а не ищет

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

Подождите, Вы же писали https://t.me/pgsql/313139
А хранение в redis и т.п. муре решения гарантируют не 100% счётчик.
Так что Вам всё-таки нужно?

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

Зачем Вам список? Почему Вы ходите кругами, мне вот любопытно (вроде же уже выяснили что индивидуального статуса "прочитано" сообщениям не нужно)? ;)

» индивидуального статуса "прочитано" сообщениям не нужно)?
при условии монотонного возрастания неких идентификаторов сообщений

я правильно вас понял?

Не надо же, нет? Если Вам действительно нужно, чтобы 1M непрочитанных сообщений "считались" быстро — можно применять всякие трюки с триггерами и частичными агрегатами... но неужели оно того стоит для этой задачи?!

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

зачем?

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

Нет, это совершенно не нужно.

> У чата есть 1000 сообщений. У пользователя "A" прочитано 900.

Значит, для него хранится id первого непрочитанного сообщения — пусть 901.

> Пользователь "B" удаляет сообщение под номером 700, к примеру, у чата становится 999 сообщений.
>  Заходит пользователь и видит

Что SELECT COUNT(*) FROM messages WHERE id >= 901 как был 100, так и есть.
Где проблема-то?

тут есть проблема
- если мы говорим о масштабе телеграма - хватит ли bigserial в перспективе нескольких лет эксплуатации?
- если есть понятие "удаление сообщения" - появляется сложный count()
а нужно ли вообще "удалять" сообщения?

count - медленная операция при большом кол-ве непрочитанных

Да что за предрассудки то 🤦‍♀️

> если мы говорим о масштабе телеграма - хватит ли bigserial в перспективе нескольких лет эксплуатации?

Да, bigserial хватит для чего угодно, в т.ч. масштабе всей планеты — я Вам советую посмотреть, что это за число, и выполнить некоторые прикидки. ;) Более того, PostgreSQL просто не сможет доработать до этого момента — это техническое ограничение (с которым никто никогда не столкнётся... по той же причине).

В остальном — автору вопроса виднее.