Не согласен. Кеш с нормальной инвалидацией делать сложно.

Я рад, что у вас есть возможность не соглашаться ))

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

ТС же не просит половину приложения ему переписать. Сделать вместо 5000 в единицу времени хотя бы 500 - и уже база вздохнет спокойно.

Да, все варианты конечно звучат как что-то очень не простое. С другой стороны допустить 5к запросов с одной страницы - чудовищно

тут не поспоришь

А, ещё не упомянули вариант отправлять селекты на слейв-реплики

Коллеги, я правильно понимаю, что в PostgreSQL нельзя создать CTE с продолжительностью жизни до конца транзакции и для этой цели надо использовать CREATE TEMPORARY TABLE ... ON COMMIT DROP?

Бэлла, варианты есть. Есть вариант создать VIEW, возможно содержащий больше данных, на него накладывать фильтры. Оно будет работать примерно как CTE, и вы его не будете никогда удалять.
Есть вариант написать всю транзакцию как цепочку CTE (если процедура написана на SQL, а не на plpgsql, то переписать наверняка к получится)

Нужно в середине транзакции посмотреть на количество строк результата SELECT и только затем выполнить INSERT части этого результата, попадающего под условие. То есть в конце транзакции смотреть на количество вставок будет не вариант. Насчёт VIEW подумаю, спасибо.

У меня тогда возник ещё один вопрос. Реализую сервис авторизации для пользователей на питоне, в момента входа пишу в БД данные пользователя о входе. Пока реализую для внутреннего пользователя - нагрузка должна быть совсем не большой. Но я подозреваю, что в дальнейшем этот же механизм будет использоваться для входа пользователей сайта. В результате нагрузка возрастёт в разы. Правильно ли так делать? Или нужно подключать что-то типа редиса и через определенные промежутки делать балковые вставки?

это скорее размазывание проблемы, чем ее решение

Так просят же затычку на месяц

ну а через месяц скажут "решение не помогло"
имхо это непрофессионально
надо разбираться с первопричиной
и она не в БД

ответ зависит от количественных метрик
в каких единицах меряете количество входов пользователей и сколько намеряли?

with
(select client_id, amount from paymnet where client_id = ? ) as A,
(select avg(amount) as _avg from A) as B,
(insert into stash select * from A where amount > _avg returning stash_id, client_id) as C
insert into log select * from C
— Все промежуточные вычисления можно добавить в цепочку. Я имел в виду такой стиль

Штуки/с. Сейчас это не более 5/с. Потом это может стать 1000/мс. Плюс остальные запросы

1000/мс?
мне что-то говорит о том, что вы чуть-чуть преувеличиваете

если у вас ДЕЙСТВИТЕЛЬНО такая нагрузка - вам нужен кластер стоимостью с Яндекс

Нет вы правда оцените. Обычно известно, сколько пользователей, и как часто они входят в систему. Я бы не стал этот этап оптимизировать.