🟢 Junior Level

Индексы имеют обратную сторону: они ускоряют чтение, но замедляют запись.

Простая аналогия: Представьте, что у вас есть книга с оглавлением. Когда вы добавляете новую главу, вам нужно не только вписать её в книгу, но и обновить оглавление. Если оглавлений несколько — обновлять нужно каждое.

Основные недостатки:

• ⚠️ Замедление INSERT/UPDATE/DELETE — каждый индекс нужно обновлять
• ⚠️ Занимают место на диске — иногда больше, чем сама таблица
• ⚠️ Требуют обслуживания — нужно следить за их состоянием

Пример:

-- Таблица с 5 индексами
-- При вставке 1 строки:
-- 1. Записываем строку в таблицу
-- 2. Обновляем индекс #1
-- 3. Обновляем индекс #2
-- 4. Обновляем индекс #3
-- 5. Обновляем индекс #4
-- 6. Обновляем индекс #5
-- Итого: 6 операций вместо 1!

Правило: Создавайте индексы только тогда, когда они действительно нужны.

---

🟡 Middle Level

Write Amplification (Усиление записи)

Каждый индекс увеличивает количество операций записи:

1 INSERT в таблицу 
= 1 запись в таблицу 
+ N записей в индексы
+ Запись в WAL (Write Ahead Log)

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

Количество индексов	INSERT latency	WAL объем
1	1 ms	1x
5	5-10 ms	5x
10	20-50 ms	10x

Блокировка HOT-обновлений

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

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

-- Таблица с индексом на status
CREATE INDEX idx_orders_status ON orders(status);

-- ❌ Этот UPDATE должен обновить индекс
UPDATE orders SET status = 'SHIPPED' WHERE id = 123;

-- ✅ Этот UPDATE может использовать HOT (status не индексирован)
UPDATE orders SET note = 'New note' WHERE id = 123;

Условие для HOT:

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

Раздувание (Index Bloat)

Индексы склонны к фрагментации больше, чем таблицы:

-- Проблема: Page Splits при вставке в середину
-- Индекс по email: 'A...', 'B...', 'C...'
-- Вставляем 'B...' → страница переполняется → Split

-- Решение: fillfactor
CREATE INDEX idx_orders_status ON orders(status) 
WITH (fillfactor = 70);
-- 30% страницы свободно → меньше сплитов

VACUUM не возвращает место ОС:

• VACUUM помечает место как свободное для новых записей
• Физический размер файла не уменьшается
• Для сжатия нужен REINDEX

Конкуренция за RAM

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

-- Если суммарный размер индексов > доступной RAM
-- → Cache Misses → Random I/O → падение производительности

Время планирования запроса

Оптимизатор рассматривает все доступные индексы:

• 5-10 индексов → планирование < 1 ms
• 20-30 индексов → планирование 10-50 ms
• 100+ индексов → планирование > 100 ms!

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

• Таблица < 10,000 строк (Seq Scan быстрее)
• Колонка с низкой кардинальностью (например, пол: M/F)
• Поле, которое редко в WHERE/JOIN
• Таблица с очень интенсивной записью

---

🔴 Senior Level

Write Amplification и WAL-логи

Детальное влияние на I/O:

Операция INSERT без индексов:
├── Heap page записей: 1
├── WAL записей: 1 page
└── I/O операций: 1-2

Операция INSERT с 10 индексами:
├── Heap page записей: 1
├── Index page записей: 10
├── WAL записей: 11 pages
├── Возможно Page Splits: +10-20 записей
└── I/O операций: 15-30

Влияние на Replica Lag:

• Каждый индекс генерирует WAL-записи
• Реплика должна проиграть ВСЕ записи
• При 10+ индексах: WAL volume увеличивается в 10-15 раз
• Реплика не успевает → Replica Lag растёт
• Критично для систем с Read Replicas

Блокировка HOT-обновлений: Глубокий анализ

Механизм HOT:

-- Страница Heap (fillfactor = 100, нет места)
┌─────────────────────────────────┐
│ Row1 | Row2 | Row3 | ... | Full│
└─────────────────────────────────┘

-- UPDATE Row2 без HOT:
-- 1. Создаём новую версию Row2 в другой странице
-- 2. Обновляем ВСЕ индексы (новая TID)
-- 3. Старую версию помечаем dead

-- UPDATE Row2 с HOT (есть место на странице):
-- 1. Создаём новую версию Row2 рядом
-- 2. Обновляем только Heap (индексы не трогаем!)
-- 3. Старую версию помечаем dead
-- 4. Индексы всё ещё валидны (TID не изменился)

Мониторинг HOT:

-- Проверка эффективности HOT
SELECT 
    schemaname,
    relname,
    n_tup_hot_update,  -- HOT обновления
    n_tup_upd,         -- Всего обновлений
    ROUND(100.0 * n_tup_hot_update / NULLIF(n_tup_upd, 0), 2) as hot_ratio
FROM pg_stat_user_tables
WHERE n_tup_upd > 0
ORDER BY hot_ratio DESC;

-- hot_ratio > 70% → отлично (типичное значение для здоровой OLTP-нагрузки)
-- hot_ratio < 20% → проблема: большинство обновлений вынуждены обновлять индексы,
--   что указывает на избыточное индексирование или слишком маленький fillfactor

Index Bloat: Диагностика и лечение

Причины Bloat:

1. Page Splits — вставка в середину отсортированного списка
2. Удаления — страницы остаются полупустыми
3. Обновления индексированных колонок — новые версии, старые dead

Диагностика:

-- 1. Проверка через pgstattuple
CREATE EXTENSION pgstattuple;

SELECT 
    index_name,
    avg_leaf_density,      -- Норма: 70-90%
    leaf_fragmentation     -- Норма: < 20%
FROM pgstatindex('idx_orders_email');

-- 2. Сравнение размера с ожидаемым
SELECT 
    schemaname,
    relname as table_name,
    indexrelname as index_name,
    pg_size_pretty(pg_relation_size(indexrelid)) as actual_size,
    idx_scan,
    -- Если размер подозрительно большой → Bloat
    CASE 
        WHEN pg_relation_size(indexrelid) > 1000000000  -- > 1 ГБ
             AND idx_scan < 100 
        THEN 'POSSIBLE BLOAT'
        ELSE 'OK'
    END as status
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY pg_relation_size(indexrelid) DESC;

Лечение:

-- 1. REINDEX (блокирует запись!)
REINDEX INDEX idx_orders_email;

-- 2. REINDEX CONCURRENTLY (не блокирует, PG 12+)
REINDEX INDEX CONCURRENTLY idx_orders_email;

-- 3. pg_repack (без блокировок, расширение)
-- Установка: CREATE EXTENSION pg_repack;
-- Запуск: pg_repack -t orders -d mydb

Конкуренция за Shared Buffers

Математика:

Дано:
- RAM: 64 ГБ
- shared_buffers: 16 ГБ (25%)
- Таблица: 10 ГБ
- Индексы: 20 ГБ

Проблема:
- Таблица + Индексы = 30 ГБ > 16 ГБ shared_buffers
- Часть индексов вытесняется на диск
- Cache Miss rate > 40%
- Random I/O вместо чтения из RAM

Решение:
- Удалить неиспользуемые индексы → 8 ГБ
- Увеличить shared_buffers до 32 ГБ
- Cache Hit rate > 95%

Мониторинг Cache Hit:

-- Проверка эффективности кэша
SELECT 
    sum(heap_blks_read) as heap_read,
    sum(heap_blks_hit) as heap_hit,
    ROUND(100.0 * sum(heap_blks_hit) / 
          NULLIF(sum(heap_blks_read) + sum(heap_blks_hit), 0), 2) 
          as cache_hit_pct
FROM pg_statio_user_tables;

-- > 99% → отлично
-- 95-99% → нормально
-- < 95% → проблема (мало RAM или слишком много индексов)

Planning Time Overhead

-- Включаем логирование времени планирования
SET log_min_duration_statement = 0;
SET log_parser_stats = on;

-- В логах увидим:
-- duration: 0.123 ms  parse
-- duration: 0.456 ms  plan
-- duration: 12.345 ms execute

-- Если plan > execute → слишком много индексов/таблиц

geqo_threshold: При > 12 таблиц в запросе включается генетический алгоритм (GEQO), который может выбрать неоптимальный план.

Production Experience

Реальный сценарий #1: Bloat убивает производительность

• SaaS платформа: 100 млн пользователей
• Индекс на email: 8 ГБ (должен быть 2 ГБ)
• Симптом: Поиск по email > 500ms, Cache Hit rate 60%
• Причина: 2 года обновлений без REINDEX → Bloat 75%
• Решение: REINDEX INDEX CONCURRENTLY idx_users_email
• Результат: Индекс стал 2.1 ГБ, поиск < 5ms, Cache Hit 98%

Реальный сценарий #2: Replica Lag из-за индексов

• E-commerce: 15 индексов на orders (200 млн строк)
• Проблема: Replica Lag > 120 секунд
• Анализ: WAL volume 1 ГБ/минуту, реплика не успевает
• Решение:
  • Удалили 10 неиспользуемых индексов
  • WAL volume упал до 200 МБ/минуту
  • Replica Lag < 5 секунд

Monitoring Dashboard

-- 1. Top индексы по размеру
SELECT 
    indexrelname,
    pg_size_pretty(pg_relation_size(indexrelid)) as size,
    idx_scan
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 10;

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

-- 3. HOT ratio по таблицам
SELECT 
    relname,
    n_tup_hot_update,
    n_tup_upd,
    ROUND(100.0 * n_tup_hot_update / NULLIF(n_tup_upd, 0), 2) as hot_pct
FROM pg_stat_user_tables
WHERE n_tup_upd > 1000
ORDER BY hot_pct ASC
LIMIT 10;

-- 4. Fill factor проверка
SELECT 
    c.relname as index_name,
    c.reloptions
FROM pg_class c
JOIN pg_index i ON i.indexrelid = c.oid
WHERE c.reloptions::text LIKE '%fillfactor%'
  AND c.relkind = 'i';

Best Practices

1. Мониторьте HOT ratio — цель > 70%
2. Проверяйте Bloat регулярно через pgstattuple
3. REINDEX CONCURRENTLY раз в квартал для горячих индексов
4. Удаляйте неиспользуемые — idx_scan = 0 за месяц → кандидат на удаление
5. fillfactor = 70-80 для часто обновляемых индексов
6. Следите за Replica Lag после добавления индексов
7. Ограничьте количество — максимум 5-7 индексов на таблицу
8. Cache Hit Rate > 95% — если меньше, уменьшайте индексы или добавляйте RAM

Резюме для Senior

• Индексы — это налог на запись (Write Amplification, WAL, Replica Lag)
• HOT-update ratio < 20% → слишком много индексов
• Bloat неизбежен — лечите через REINDEX CONCURRENTLY
• Shared Buffers конкуренция — индексы вытесняют данные из кэша
• Planning Time растёт с количеством индексов
• Мониторьте: pgstattuple, pg_stat_user_indexes, HOT ratio, Cache Hit rate
• Удаляйте безжалостно — если индекс не используется месяц, он не нужен

---

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

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

• Write Amplification: 1 INSERT = 1 запись в таблицу + N записей в индексы + WAL
• HOT (Heap Only Tuple): оптимизация UPDATE без обновления индексов (если колонка не индексирована)
• HOT ratio > 70% → отлично, < 20% → слишком много индексов
• Index Bloat: Page Splits → фрагментация → REINDEX CONCURRENTLY
• VACUUM НЕ возвращает место ОС → REINDEX для сжатия
• Shared Buffers конкуренция: индексы вытесняют данные → Cache Miss → Random I/O
• Planning Time: 5-10 индексов < 1ms, 20-30 → 10-50ms, 100+ → > 100ms

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

• «Почему 15 индексов — это плохо?» → Replica Lag, Write Amplification, Cache Miss
• «Как проверить Bloat индекса?» → pgstattuple, avg_leaf_density < 50% → REINDEX
• «Что такое HOT и почему это важно?» → UPDATE без обновления индексов → быстрее
• «Почему Cache Hit Rate падает с ростом индексов?» → Индексы вытесняют данные из RAM

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

• ❌ «Чем больше индексов, тем лучше» (Write Amplification!)
• ❌ «VACUUM сжимает файл индекса» (нет, REINDEX)
• ❌ «HOT — это какая-то магия» (UPDATE без обновления индексов!)
• ❌ «Planning Time не важен» (100+ индексов → > 100ms планирования)

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

• [[Для чего нужны индексы]] → когда создавать
• [[Как работает B-tree индекс]] → Bloat, Page Splits
• [[Что такое составной индекс]] → консолидация индексов
• [[Как оптимизировать медленные запросы]] → баланс индексов