Подскажите такой вопрос: есть таблица, в которой поле "время" (не

важно, datetime или bigint) участвует в индексе и постоянно обновляется у каждой записи. из-за этого пухнет индекс, причем, fillfactor не помогает, так как через какое-то время индекс начинает пухнуть снова. что можно сделать с таким индексом ? в таблице примерно 300М записей. Варианты с секционированием не очень подходят, так как это уже часть секции еще большей таблицы и при более мелком разбитии на секции получается ну прям очень дофига таблиц, чего не хотелось бы. Есть ли какое-то решение данной проблемы или оно так исторически сложилось в постгресе и сделать ничего нельзя ?

Периодически делать рядом новый индекс и удалять старый. Только analyse не забыть перед удалением :) Или reindex concurrently.

этот вариант хорош, когда данных не очень много и он реально прокатывал до какого-то момента, но данных стало много и такой псевдо vacuum не очень быстро работает в результате.

А действительно обязательно включение этого поля в составной индекс? Вариант с отдельным индексом только по этому полю не решит проблемы?

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

Лучше всего обновиться на 14-ю версию когда она выйдет. Описание в блоке "Удаление индексных строк «снизу вверх»"

ну это еще когда оно выйдет, а работать должно "сейчас". там много чего вкусного есть.

Если fillfactor помогает ненадолго -- то, возможно, чаще запускать вакуум и постараться, чтобы не было сверхдолгих транзакцый. То есть вообще говоря — оно и так должно работать, страничка заполнилась старьём, отвакуумилась, индэкс тожэ поочистился. Почему этот шаблон у вас работает подозрительно неэффективно — надо бы разбираться. С другой стороны — докиньте лишний SSD под индэкс, делайте регулярный reindex concurrently — и это будет решэние задачи малой кровью, честно говоря. Тем более, что табличка небольшая, все части должны бы в первые сотни гигабайт умещатьсяю

сверхдолгих транзакций нет, там 5-6к обновлений в сек (update ... set dt=nnn,...), запросы на получение данных тоже не длинные, так как упираются в скорость доступа индекса, но все равно это 10-15сек сейчас. Но физическая таблица сама большая, примерно 90Гб, при том это это уже секция еще большей таблицы. индексы я раскидывал на разные диски - ssd (nvme) выпиливает быстро, а механика не такая быстрая на рандомном доступе. операции по reindex идут по 20-30мин, это долго, так как при этом диск сильно грузится и это так же тормозит другие операции. для меня подходящее решение разбивка на вообще мелкие секции, но я хз, как система прожует 15к файлов в одном каталоге. кто-нибудь релал базы где 5-6к таблиц (в секциях или просто, не важно) ?

IMNSHO: ну пухнет и пухнет, проблема-то в чём? Добавите дисков / RAM, в крайнем случае. Вы далее пишете, что используете UUID-ы — в общем-то, их использование практически гарантирует (разнообразные) проблемы с производительностью. Так что, если они Вам действительно нужны — придётся терпеть. А так — лучше бы увидеть \d таблицы, и те запросы, которые приводят индекс в такое состояние. Но, если действительно: > индексы - (group_id, bucket) и (group_id, dt) > Вставка идет по ключу (group_id, bucket) - обновляется dt и другие данные И, к тому же, dt обновляется на "далёкие" значения — это нормальное поведение, т.е. то, что этот индекс больше, чем свежесозданный, раза в 2-3, вполне ожидаемо (вот если больше, нужно уже смотреть подробнее).

> И, к тому же, dt обновляется на "далёкие" значения это на какие ? там dt время в мс с начала эпохи, т.е. обновляется чаще всего не на следующее значение (+1) и не на очень близкое. По поводу - "пусть пухнет" - я согласен, что поставить дисков и пусть работает, вопрос в том, что бы это распухло до какого-то объема не равного бесконечности и работало без особого присмотра дальше, так как у заказчика компетентного админа для постгреса нет и не будет. Делать vacuum или reindex можно, но это долгий процесс, который при работе сильно напрягает диск. Понятно, что там nvme, raid и т.д. вопрос закроют на какое-то время, но хочется прям вот решение "включил и забыл", только диски менять в рейде и все.

Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+--------------------+-----------+----------+------------------------+----------+--------------+------------- grp_id | uuid | | not null | | plain | | dt | bigint | | not null | | plain | | bucket | integer | | not null | '-1'::integer | plain | | val | double precision | | | | plain | | val1 | double precision | | | | plain | | val2 | double precision | | | | plain | | индексы по (grp_id, dt) , (grp_id, bucket) а чем uuid плох, что с ним такого плохого ? Ну для индекса да, не удобно, не u64.

> вопрос в том, что бы это распухло до какого-то объема не равного бесконечности Ну да, посыл был именно в этом. Т.е. стоит выяснить, останавливается ли "распухание" на приемлемых объёмах (допустим, в 3 раза от свежесозданного индекса) или нет. Если да, то "плясать" вокруг него с VACUUM и REINDEX малополезно — это "нормальное" состояние b-tree для таких данных и нагрузки, оно к нему сильно "стремится" и, соответственно, будет быстро приходить в это состояние (в любой СУБД, кстати).

> а чем uuid плох, что с ним такого плохого ? Ну для индекса да, не удобно, не u64. Тем, что это они случайны — это наихудший случай для b-tree (и корень проблем — погуглите, в benchmarks можно увидеть очень существенные провалы в производительности).

> Тем, что это они случайны — это наихудший случай для b-tree может быть не случайны, а у них "большой разборс" ? Ну будет не uuid, а int4, от 0 до 0x7FFFFFFF, это же не сильно улучшит работу b-tree ? Или для наиболее эффективной работы надо прям вообще делать последовательные числа в качестве id ?

Например, https://habr.com/ru/company/ozontech/blog/564520/

Нет, именно случайны, при чём тут "разброс"? Для b-tree последовательности вставок [1, 2, 3, 4, ...] и [1, 213, 5454, 6456] примерно одинаково "прекрасны", а вот случайные последовательности — примерно одинаково ужасны. И с последующее извлечение (которое чаще всего бывает примерно в том же порядке — потому что чаще всего используются "новые" данные) — не лучше.

хорошо, а как тогда b-tree поступает в случае с "композитным" индексом ? когда (uuid, dt), причем dt всегда набегает вперед, но не равномерно ?

Подскажите а какие недостатки вместо B-Tree для UUID4 использовать Hash индекс?

hash не умеет 2 поля индексировать

Но можно проиндексировать uuid || dt::text )

ну кстати да, вариант. минус в том, что в хеше будет храниться не бинарное представление uuid (16 байт), а строка. Как вариант сделать на сях функцию, которая будет делать то же самое, но результатом будет bytea .

Так выхлоп всё равно int32, хотя без тестов не скажешь, что будет быстрее.

я не знаю, как там реализовано в постгресе, но если там классический хеш в 2 уровня, то где-то надо хранить уже сами ключи (2й уровень), без хеширования, для уже перехода на кнкретную запись в таблице с данными. а если там будут hex строки - это не эффективно. Но надо попробовать, идея хорошая.

> Полученное число можно использовать как индекс обычного массива, куда и складывать ссылки на строки таблицы (TID) > ... > Страницы корзин (bucket page) — основные страницы индекса, хранят данные в виде пар «хеш-код — TID»; Читал наискосок, но вроде бы исходное значение не хранится вообще.

да, это действительно так - в индексе самих ключей нет, лишь массивы страниц относящихся к кешу. это хорошо. Спасибо за совет.

Смотря как именно используется такой индекс. Если речь о вставках, где для последних использованных UUID вставляются последовательно dt — это [существенно] лучше, чем просто вставка UUID-ов (вставки менее случайны). Если используемые UUID-ы произвольны — то примерно так же.

Обычные недостатки hash indexes (см. в документации). Опять-таки, проблема индексации UUID именно в том, что зачастую они образуют случайную последовательность (если используются как суррогатный ключ, например).