Калькулятор размера SQL индекса

Как пользоваться калькулятором

Примеры расчёта

Формулы расчёта

Калькулятор использует следующую математическую модель, основанную на внутреннем устройстве страниц PostgreSQL (8 КБ):

Overhead по типам индексов: B-tree — 8 байт, Hash — 4 байта, GiST — 16 байт, GIN — 20 байт, BRIN — 8 байт (плюс 4 байта CTID-указателя для каждого). Заголовок страницы — 28 байт (PageHeaderData).

Пошаговое объяснение

Расчёт проходит в четыре этапа. Сначала определяется размер ключа — сумма байтовых размеров всех столбцов индекса. Для VARCHAR(N) это N + 4 байта (4 байта хранят фактическую длину строки), для INT — ровно 4 байта.

Затем добавляется overhead типа индекса. B-tree требует 8 байт на внутренние указатели дерева плюс 4 байта на CTID (физический адрес строки в куче). GiST и GIN имеют больший overhead из-за сложной внутренней структуры.

На третьем этапе вычисляется ёмкость страницы. Стандартный блок PostgreSQL — 8192 байта. Вычитаем 28 байт заголовка, умножаем на fillfactor. Получаем полезное пространство, доступное для записей индекса.

Наконец, определяется количество страниц делением общего числа строк на количество записей на странице с округлением вверх. Умножение на 8192 даёт итоговый размер индекса в байтах.

Где применяется

• Планирование дискового пространства — оценка размера индексов перед созданием новой таблицы или миграцией, чтобы зарезервировать достаточно места на SSD/HDD.
• Оптимизация производительности — понимание, поместится ли индекс в оперативную память (shared_buffers). Индекс, целиком попадающий в кеш, работает в разы быстрее.
• Сравнение типов индексов — выбор между B-tree и BRIN для хронологических данных. BRIN может быть в 100 раз компактнее на миллиардах строк.
• Аудит существующей базы — расчёт ожидаемого размера и сравнение с реальным через pg_total_relation_size() для выявления раздувания (bloat).
• Проектирование составных индексов — оценка стоимости добавления ещё одного столбца в индекс: каждый дополнительный столбец увеличивает размер записи и общий размер индекса.
• Облачные затраты — в AWS RDS или Google Cloud SQL размер индексов напрямую влияет на счёт за хранилище. Точная оценка помогает избежать перерасхода.

Важные нюансы

• Оценка, а не точное значение. Реальный размер зависит от множества факторов: сжатие TOAST, выравнивание данных, фрагментация после UPDATE и DELETE. Калькулятор даёт оценку с погрешностью ±10-15%.
• NULL-значения не занимают место в индексе (для B-tree). Если 80% строк имеют NULL в индексируемом столбце, реальный размер индекса будет значительно меньше расчётного. Учитывайте долю NULL при оценке.
• Fillfactor ниже 100% ускоряет INSERT, но увеличивает размер. Значение 70% означает, что 30% каждой страницы резервируется под будущие вставки — индекс будет физически больше, но избежит дорогостоящих расщеплений страниц.
• Для VARCHAR учитывайте фактическую среднюю длину. Если столбец объявлен как VARCHAR(255), но средняя длина строки — 20 символов, используйте в расчётах 24 байта (20+4), а не 259. Это даст гораздо более точный результат.
• GIN-индексы могут быть неожиданно большими. Один массив из 10 элементов в GIN порождает 10 отдельных записей в индексной таблице. Для массивов и JSONB реальный размер GIN-индекса может превышать размер самой таблицы.
• BRIN работает иначе. Он хранит диапазоны значений для блоков страниц, а не отдельные строки. Размер BRIN-индекса почти не зависит от количества строк — только от количества блоков таблицы и параметра pages_per_range.

Частые ошибки

• Путаница байтов и символов. VARCHAR(100) в UTF-8 может занимать до 400 байт на строку (кириллица — 2 байта на символ, эмодзи — 4). Калькулятор считает в байтах. Убедитесь, что переводите символы в байты корректно.
• Игнорирование составных индексов. Индекс на (city, street, house) — это не три отдельных индекса, а один с суммарным размером ключа. Ошибка: считать размер каждого столбца отдельно и складывать размеры трёх разных индексов.
• Завышенные ожидания от fillfactor=100%. Полностью заполненные страницы быстро фрагментируются при любых вставках. Для активно обновляемых таблиц fillfactor=100% приводит к падению производительности и раздуванию индекса.
• Пренебрежение overhead-ом. Разница между 8 байтами ключа и 20 байтами записи индекса (с overhead) — это 2,5-кратная разница в размере. Новички часто забывают про CTID и внутренние указатели.
• Расчёт на пиковое количество строк без запаса. Если таблица вырастет с 1 млн до 5 млн строк, индекс вырастет пропорционально. Закладывайте запас 20-30% при планировании дискового пространства.
• Использование одного типа индекса для всех задач. B-tree — универсальный, но неоптимальный для полнотекстового поиска (нужен GIN) или геоданных (нужен GiST). Ошибка выбора типа индекса ведёт к неадекватному размеру и низкой производительности.

Ответы на частые вопросы

Почему мой реальный индекс больше расчётного? Скорее всего, в таблице много «мёртвых» строк после UPDATE/DELETE, и индекс раздут. Выполните VACUUM или REINDEX для восстановления размера. Также проверьте актуальность статистики через ANALYZE.

Какой fillfactor выбрать для таблицы с частыми вставками? Для активно пополняемых таблиц (логи, события) рекомендуем fillfactor 70-80%. Это замедлит рост индекса и уменьшит фрагментацию. Для статических архивных таблиц можно использовать 100%.

BRIN или B-tree для временных рядов? Для данных, где строки вставляются последовательно по времени (события, метрики), BRIN даёт выигрыш по размеру в 50-200 раз при сопоставимой скорости чтения диапазонов. B-tree оправдан, если нужны точечные запросы.

Сколько столбцов можно включать в составной индекс? Технически — до 32, практически — не более 5-7. Каждый дополнительный столбец увеличивает размер индекса и замедляет вставки. Составной индекс на 10 столбцов размером с таблицу — это антипаттерн.

Влияет ли порядок столбцов в составном индексе на размер? На размер — нет. Сумма байтов та же. Но порядок критичен для производительности: индекс используется только если условия в WHERE начинаются с первого столбца индекса (правило левого префикса).

Можно ли использовать калькулятор для MySQL? Да, с оговорками. MySQL/InnoDB использует страницы по 16 КБ (вместо 8 КБ) и кластерный индекс (PRIMARY KEY включён во все вторичные индексы). Для MySQL умножайте результат на 1,5-2 для вторичных индексов.

Источники и справочные данные

Расчёт основан на официальной документации PostgreSQL (раздел «Database Page Layout», статьи 68.1-68.4), исходном коде PostgreSQL (файл bufpage.h, структура PageHeaderData — 24 байта + 4 байта выравнивания = 28 байт), а также исследованиях производительности индексов (Use The Index, Luke!). Overhead для B-tree взят из статьи «B-tree index internals» (PostgreSQL Wiki), для GiST/GIN — из документации PostgreSQL, раздел «GiST Indexes» и «GIN Indexes». Размеры типов данных соответствуют pg_type.typlen и документации по типам данных PostgreSQL 16.

SQL-индексы и их размер: полное практическое руководство

Зачем вообще знать размер индекса?

Индекс — это отдельная структура данных, которая физически хранится на диске рядом с таблицей. Каждый созданный индекс занимает место, иногда сопоставимое с размером самой таблицы. На проекте с 50 таблицами и 200 индексами суммарный размер индексов может превышать 500 ГБ — и это напрямую влияет на стоимость хранения в облаке и время бэкапов.

Знание примерного размера индекса до его создания помогает ответить на три вопроса: хватит ли дискового пространства, поместится ли индекс в оперативную память сервера и стоит ли вообще его создавать или лучше обойтись частичным индексом. Практика показывает, что 15-20% индексов в продуктовых базах либо не используются, либо дублируют друг друга.

Как устроена страница индекса в PostgreSQL

PostgreSQL хранит индексы в виде страниц фиксированного размера — 8192 байта (8 КБ). Это минимальная единица чтения и записи. Каждая страница содержит заголовок (28 байт), массив указателей на записи и сами записи индекса. Свободное пространство между записями и концом страницы используется для будущих вставок.

Когда новая строка попадает в индекс, PostgreSQL ищет подходящую страницу и размещает запись там. Если свободного места недостаточно, страница расщепляется на две — это дорогая операция, которая блокирует запись и увеличивает размер индекса. Fillfactor как раз управляет тем, сколько места резервируется под будущие вставки.

Для B-tree индекса каждая запись содержит: значение ключа (ваши данные), CTID — указатель на физическое расположение строки в таблице (6 байт: номер блока + смещение), и служебные биты для навигации по дереву (ещё 2-4 байта). Суммарно overhead составляет 8-12 байт на запись.

Пять типов индексов и их особенности

B-tree — универсальный тип, покрывающий 90% потребностей. Поддерживает операции =, <, >, BETWEEN, ORDER BY. Размер линейно зависит от количества строк. Overhead: ~12 байт на запись.

Hash — работает только для точного сравнения (=). Компактнее B-tree на 15-20% за счёт меньшего overhead (~8 байт). Хорош для первичных ключей-суррогатов, если не нужна сортировка. В PostgreSQL до версии 10 Hash-индексы не журналировались и считались небезопасными — сейчас это полноценный тип.

GiST — обобщённое поисковое дерево. Используется для геоданных (PostGIS), полнотекстового поиска и пользовательских типов. Overhead: ~20 байт на запись. Размер может быть в 1,5-2 раза больше B-tree на тех же данных из-за сложных внутренних структур.

GIN — инвертированный индекс. Идеален для массивов, JSONB и полнотекстового поиска. Каждый элемент массива или каждый токен документа порождает отдельную запись в индексе, поэтому размер GIN-индекса может многократно превышать размер таблицы. Overhead: ~24 байта на запись.

BRIN — блочный диапазонный индекс. Хранит не отдельные строки, а диапазоны значений для групп страниц (по умолчанию 128 страниц в группе). Чрезвычайно компактный: индекс на 100 млн строк может занимать 3-5 МБ. Подходит только для данных с физической корреляцией (временные ряды, автоинкрементные ID).

Практические советы по управлению размером индексов

Всегда проверяйте, используется ли индекс. Запрос SELECT * FROM pg_stat_user_indexes WHERE idx_scan = 0 показывает индексы, которые ни разу не использовались с момента сброса статистики. Такие индексы — кандидаты на удаление.

Рассмотрите частичные индексы. Вместо индекса на всю таблицу создайте индекс с WHERE-условием: CREATE INDEX idx_active ON orders(status) WHERE status = 'active'. Если активных заказов 5% от общего числа, индекс будет в 20 раз меньше полного.

Для TIMESTAMP и автоинкрементных ID используйте BRIN. Таблица логов с 500 млн строк и B-tree индексом на timestamp требует ~12 ГБ диска. BRIN-индекс на том же столбце — всего 40-60 МБ, а производительность выборки за конкретный день сопоставима.

Мониторьте раздувание (bloat). После интенсивных UPDATE и DELETE индекс может занимать в 2-3 раза больше своего «чистого» размера. Расширение pgstattuple и запросы на основе pg_class помогают выявить раздутые индексы. Регулярный REINDEX CONCURRENTLY возвращает размер к норме без блокировки записи.

Кейс: оптимизация индексов в интернет-магазине

Средний интернет-магазин имеет таблицу orders на 5 млн строк. Индексы: PRIMARY KEY (id) — 214 МБ, индекс на customer_id — 168 МБ, индекс на created_at — 156 МБ, составной индекс на (status, created_at) — 245 МБ. Итого: 783 МБ только на индексы одной таблицы.

После аудита выяснилось, что индекс на customer_id используется только в админке (0,1% запросов), а составной индекс дублирует created_at на 80%. Решение: удалить индекс на customer_id, заменить составной на частичный WHERE status IN ('pending','processing'). Итог: 783 → 420 МБ, экономия 363 МБ на одной таблице.

Заключение

Размер индекса — это не абстрактная цифра, а прямой фактор стоимости владения базой данных. Один непродуманный индекс на большую таблицу может стоить десятки гигабайт диска и тысячи рублей ежемесячно в облачном тарифе. Используйте калькулятор на этой странице для быстрой оценки, сравнивайте типы индексов и не создавайте индексы «на всякий случай». Помните: каждый индекс ускоряет чтение, но замедляет запись и занимает место. Баланс — ключ к эффективной работе с SQL.