BRIN-индексы в PostgreSQL

Привет, Хабр!

Когда мы говорим о PostgreSQL и оптимизации запросов, большинство тут же вспоминает B-Tree индексы, GIN, GiST и так далее. Но вот BRIN иногда остается в тени, хотя в некоторых сценариях он способен творить чудеса с производительностью, особенно когда ваши таблицы размером с космический лифт, а места на диске жалко. Сегодня я расскажу, как именно работает BRIN.

BRIN расшифровывается как Block Range Index. Представьте обычный индекс, который пытается знать все и о каждом ряду. BRIN — это противоположность. Он хранит сводную информацию о группах страниц (так называемых «зонах»), а не о каждой строке. Проще говоря, вместо того, чтобы индексировать каждую запись, BRIN делит таблицу на блоки — например, каждые 32 страницы считаются одной зоной — и для этой зоны хранит минимальную и максимальную величину столбца (или другую обобщённую характеристику, в зависимости от класса операторов). Когда вы делаете запрос, BRIN быстро определяет, какие зоны могут содержать нужные данные, и «отбрасывает» все остальное. Неточно? Да, ведь внутри зоны придется все равно фильтровать строки. Но стоимость фильтрации намного меньше, чем полный проход по всей таблице.

Когда BRIN — хороший выбор?

1. Огромные таблицы. Допустим, есть таблица с миллионами, а то и миллиардами строк. Классический B-Tree индекс начинает раздуваться и занимает большое пространство, тогда как BRIN остается микроскопическим.

2. Естественная корреляция данных. Например, у вас есть столбец order_date в таблице заказов. Вполне логично, что старые заказы в начале таблицы, новые — в конце. Если вы ищете заказы за конкретный месяц, BRIN определит диапазон зон, где могут быть эти даты.

3. Экономия дискового пространства. BRIN-индексы невероятно компактны. Они хранят не все значения, а только агрегированные метаданные по зонам.

Минусы

• BRIN — неточный индекс. Он скажет: «Тут могут быть нужные данные!», но внутри зоны придётся проверить каждый кортеж.

• Если данные в столбце разбросаны хаотично, выгоды от BRIN будет мало. Он все равно не сможет эффективно отсеивать зоны.

Как создать BRIN-индекс?

Немного SQL:

CREATE INDEX idx_orders_date_brin ON orders
USING BRIN(order_date)
WITH (pages_per_range = 32);

Параметр pages_per_range определяет, сколько страниц попадает в одну зону. По дефолту это 128 страниц, но можно подобрать другое число. Чем меньше pages_per_range, тем точнее индекс, но больше размер индекса. Чем больше — тем компактнее индекс, но менее точен. И тут надо экспериментировать.

Есть еще неплохие опции. К примеру, можно использовать autosummarize через расширения, чтобы не вручную вызывать brin_summarize_new_values. Это обновит сводные данные автоматически.

BRIN-операторы: minmax, inclusion

BRIN опирается на так называемые классы операторов, чтобы понять, как сохранять сводные данные. Основных подхода два:

1. Minmax-классы: хранят минимальное и максимальное значение для зоны. Отлично подходят для типов, где есть естественный порядок: числа, даты, строки.

2. Inclusion-классы: для более сложных типов данных, например, геометрия (box), IP-сети или диапазоны. Индекс хранит «объемное» представление, например bounding box или объединение диапазонов.

Чем интересны inclusion-классы? Тем, что они могут вычленить информацию о более сложных структурах данных — например, определить, что у нас в зоне могут быть объекты, пересекающие некую область.

Таблица встроенных классов операторов BRIN

Для каждого типа данных свой класс.

Для большинства типов есть minmax-операции, а для более экзотичных структур — inclusion-операторы. То есть можно индексировать не только простые числа и строки, но и сложные типы. К примеру, box_inclusion_ops позволит искать объекты в определенной географической области, быстро отсекая зоны, где точно нет подходящих объектов.

Пример оптимизации выборки

Предположим, есть таблица заказов с кучей строк:

CREATE TABLE orders (
    id            BIGSERIAL PRIMARY KEY,
    order_date    DATE NOT NULL,
    customer_id   BIGINT NOT NULL,
    total_amount  NUMERIC(10, 2) NOT NULL
);

Зальем кучу данных:

INSERT INTO orders (order_date, customer_id, total_amount)
SELECT
    (DATE '2023-01-01' + (RANDOM()*365)::INT),
    (RANDOM()*1000000)::BIGINT,
    (RANDOM()*1000)::NUMERIC(10,2)
FROM generate_series(1,10000000) g;

Теперь создаем BRIN-индекс:

CREATE INDEX idx_orders_date_brin ON orders
USING BRIN(order_date date_minmax_ops)
WITH (pages_per_range = 64);

date_minmax_ops говорит: мы будем хранить минимальную и максимальную дату для каждой 64-страничной зоны.

При запросе:

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE order_date BETWEEN '2023-06-01' AND '2023-06-15';

План покажет, что PostgreSQL сначала обратится к BRIN-индексу, определит какие зоны могут содержать нужные даты, и пропустит остальное. Если данные приблизительно по дате отсортированы (например, по мере вставки), количество лишних зон будет минимальным.

Тонкости параметров

• pages_per_range:
  Чем меньше — тем точнее фильтрация (каждая зона меньше, значит меньше мусора), но индекс растет. Чем больше — тем меньше размер индекса, но хуже точность. Нужно тестировать на реальных данных.

• autovacuum и brin_summarize_new_values:
  BRIN не всегда автоматом обновляет сводные данные для новых зон. Можно вызывать:

  SELECT brin_summarize_new_values('orders');

  Или настроить autovacuum, чтобы этот процесс шел регулярно. В новых версиях можно использовать autosummarize опцию через расширения, чтобы автоматизировать это дело.

• Кастомные классы операторов:
  Если стандартных нет, можнго писать свои классы операторов. Это уже хардкор: придется реализовывать C-функции, описывающие, как агрегировать значения, как объединять зоны, как проверять пересечения с запросами. Но зато можно индексировать действительно экзотические структуры.

Заключение

BRIN не заменит B-Tree везде и повсюду. Но в тех случаях, когда данные упорядочены или имеют естественную корреляцию с их физическим размещением, BRIN может дать потрясающую производительность при минимальном размере индекса.

На этом всё. Желаю вам продуктивных поисков оптимальной стратегии индексации!

В завершение приглашаю на бесплатный вебинар «Строим архитектуру DWH по методу „борща“». Регистрация доступна по ссылке. А на странице курса вы можете посмотреть записи ранее прошедших вебинаров.