🟢 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/DELETE (дерево потрібно оновлювати)
  + Навантаження на 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-оновлення