Информатика за индексами в Постгресе

[azsx]

Скажите, пожалуйста, планируется ли сделать команду reindex с опцией CONCURRENTLY?

[rdruzyagin]

Не могу дать точного ответа. Знаю лишь, что разработка патча, добавляющего поддержку, велась, но вроде как подзаглохла. В сети имеется некоторое количество всяких workaround'ов на эту тему, использующих drop + create concurrently

Насколько могу судить из этой дискуссии http://www.sql.ru/forum/941407/reindex-bazy-pod-nagruzkoy, вопрос внедрения фичи не стоит остро и народ, в целом, обходится вышеупомянутым обходным путем.

[azsx]

Ясно. Я делаю в иной последовательности, сперва create concurrently, а уже затем drop. Подскажите, пожалуйста, таблица около 20 миллионов строк, ежедневно 200-300 тысяч записей обновляется, 50-90 тысяч записей удаляется, бывает, что столько же добавляется, но обычно меньше 50 тысяч. В таблице несколько индексов, автовакуум настроен на 50 тысяч. Если я делаю создание нового индекса раз в месяц, то у меня индексы сокращаются раза в три, то есть вместо 1,5 гб, становятся 500 мб. Что я могу делать не так с индексами и автовакумом, если у других нет такой проблемы?
ps
Это хабр, может Вы ответа не знаете, вдруг кто-то другой знает, поможет новичку (мне то есть).

[kshvakov]

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

• Index Maintenance
• Checking for PostgreSQL Bloat
• pgsql-bloat-estimation
• Show database bloat

[rdruzyagin]

в тред вызывается hydrobiont )

[hydrobiont]

а покажите для начала

select name,setting  from pg_settings where category ~ 'Autovac'

[azsx]

«autovacuum»;«on»
«autovacuum_analyze_scale_factor»;«0.01»
«autovacuum_analyze_threshold»;«50»
«autovacuum_freeze_max_age»;«200000000»
«autovacuum_max_workers»;«1»
«autovacuum_multixact_freeze_max_age»;«400000000»
«autovacuum_naptime»;«60»
«autovacuum_vacuum_cost_delay»;«20»
«autovacuum_vacuum_cost_limit»;"-1"
«autovacuum_vacuum_scale_factor»;«0.01»
«autovacuum_vacuum_threshold»;«50»

[hydrobiont]

Для начала autovacuum_max_workers как минимум в 3 (может потом больше стоит поднять) и посмотрите какой процент времени они будут работать (из pg_stat_activity надо вывести график на мониторинг). Один воркер почти наверняка не успевает обработать таблицу на которой наступил autovacuum_vacuum_threshold или autovacuum_vacuum_scale_factor и когда он до нее добирается она уже существенно распухла.

[MacIn]

Про B-Tree рассказано мало, по верхам. Какое-нибудь АВЛ дерево тоже сбалансировано, и поиск в нем, с поправкой на кол-во дочерних узлов, происходит аналогично. Да почти в любом дереве так.
А вот почему применяется имеено Б-дерево (не только в постгре и вообще не только в реляционных БД)? Как выбирается размер узла, как он связан с размером блока, какова сложность поиска, сколько блочных операций ввода-вывода надо — это ключевые особенности B-Tree.
А ведь есть еще B+Tree.

[kmu1990]

Если упоминаются Леман с Яо, то имеются ввиду именно B+Tree (точнее их модификация, которую авторы называют B-link tree). Чистые без всяких модификаций B-деревья не такой уж частый зверь, поэтому часто, когда говорят B-дерево, то имеют ввиду B+дерево.

[dmrt]

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

Не совсем понял, может добавляет ключИ из разделенного узла, а не один ключ в родительский? А как же ссылка на второй узел новый?
Или как это все же происходит?

[Deosis]

На самом деле статья должна называться «Коротко о В-Tree и как оно используется в Постгресс»
В классическом В-дереве при разделении узла ключ-медиана переносится в родительский узел, а половина ключей больших медианы переносится в новый.
Хотелось бы узнать как постгресс обрабатывает граничные случаи?:
Значения повторяются
Значения меняются (как обновляется в таких случаях индекс)
Производится ли перестройка индекса для оптимизации поиска? И когда?
Какие ещё отличия от стандартного В-дерева?

[G-M-A-X]

Первичный автоинкрементальный ключ будет правосторонним деревом? :)

[JeStoneDev]

Удаление из B-Tree: обратная операция также интересна. Когда ключ удаляется из узла, Постгрес объединяет одноуровневые узлы, если это возможно, удаляя ключ из их родительского узла. Эта операция также может быть рекурсивной.

И кому верить? Егор Рогов из Postgres Professional говорит, что Postgres не умеет объединять страницы при удалении из B-Tree
пруф

[lubennikovaav]

Егору верить)
Постгрес никогда не объединяет страницы в B-tree. Если создать индекс на таблице и затем всё из неё удалить, пустой индекс будет занимать ровно столько же места, как и с данными. Вакуум просто пометит страницы как свободные для переиспользования при последующих вставках.
Такие дела.

[kromobis]

У вас получилась статья немного об индексах, немного о В-деревьях. Сконцентрировались бы на чем-то одном, а, например, про В-деревья вынесли бы в отдельную статью, было бы очень приятно почитать про них еще раз от настолько заинтересованного человека) Но все равно статья отлично, буду рад, если продолжите цикл про внутренности Postrges'a!

[rdruzyagin]

Мы стараемся подбирать разнообразные материалы. Может, у вас есть на примете хороший? Мы добавим его в свой wish-list и подготовим перевод для читателей Habrahabr!

[kmu1990]

Термин B-Tree является сокращением от английского “balanced tree” — «сбалансированное дерево»

на сколько я знаю, авторы не расскрывали, что значит B в названии (некоторые из возможных вариантов: Balanced — потому что идеально сбалансированное, Bayer — фамилия одного из авторов, Boeing — авторы в то время там работали)