Для чего нужны индексы?

🟢 Junior Level

Индексы — это вспомогательные структуры данных в базе данных, которые ускоряют поиск информации.

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

Основная идея:

Без индекса база данных просматривает всю таблицу подряд (Seq Scan — последовательное сканирование, когда каждая строка читается одна за другой, как если бы вы листали книгу страницу за страницей)
С индексом база данных быстро находит нужные строки

Пример:

-- Без индекса: медленный поиск по всей таблице
SELECT * FROM users WHERE email = 'test@example.com';

-- Создаём индекс
CREATE INDEX idx_users_email ON users(email);

-- Теперь поиск происходит мгновенно
SELECT * FROM users WHERE email = 'test@example.com'; -- Использует индекс

Что изменилось внутри: SQL-запрос тот же, но PostgreSQL теперь не читает все 1 000 000 строк. Вместо этого он проходит 3-4 страницы B-дерева индекса (это ~0.01ms) и по TID сразу переходит к нужной строке в таблице.

Когда использовать:

Когда часто ищете данные по определённому полю
Когда таблица большая (тысячи строк и больше)
Для полей, которые используются в WHERE, JOIN, ORDER BY

🟡 Middle Level

Как это работает

Индекс хранит значения колонок в отсортированном виде и указатели (TID — Tuple ID, пара номер_блока:смещение_в_блоке) на физические строки в таблице. Самый распространённый тип индекса — B-Tree (создаётся по умолчанию).

Типы индексов в PostgreSQL

Тип	Когда использовать	Поддерживаемые операции
B-Tree	Универсальный (по умолчанию)	`<`, `<=`, `=`, `>=`, `>`, диапазоны
Hash	Только точное равенство	`=`
GIN	Массивы, JSONB, полнотекстовый поиск	`@>`, `?`, `@@`
GiST	Геоданные, интервалы	Зависит от оператора
BRIN	Огромные таблицы (>100ГБ) с упорядоченными данными	Диапазоны

Почему не всегда B-Tree? Hash-индекс занимает меньше места на диске, чем B-Tree, но поддерживает только =. GIN умеет искать по содержимому массивов и JSONB — чего B-Tree не умеет вообще. BRIN хранит только диапазоны значений на страницах — занимает в 100-1000 раз меньше места, чем B-Tree, но подходит только для упорядоченных данных (временные ряды).

Типы сканирования

Тип	Механизм	Когда происходит
Index Scan	Индекс → чтение таблицы	Когда нужны колонки, которых нет в индексе
Index Only Scan	Только индекс	Все данные есть в индексе
Bitmap Index Scan	Индекс → битовая карта → таблица	Когда данных много, но они разбросаны
Seq Scan	Последовательное чтение	Маленькие таблицы или нет индекса

Bitmap Index Scan: собирает ВСЕ matching TID из индекса в битовую карту (1 = есть совпадение, 0 = нет), затем читает строки из heap. Выгоднее Index Scan когда нужно прочитать 100-10,000 строк из разных мест — меньше случайных I/O.

Типичные ошибки

Создание индексов на всех полях
- ❌ Проблема: Замедляет INSERT/UPDATE/DELETE
- ✅ Решение: Индексируйте только поля, которые реально используются в WHERE/JOIN

Использование функций в WHERE

-- ❌ Индекс НЕ будет использован
SELECT * FROM users WHERE LOWER(email) = 'test@example.com';
   
-- ✅ Создайте функциональный индекс
CREATE INDEX idx_users_email_lower ON users(LOWER(email));

Поиск по второй колонке составного индекса

-- Индекс (city, age)
-- ❌ Не будет использован для поиска только по age
SELECT * FROM users WHERE age = 25;

Когда НЕ стоит создавать индекс

Маленькие таблицы (< 1000 строк) — Seq Scan быстрее, потому что стоимость одного последовательного чтения всех страниц меньше, чем стоимость навигации по B-tree индексу + случайного чтения Heap-страницы
Колонки с низкой кардинальностью (например, пол: M/F)
Поля, которые редко используются в запросах
Таблицы с интенсивной записью (каждый индекс замедляет INSERT)

Практический пример

-- Составной индекс для типичного запроса
CREATE INDEX idx_orders_customer_date 
ON orders(customer_id, created_at DESC);

-- Этот запрос использует индекс полностью
SELECT * FROM orders 
WHERE customer_id = 123 
ORDER BY created_at DESC 
LIMIT 10;

🔴 Senior Level

Internal Implementation

Индекс в PostgreSQL — это дисковая структура, оптимизированная под конкурентный доступ. B-tree реализован на основе алгоритма Lehman and Yao, который позволяет выполнять поиск и вставку без блокировки всего пути от корня до листа.

Структура B-tree:

Meta-page (страница 0)
  └── Root page
       ├── Internal pages (навигация)
       └── Leaf pages (данные + TID → Heap)
            └── P_NEXT / P_PREV (ссылки для сканирования)

High Key: Каждая страница содержит максимальное значение. Если при поиске видим, что искомое значение > High Key — значит, произошёл сплит, и нужно перейти по P_NEXT.

Архитектурные Trade-offs

Покрывающие индексы (INCLUDE):

-- В INCLUDE хранятся только в листовых узлах, не раздувая дерево
CREATE INDEX idx_orders_customer_total 
ON orders(customer_id) INCLUDE (total_amount);

-- Index Only Scan без обращения к Heap
SELECT customer_id, total_amount 
FROM orders 
WHERE customer_id = 123;

Частичные индексы:

-- Индексируем только активные сессии (экономия места в 10-100 раз)
CREATE INDEX idx_active_sessions 
ON sessions(user_id) 
WHERE status = 'ACTIVE';

Создание в Production:

-- CONCURRENTLY не блокирует запись, но выполняется дольше
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);
-- ⚠️ Нельзя внутри транзакции!
-- ⚠️ При прерывании остаётся INVALID индекс

MVCC и HOT-обновления

В PostgreSQL UPDATE = DELETE + INSERT. Если колонка индексирована, все индексы нужно обновить.

HOT (Heap Only Tuple) — оптимизация, позволяющая обновить строку без обновления индексов:

Работает, если изменяемые колонки не индексированы
На странице Heap должно быть место

Вывод: Каждый лишний индекс убивает HOT и увеличивает Write Amplification.

Edge Cases

Index Bloat
- Причина: Page Splits при вставке в середину
- Решение: fillfactor = 70-80 для часто обновляемых индексов
- Обслуживание: REINDEX CONCURRENTLY (VACUUM не возвращает место ОС)
Селективность и планировщик
- Если запрос возвращает >10-15% таблицы → планировщик выберет Seq Scan
- Random I/O (Index Scan) дороже Sequential Scan при больших выборках

Невалидные индексы

-- Проверка на INVALID индексы (после прерванного CREATE INDEX CONCURRENTLY)
SELECT indexrelid::regclass, indisvalid 
FROM pg_index 
WHERE NOT indisvalid;

Производительность

Метрика	1-2 индекса	10-15 индексов
Write IOPS	Минимальное	Экспоненциальное
Replica Lag	Почти отсутствует	Высокий риск
Update Speed	Высокая (HOT возможен)	Низкая
Maintenance	Быстрый	Долгий REINDEX

Production Experience

Реальный сценарий:

Таблица заказов: 50 млн строк, 15 индексов
Проблема: INSERT замедлился в 10 раз, Replica Lag = 30 секунд
Решение:
- Удалили 8 неиспользуемых индексов (idx_scan = 0 в pg_stat_user_indexes)
- Заменили 3 отдельных индекса на 1 составной
- Результат: INSERT ускорился в 5 раз, Replica Lag < 1 секунда

Monitoring

-- Размер индексов
SELECT 
    indexrelname,
    pg_size_pretty(pg_relation_size(indexrelid)) as size,
    idx_scan as scans,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes 
WHERE relname = 'orders'
ORDER BY pg_relation_size(indexrelid) DESC;

-- Неиспользуемые индексы (кандидаты на удаление)
SELECT 
    schemaname,
    relname as table_name,
    indexrelname as index_name,
    pg_size_pretty(pg_relation_size(indexrelid)) as size
FROM pg_stat_user_indexes 
WHERE idx_scan = 0 
  AND NOT indisunique  -- не удаляем UNIQUE
ORDER BY pg_relation_size(indexrelid) DESC;

-- Проверка Bloat через pgstattuple
SELECT * FROM pgstattuple('idx_orders_customer');
-- avg_leaf_density < 50% → время для REINDEX

Best Practices

Всегда используйте CONCURRENTLY в production
Мониторьте pg_stat_user_indexes на неиспользуемые индексы
Проверяйте планы через EXPLAIN (ANALYZE, BUFFERS)
Consolidate индексы: (a), (b), (a,b,c) → один (a,b,c)
Используйте HypoPG для тестирования гипотетических индексов
Foreign Keys — почти всегда индексируйте

Резюме для Senior

Индексы — это налог на запись ради ускорения чтения
Балансируйте между скоростью SELECT и стоимостью INSERT/UPDATE
Следите за Bloat, Replica Lag, HOT-update ratio
EXPLAIN (ANALYZE, BUFFERS) — ваш лучший друг
Удаляйте неиспользуемые индексы регулярно

🎯 Шпаргалка для интервью

Обязательно знать:

Индексы ускоряют SELECT, замедляют INSERT/UPDATE/DELETE
B-Tree — тип по умолчанию, поддерживает =, <, >, диапазоны
5 типов: B-Tree, Hash, GIN, GiST, BRIN
4 типа сканирования: Index Scan, Index Only Scan, Bitmap, Seq Scan
CONCURRENTLY для создания без блокировки в production
INCLUDE для Index Only Scan без раздувания дерева

Частые уточняющие вопросы:

«Что будет, если создать индексы на всех полях?» → Write Amplification, Replica Lag, убьёт HOT
«Как проверить, используется ли индекс?» → EXPLAIN (ANALYZE, BUFFERS), pg_stat_user_indexes
«Почему WHERE LOWER(email) = ... не использует индекс?» → Нужен функциональный индекс
«Как найти неиспользуемые индексы?» → idx_scan = 0 в pg_stat_user_indexes
«В чём разница между VACUUM и REINDEX?» → VACUUM чистит мёртвые строки, REINDEX перестраивает индекс

Красные флаги (НЕ говорить):

❌ «Индексы всегда полезны» (нет — налог на запись)
❌ «VACUUM сжимает файлы индексов» (нет, нужен REINDEX)
❌ «Hash индекс лучше B-Tree» (Hash только для =)
❌ «Создам индексы на всякий случай» (анализируйте workload)

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

[[Как работает B-tree индекс]] → внутренняя структура
[[Что такое составной индекс]] → правило левого префикса
[[Как работает MVCC в PostgreSQL]] → MVCC и HOT-обновления
[[Что такое VACUUM в PostgreSQL]] → обслуживание индексов
[[Как оптимизировать медленные запросы]] → практическое применение