А при чём тут / чем поможет "переделать под курсори"?
Если же хотите помощи с оптимизацией запроса — https://t.me/pgsql/288632

Попробуйте вместо подзапроса с подсчетом числа написать count(*) over (), а то у вас таблица дважды считается. Пока влазит в память - всё ок, а дальше тормоза.

Но лучше, конечно, explain analyze смотреть

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

Но в этом запросе даже следов pagination нет... и при чём тут JSON, в таком случае?
И да, насчёт pagination в принципе: https://use-the-index-luke.com/no-offset

select json_agg(row_to_json(t)) as data, count(*) as count from backend.broadcast t where type_broad

Ну  на беке я буду передавать limit offset и хочу получить данние в json. Не дописал вконце лимит оффсет. Спасибо за ссилку

"Ненастоящие" запросы нет смысла оптимизировать.
Что нужно показать, чтобы Вам помогли с оптимизацией (реального запроса!), я уже писал.
И делать это нужно именно на тех данных, где есть проблема, конечно.

Спасибо

Вот казалось бы берём b-tree, добавляем в каждый узел счётчик - кол-во узлов, которое равно сумме кол-ва узлов по всем детям. А у листьев это число равно 1. Далее можно быстро делать offset по такому изменённому дереву. Почему так никто не делает?

Операция update требует изменения этого счётчика проходом до корня, что равно O(log n), а вы тут утверждали, что глубина не бывает больше 8-9 в реальной жизни, поэтому это вообще копейки.

как всё просто… а если они все в разных блоках, и эти блоки холодные?
к тому же, на каждый чих надо блокировать узлы на верхних уровнях, вы же типа счётчик должны обновить.
в результате у вас доступ к таблице с таким индексом полностью сериализуется.

вы с Ораклом работали с индексами на колонках с низкими кардинальнастями?
у них даже в доке есть предупрежение так неделать, ибо всё встаёт колом из-за блокировок

Потому что это никому настолько не нужно. Т.е. отношение затрат и выгод на практике никогда не в пользу такого решения.

Я думаю, что блокировки там не futex-ы. Тут легковесных достаточно. Ярослав подскажет, как они называются по умному (типо а-ля spinlock-и)

всем привет. Настраиваю pgpool по официальной доке, но в конце при подключении на виртуальный айпи пишет "No route to host", демон постгри и пгпул подняты и без ошибок. Конфигурировал по этой доке https://www.pgpool.net/docs/latest/en/html/example-cluster.html

в логах каждой ноды:
Mar 21 12:11:18 srv1 pgpool[9888]: 2021-03-21 12:11:18: pid 9891: LOG:  watchdog node state changed from [INITIALIZING] to [STANDING FOR MASTER]
Mar 21 12:11:18 srv1 pgpool[9888]: 2021-03-21 12:11:18: pid 9891: LOG:  our stand for coordinator request is rejected by node ":9999 Linux srv1"
Mar 21 12:11:18 srv1 pgpool[9888]: 2021-03-21 12:11:18: pid 9891: LOG:  watchdog node state changed from [STANDING FOR MASTER] to [PARTICIPATING IN ELECTION]

И мне кажется, что чем выше страница в дереве индекса, тем чаще она выбирается, тем более вероятно, что она в кеше, ведь кеши у нас lru

По оракл я не понял. Что не делать?

ага. и при этом в эту страницу упрутся все, кто захочет добавить/удалить ключ в любой части любого её поддерева.

вы никогда не писали такую вещь:
- создать MASTER счёт компании (провайдера)
- у каждого клиента сделать свой счёт
- любую операцию проводить используя MASTER-счёт в качестве одного из контр-агентов
- запустить обработку операций в 10 потоков

Ну ещё раз, там блокировок легковесных достаточно (ну в теории, не знаю всех премудростей PG), если у вас non-stop update/insert, то b-tree в таком кейсе не самое лучшее решение.

В PG где-то в ~12 появился storage API. Нету ещё движков LSM-tree?

Хмм... в какой "теории"? Я такой не помню, например (и неудивительно, как мне кажется — потому что см. https://t.me/pgsql/291614 ). ;)

> то b-tree в таком кейсе не самое лучшее решение.

Опять-таки, [citation needed]. Я к тому, что того, что кому-то "теоретически" кажется, что это так, недостаточно для того, чтобы так было в реальности.

> Нету ещё движков LSM-tree?

Кажется, нет. Но я специально не искал, а Вы?