Хочу сделать партицирование по полю которое имеет более 10 тысяч

id, на сколько это правильно иметь более 10к партиций?)

В общем суть, есть таблица с 250 миллионами записей, и два вариант как по мне партицирования
1. это по датам 1 месяц, тогда таблица будет содержать примерно 80-90 тысяч записей и около 115 таблиц
2. Сделать партицирование по relation_id тогда будет более 10 тысяч таблиц, но каждая таблица будет иметь со старта чуть больше 27 тысяч строк, и только через 5 лет в таблице будет около 500 тысяч

бы сделал по годам

слишком много записей в одной партиции будет, сейчас сделал по 2 месяца и запрос на получение записей за 3 месяца около 4-8 секунд, если его запустить после этого еще раз то менее секунды

какой запрос именно?

select * from (select max(id) as id, avg(price) as price, avg(volume_24h) as volume, max(datetime) as datetime, "relation_id" from (select *, ceil(EXTRACT(epoch from to_timestamp(datetime)) / EXTRACT(epoch from INTERVAL '5 min')) as interval_time from "table" where "table"."relation_id" in (33805, …., …, ..., …)) as "table" where "datetime" >= 1659357000 group by "interval_time", "relation_id") as "table" order by "table"."datetime" asc;

Вы просто запрос не хотите переписать?

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

3. Не делать партицыонирования, на паре сотен гигабайт оно крайне редко нужно.

Дажэ не план -- а всё из закрепа. И это пункт 0, так сказать для начала разговора за оптимизацыю запроса.

я уже давал как-то, не особо помогли, подумал что поможет партицировнание, таблица весит 30гб

что тогда в моем случаи нужно сделать или хоть в какую сторону копать, на стековерфлов люди пишут что базы по 500 миллионов и выборка меньше 1 секунды, а тут всего 230 мам 240 миллионов и уже 4-5 секунд

секции не нужны на таблице в 30ГБ

а когда их делают, я думал тут не в весе дело, а в количестве записей

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

Такое поведение -- это кэш, к индэксам это не имеет никакого отношэния.

он сбрасывается потому что озу ему мало, или потому что записи добавляются в огромном количестве?

Скорее второе. Но вообще -- он обновляется потому, что это его работа. Держать последнее в памяти.

Index Scan using cra_index_coin_id_and_datetime on coin_rate_avg (cost=0.56..5594.82 rows=4988 width=56) (actual time=6.601..4154.462 rows=5097 loops=1) как раз поиск данных по индексу)

Это не поиск, это полный скан индекса. Вам не надо партицирование, сделайте просто индексы под каждый из основных запросов

Да, стало из разряда… было 4-5 сек стало 1.5-3, но меня смущает количество индексов, выше сколько их я написал

1) Ну, вы пока что предъявили один запрос -- давайте всю пачку, если хотите, чтобы мы поковырялись со всей пачкой. 2) Странный результат. Покажыте explain (verbose, analyze, buffers, timing) -- разница в скорости должна быть сильно большэ.

Запрос один, в нем меняется только интервал, он всегда изместен мне ( есть набор интервалов) я могу на них все сделать индексы и запросы сразу начинают использовать index only

А-а. То есть, мой совет по поводу индэкса вы проигнорировали.