BRIN-индексы в PostgreSQL

Версия PostgreSQL 9.5 привнесла с собой новый вид индексов — BRIN (Block Range Index, или индекс блоковых зон).

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

Под блоковой зоной (Block Range) подразумевается набор страниц, физически расположенных по соседству в таблице. Для каждой такой зоны создается некий идентификатор, отвечающий за «место» этой зоны в таблице. Для лога это может быть дата создания записи. Поиск по такому индексу осуществляется с включением лишних записей, то есть выбираются все записи, входящие в блоковые зоны с идентификаторами, соответствующими запросу, но среди записей в этих зонах могут попадаться такие, которые на следующем этапе надо будет отфильтровать. Размер индекса при этом очень маленький, и он почти не нагружает базу. Размер индекса обратно пропорционален параметру pages_per_range, отвечающему за количество страниц на зону. В то же время, чем меньше размер зоны, тем меньше «лишних» данных попадёт в результат поиска. В общем, надо подходить к этому параметру с умом.

Индексы BRIN могут иметь один из нескольких встроенных классов операторов, по которым будет осуществляться разбивка на зоны и присвоение идентификаторов. Например, int8_minmax_ops применяется для операций сравнения целых чисел, а date_minmax_ops для сравнения дат. Полная таблица есть в официальной документации.

Тестирования BRIN-индекса (здесь и здесь), проводившиеся зарубежными коллегами, показывают очень хорошую производительность и экономность по сравнению с B-Tree.