🟢 Junior Level

Создавайте индекс, когда поиск по таблице стал медленным и вы понимаете, какие поля используются для поиска.

Простая аналогия: Если вы часто ищете контакт в телефонной книге по номеру телефона, имеет смысл создать отдельный указатель “по номерам”.

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

• Индекс нужен для ускорения поиска
• Создавайте индексы для полей в WHERE, JOIN, ORDER BY
• Не создавайте индексы “на всякий случай”

Пример:

-- Медленный запрос без индекса
SELECT * FROM users WHERE email = 'test@example.com';

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

-- Теперь запрос быстрый
SELECT * FROM users WHERE email = 'test@example.com';

Когда создавать:

• Поле часто используется в WHERE
• Поле используется в JOIN для связи таблиц
• Поле используется в ORDER BY для сортировки
• Таблица большая (больше 10,000 строк)

---

🟡 Middle Level

Как выявить потребность в индексах

1. Анализ медленных запросов

-- Включаем логирование медленных запросов
-- postgresql.conf:
-- log_min_duration_statement = 1000  -- логирует запросы > 1 секунды

2. Расширение pg_stat_statements

-- Включаем расширение
CREATE EXTENSION pg_stat_statements;

-- Находим самые медленные запросы
SELECT query, 
       calls,
       mean_exec_time as avg_time_ms,
       total_exec_time as total_time_ms
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Основные критерии для создания

1. Поля в WHERE с высокой селективностью

-- ✅ Высокая селективность (много уникальных значений)
CREATE INDEX idx_users_email ON users(email);

-- ❌ Низкая селективность (мало уникальных значений)
CREATE INDEX idx_users_gender ON users(gender); -- M/F

2. Внешние ключи (Foreign Keys)

-- Рекомендуется индексировать FK!
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id)  -- ← индексируйте это!
);

-- Создаём индекс на FK
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- Зачем? При DELETE пользователя PG проверяет absence заказов
-- Без индекса → Seq Scan по orders (очень медленно на больших таблицах)

Исключения: FK-таблица < 1000 строк (Seq Scan быстрый), или вы никогда не удаляете/не обновляете родительские записи.

3. Поля для JOIN

-- Для Nested Loop Join критичны индексы на полях связи
SELECT * FROM orders o
JOIN users u ON o.user_id = u.id;

-- Индексы:
-- orders(user_id) ← для поиска заказов пользователя
-- users(id) ← уже есть (PRIMARY KEY)

4. Поля для ORDER BY / GROUP BY

-- Индекс позволяет избежать сортировки
CREATE INDEX idx_orders_created ON orders(created_at DESC);

-- Запрос без Sort (данные уже отсортированы)
SELECT * FROM orders ORDER BY created_at DESC LIMIT 10;

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

1. Создание индекса без анализа нагрузки
   • ❌ Создали индекс “на всякий случай”
   • ✅ Сначала проанализируйте запросы через EXPLAIN
2. Дублирование индексов

   -- ❌ Два одинаковых индекса
   CREATE INDEX idx1 ON users(email);
   CREATE INDEX idx2 ON users(email);
   

3. Индексы на маленьких таблицах
   • Таблица < 1000 строк → Seq Scan быстрее
   • Индекс только занимает место

Стоимость индекса

Каждый индекс — это:

1. Замедление INSERT/UPDATE/DELETE (дерево нужно обновлять)
2. Место на диске (от 10% до 100%+ от размера таблицы)
3. Нагрузка на RAM (индекс должен быть в памяти для эффективности)

Практическое правило

-- Перед созданием индекса проверьте:
EXPLAIN (ANALYZE, BUFFERS) 
SELECT * FROM users WHERE email = 'test@example.com';

-- Если видите Seq Scan и buffer hits низкий → индекс поможет
-- Если уже Index Scan → возможно, индекс уже есть

---

🔴 Senior Level

Как выявить потребность: Workload Analysis

pg_stat_statements — основной инструмент:

-- Находим запросы с наибольшим total_exec_time
SELECT 
    query,
    calls,
    mean_exec_time,
    total_exec_time,
    rows,
    shared_blks_read,    -- Чтение с диска (плохо)
    shared_blks_hit      -- Чтение из кэша (хорошо)
FROM pg_stat_statements
WHERE dbid = (SELECT oid FROM pg_database WHERE datname = current_database())
ORDER BY total_exec_time DESC
LIMIT 20;

-- Ключевая метрика: shared_blks_read / calls > 100
-- → Запрос читает много страниц с диска → нужен индекс

HypoPG — гипотетические индексы:

-- Расширение для тестирования "что если"
CREATE EXTENSION hypopg;

-- Создаём виртуальный индекс (НЕ занимает место!)
SELECT * FROM hypopg_create_index(
    'CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC)'
);

-- Проверяем, выберет ли его планировщик
EXPLAIN SELECT * FROM orders WHERE user_id = 123 ORDER BY created_at DESC LIMIT 10;
-- В плане увидите: Index Scan using <hypothetical>...

-- Удаляем гипотетический индекс
SELECT * FROM hypopg_drop_index(indexrelid);

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

1. Write Amplification

Каждый новый индекс = 
  + Замедление INSERT/UPDATE/UPDATE (дерево нужно обновлять)
  + Нагрузка на WAL (Write Ahead Log — журнал предварительной записи: PostgreSQL сначала логирует все изменения в WAL-файлы, а потом применяет их к основным данным. Каждый индекс увеличивает объём WAL, потому что изменения индекса тоже нужно залогировать)
  + Увеличение Replica Lag (реплика не успевает)
  + Bloat (раздувание индекса со временем)

2. Консолидация индексов

-- ❌ Было: 3 отдельных индекса
CREATE INDEX idx_a ON t(a);              -- 2 ГБ
CREATE INDEX idx_a_b ON t(a, b);         -- 5 ГБ
CREATE INDEX idx_a_b_c ON t(a, b, c);    -- 8 ГБ
-- Итого: 15 ГБ, замедление записи x3

-- ✅ Стало: 1 индекс покрывает все префиксы
CREATE INDEX idx_a_b_c ON t(a, b, c);    -- 8 ГБ
-- Покрывает: WHERE a, WHERE a,b, WHERE a,b,c
-- Экономия: 7 ГБ, запись в 3 раза быстрее

3. Partial Index для редких значений

-- Индексируем только ошибки (5% от всех записей)
CREATE INDEX idx_orders_errors ON orders(id, error_message)
WHERE status = 'ERROR';

-- Экономия места: 95% меньше полного индекса
-- Ускорение: только для критичных запросов

Edge Cases

1. Создание индекса в Production

   -- ❌ Блокирует запись (ShareLock)
   CREATE INDEX idx ON orders(user_id);
      
   -- ✅ Не блокирует, но выполняется дольше
   CREATE INDEX CONCURRENTLY idx ON orders(user_id);
      
   -- ⚠️ При прерывании остаётся INVALID
   SELECT indexrelid::regclass, indisvalid 
   FROM pg_index 
   WHERE NOT indisvalid;
   

2. Bulk Load оптимизация

   -- При вставке миллионов строк:
   -- 1. Удалить индексы
   DROP INDEX idx_orders_user_id;
      
   -- 2. Вставить данные
   COPY orders FROM '/data/orders.csv';
      
   -- 3. Пересоздать индексы (быстрее, чем обновлять при каждой вставке)
   CREATE INDEX idx_orders_user_id ON orders(user_id);
   

3. Index bloat prevention

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

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

Влияние на запись (примерные цифры):

Количество индексов	INSERT latency	WAL volume
1-2	+5-10%	Базовый
5-7	+50-100%	x2-x3
10-15	+200-500%	x5-x10

Правило 10-15%:

• Если индекс используется < 10-15% от общего числа запросов → скорее всего, не нужен
• Seq Scan на таблице, которая целиком в кэше, может быть быстрее Index Scan

Production Experience

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

• Финтех стартап: 50 млн транзакций
• Проблема: Запросы на отчёты > 30 секунд
• Анализ через pg_stat_statements:
  • Топ-3 запроса читают 80% времени
  • Все 3 используют WHERE user_id = ? AND created_at > ?
• Решение:
  • CREATE INDEX CONCURRENTLY idx_transactions_user_date ON transactions(user_id, created_at DESC)
  • Добавили INCLUDE (amount, type) для Index-Only Scan
• Результат: Запросы стали < 100ms (ускорение в 300 раз)

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

• E-commerce: 200 млн заказов
• Проблема: INSERT замедлился до 2 секунд, Replica Lag > 60 секунд
• Анализ:
  • 18 индексов на таблице orders
  • 12 из них использовались < 5 раз за месяц
  • WAL volume: 500 МБ/минуту
• Решение:
  • Удалили 12 неиспользуемых индексов
  • Consolidate оставшихся до 6
  • Результат: INSERT < 100ms, Replica Lag < 5 секунд

Monitoring

-- 1. Проверка использования индексов
SELECT 
    schemaname,
    relname as table_name,
    indexrelname as index_name,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch,
    pg_size_pretty(pg_relation_size(indexrelid)) as size
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY idx_scan ASC  -- Неиспользуемые внизу
LIMIT 20;

-- 2. Проверка Foreign Keys без индексов
SELECT 
    conrelid::regclass AS table_with_fk,
    conname AS fk_constraint,
    confrelid::regclass AS referenced_table
FROM pg_constraint
WHERE contype = 'f'
  AND NOT EXISTS (
    SELECT 1 FROM pg_index
    WHERE indrelid = conrelid
      AND indkey[0] = ANY(conkey)
  );

-- 3. Проверка дубликатов
-- (индексы с одинаковым набором колонок)
SELECT 
    a.indexrelid::regclass as index1,
    b.indexrelid::regclass as index2,
    pg_get_indexdef(a.indexrelid) as definition
FROM pg_index a
JOIN pg_index b ON a.indrelid = b.indrelid 
  AND a.indexrelid < b.indexrelid
  AND a.indkey::text = b.indkey::text;

-- 4. HypoPG для тестирования
SELECT * FROM hypopg_list_indexes();

Best Practices

1. Анализируйте workload через pg_stat_statements перед созданием
2. Тестируйте гипотезы через HypoPG
3. Рекомендуется индексировать FK
4. Используйте CONCURRENTLY в production
5. Консолидируйте индексы по префиксам
6. Удаляйте неиспользуемые индексы регулярно
7. Мониторьте Replica Lag при добавлении новых индексов
8. Для Bulk Load — удаляйте и пересоздавайте индексы

Резюме для Senior

• Используйте pg_stat_statements для поиска bottleneck запросов
• Проверяйте гипотезы через HypoPG перед созданием реальных индексов
• Рекомендуется индексировать Foreign Keys — это частая причина Seq Scan
• В production — только CONCURRENTLY
• Консолидируйте индексы: (a), (a,b), (a,b,c) → один (a,b,c)
• Мониторьте Replica Lag и WAL volume после добавления индексов
• Удаляйте неиспользуемые индексы — каждый индекс это налог на запись

---

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

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

• Анализируйте workload через pg_stat_statements перед созданием
• Рекомендуется индексировать Foreign Keys — это частая причина Seq Scan
• CONCURRENTLY в production (не блокирует запись, но дольше)
• HypoPG — тестирование «что если» без создания реального индекса
• Write Amplification: каждый индекс = +INSERT/UPDATE/DELETE + WAL volume
• Bulk Load: удаляйте индексы → вставляйте → пересоздавайте (быстрее)
• fillfactor = 70-80 для часто обновляемых индексов

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

• «Как понять, что индекс нужен?» → shared_blks_read / calls > 100 в pg_stat_statements
• «Что делать перед массовой вставкой?» → Удалить индексы → COPY → пересоздать
• «Как проверить, поможет ли индекс?» → HypoPG + EXPLAIN
• «Что такое Replica Lag и как индексы влияют?» → Больше индексов → больше WAL → реплика отстаёт

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

• ❌ «Создам индекс и посмотрю» (анализируйте workload сначала!)
• ❌ «Индексы на всякий случай не повредят» (Write Amplification!)
• ❌ «В production создам индекс без CONCURRENTLY» (блокировка записи!)

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

• [[Для чего нужны индексы]] → обзор
• [[Какие недостатки индексов]] → Write Amplification
• [[Что такое составной индекс]] → консолидация
• [[Как работает MVCC в PostgreSQL]] → HOT-обновления