Как хранится Index в PostgreSQL?

Механизм хранения индексов в PostgreSQL

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

Физическая организация индексов

На дисковом уровне индексы PostgreSQL хранятся в каталоге базы данных (например, base/12345/) как отдельные файлы с расширением .idx. Каждому индексу присваивается уникальный OID (идентификатор объекта), который используется в имени файла. Например, индекс может храниться в файле 123456.1.idx.

Структура хранения следует тем же принципам, что и для таблиц:

• Данные разбиваются на страницы (обычно по 8 КБ)
• Используется механизм MVCC (Multi-Version Concurrency Control) для управления версиями
• Применяется WAL (Write-Ahead Logging) для обеспечения сохранности данных

Типы индексов и их внутреннее устройство

1. B-дерево (B-tree) — индекс по умолчанию

-- Создание B-дерева
CREATE INDEX idx_users_email ON users(email);

B-дерево хранится как сбалансированное дерево, где:

• Каждый узел соответствует одной странице на диске
• Листовые узлы содержат TID (Tuple ID) — указатели на строки таблицы
• TID состоит из номера блока и позиции внутри блока
• Индекс поддерживает сортировку значений

2. Хеш-индекс (Hash index)

CREATE INDEX idx_users_id_hash ON users USING hash(id);

Хранит хэш-значения ключей и соответствующие TID. После версии PostgreSQL 10 стал полностью устойчивым к сбоям и поддерживает WAL.

3. GiST (Generalized Search Tree)

CREATE INDEX idx_geo_points ON points USING gist(location);

Предоставляет инфраструктуру для создания собственных типов индексов. Используется для геоданных, полнотекстового поиска и других сложных структур.

4. GIN (Generalized Inverted Index)

CREATE INDEX idx_docs_content ON documents USING gin(to_tsvector('english', content));

Оптимизирован для составных значений (массивы, JSONB, полнотекстовый поиск). Хранит пары (ключ, массив TID).

5. SP-GiST (Space-Partitioned GiST)

CREATE INDEX idx_ip_addresses ON network_logs USING spgist(ip_address inet_ops);

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

Внутренняя структура B-дерева (наиболее распространенного)

Физическая структура страницы индекса включает:

• Заголовок страницы (PageHeader) — общая информация о странице
• Массив указателей на элементы (ItemIdArray)
• Свободное пространство (FreeSpace)
• Сами элементы индекса (IndexTuples)

Пример элемента индекса (Index Tuple):

[Нулевой бит] [Биты видимости] [Значение ключа] [TID]

Особенности хранения TID

TID (Tuple ID) — это внутренний указатель на строку:

typedef struct ItemPointerData {
    BlockIdData ip_blkid;  /* ID блока (0..65535) */
    OffsetNumber ip_posid; /* Позиция в блоке (1..MaxHeapTuplesPerPage) */
}

В B-дереве листовые узлы хранят пары (ключ, TID). Если в таблице есть HOT (Heap-Only Tuples) обновления, старые TID могут перенаправляться к новым версиям строк.

Управление версиями и MVCC

Индексы в PostgreSQL не хранят информацию о версиях строк. Они указывают на последнюю версию строки в таблице. При обновлении строки:

• Если изменяются индексированные столбцы, создаются новые записи индекса
• Если изменяются неиндексированные столбцы, может использоваться HOT-оптимизация без обновления индексов

Особенности для частичных и составных индексов

Частичный индекс хранит только записи, удовлетворяющие условию:

CREATE INDEX idx_active_users ON users(email) WHERE is_active = true;

Составной индекс хранит комбинации значений:

CREATE INDEX idx_users_name_email ON users(last_name, first_name, email);

Значения хранятся в порядке следования столбцов, что позволяет эффективно выполнять поиск по префиксу.

Мониторинг и обслуживание

Для анализа использования индексов:

-- Размер индекса
SELECT pg_size_pretty(pg_total_relation_size('idx_users_email'));

-- Статистика использования
SELECT * FROM pg_stat_user_indexes WHERE indexrelname = 'idx_users_email';

-- Перестроение индекса
REINDEX INDEX CONCURRENTLY idx_users_email;

Ключевые особенности хранения

1. Отдельные файлы — каждый индекс хранится в своем файле
2. Совместное использование WAL — обеспечивает надежность
3. Отсутствие версионности в самих индексах
4. Поддержка параллельного построения (с версии PostgreSQL 11)
5. Индекс-сканирование может быть только последовательным или бинарным

Производительность и оптимизация

• Fillfactor — позволяет оставлять свободное место в страницах индекса
• Параллельное построение уменьшает время создания индексов
• Индексы только для чтения в репликах не создают WAL
• Автовакуум обслуживает индексы, удаляя устаревшие TID

Хранение индексов в PostgreSQL — это компромисс между производительностью чтения, накладными расходами на запись и надежностью. Понимание внутреннего устройства позволяет оптимизировать работу с индексами для конкретных сценариев использования.