BRIN индексы для больших таблиц

Таблица логов: 500GB, 2 миллиарда строк. B-Tree индекс на timestamp занял бы 40GB. BRIN занимает 5MB. При запросе 'дай логи за вчера' B-Tree читает индекс + страницы heap. BRIN читает 5MB метаданных и пропускает 99.9% блоков. Для append-only данных это революционная разница.

• **Timescale DB**: расширение PostgreSQL для time-series. Внутри использует BRIN + партиционирование. Обрабатывает триллионы строк метрик. Airbus, Siemens, Cisco используют для IoT-мониторинга
• **Cloudflare**: PostgreSQL с BRIN для хранения DNS-логов. 200+ миллионов запросов/сек. BRIN на timestamp позволяет запросы по диапазонам дат без 100GB B-Tree индексов
• **Snowflake / Parquet**: zone maps - тот же принцип min/max на chunk данных. BRIN реализует zone maps для PostgreSQL heap файлов

BRIN: индекс, который знает только минимум и максимум

2016 год. PostgreSQL 9.5. Новый тип индекса: BRIN (Block Range INdex). Идея предельно проста: не запоминать каждый ключ, а запоминать диапазон [min, max] для каждого диапазона физических блоков heap. Индекс на таблицу 1TB занимает несколько мегабайт. Это возможно только при одном условии: данные физически коррелируют с индексным ключом.

B-Tree хранит один указатель на каждую строку → N записей. BRIN хранит один [min,max] на каждые K блоков → N/K/8192 записей. Для таблицы с 100М строк и pages_per_range=128: B-Tree ~2GB, BRIN ~1MB. Разница в тысячи раз. Но BRIN работает только как фильтр блоков: читает все блоки где max >= query_val AND min <= query_val.

Аналог из мира аналитики: zone maps в колоночных хранилищах (Parquet, ORC, Snowflake). Каждый row group хранит min/max метаданные - при чтении пропускаются группы вне диапазона запроса. BRIN реализует тот же принцип в PostgreSQL для heap-таблиц. Именно поэтому ClickHouse (с primary index как sparse index) и Snowflake эффективны на time-series без B-Tree.

pages_per_range: настройка гранулярности BRIN

Ключевой параметр BRIN: `pages_per_range` (default: 128). Определяет, сколько heap-страниц покрывает одна запись в индексе. Меньше значение → больше записей в BRIN → лучше selectivity → больше читаемых блоков пропускается → но сам индекс больше. Trade-off: точность vs размер.

Correlation - ключевая метрика для BRIN. `pg_stats.correlation` для колонки показывает, насколько физический порядок строк в heap совпадает с логическим порядком значений. correlation = 1.0 → идеальная корреляция, BRIN максимально эффективен. correlation = 0 → случайный порядок, BRIN бесполезен. Для auto-increment id и timestamp типично correlation ≈ 0.95-1.0.

Time-series: классический кейс для BRIN

Таблица событий, метрик, логов - append-only, с монотонно возрастающим timestamp. Каждый INSERT добавляет строки в конец файла → корреляция timestamp с физическим расположением ≈ 1.0. BRIN идеально подходит: [min,max] каждого блок-диапазона не пересекаются, запросы по дате пропускают 99%+ блоков.

Партиционирование + BRIN - стандартная комбинация для time-series в production. Партиция ограничивает диапазон дат, BRIN внутри партиции обеспечивает эффективный поиск по дню/часу. Partition pruning убирает ненужные партиции, BRIN убирает ненужные блоки внутри партиции. Два уровня фильтрации блоков без B-Tree overhead.

BRIN vs B-Tree: когда что выбирать

BRIN - не замена B-Tree, а дополнение для специфического кейса. B-Tree оптимален для точечных запросов (WHERE id = 42), BRIN - для диапазонных запросов на физически-коррелированных колонках. Выбор определяется двумя факторами: correlation и selectivity запросов.

BRIN autosummarize и обслуживание индекса

BRIN-индекс нужно поддерживать. При INSERT новых блоков автоматически создаётся summary (если `autosummarize = on`, default в PG 10+). Но при больших batch-вставках сводка может отставать. `brin_summarize_new_values()` принудительно создаёт summary для несуммированных диапазонов.

BRIN и vacuum: VACUUM помечает мёртвые tuple, но не перемещает строки - correlation не восстанавливается. CLUSTER (блокирующий) и pg_repack (онлайн) физически переупорядочивают heap, восстанавливая correlation ≈ 1.0 и эффективность BRIN. Для append-only time-series таблиц CLUSTER не нужен: данные всегда вставляются в конец.

Связанные темы

BRIN работает на физическом уровне хранения данных:

• B-Tree индексы — Базовый индекс для сравнения: точечные запросы vs диапазонные на коррелированных данных
• Партиционирование — BRIN + партиционирование - стандартная комбинация для time-series таблиц
• Обслуживание индексов — BRIN требует понимания bloat, correlation, cluster для эффективного обслуживания

Ключевые идеи

• **BRIN = zone map**: [min,max] на pages_per_range блоков. Размер в тысячи раз меньше B-Tree, работает только при высокой correlation.
• **Lossy индекс**: BRIN всегда требует Recheck. Он пропускает блоки, а не строки - читает все строки из подходящих блоков.
• **pages_per_range**: уменьшение улучшает selectivity (меньше лишних блоков), но увеличивает размер индекса. Default=128.
• **Идеальный кейс**: append-only timestamp с correlation ≈ 1.0. Плохой кейс: случайные UPDATE или точечный поиск.
• **Партиционирование + BRIN**: два уровня фильтрации блоков для time-series без overhead B-Tree на терабайтных таблицах.

Вопросы для размышления

• BRIN на UUID колонке (random v4) будет бесполезен. Как проверить correlation до создания индекса и что делать если она низкая?
• Для таблицы с BRIN на timestamp делают VACUUM FULL раз в месяц. Это восстанавливает correlation? Есть ли альтернатива без блокировки?
• При batch insert 10М строк за раз autosummarize может не успеть. Как встроить brin_summarize_new_values в ETL-пайплайн?

Связанные уроки

• pg-15-gin-gist — Специализированные индексы GIN/GiST - предыдущая ступень в серии
• pg-12-btree — B-Tree - базовый индекс для сравнения с BRIN
• pg-34-partitioning — Партиционирование + BRIN - классическая комбинация для time-series
• pg-17-index-maintenance — После BRIN важно понять обслуживание индексов: bloat, vacuum
• pg-29-storage — BRIN работает с физическим расположением данных в heap-страницах
• alg-21-dp — BRIN summary per block range - аналог range minimum query в DP
• db-10-indexes-advanced