🟢 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.

Типові помилки

1. Створення індексів на всіх полях
   • ❌ Проблема: Сповільнює INSERT/UPDATE/DELETE
   • ✅ Рішення: Індексуйте лише поля, які реально використовуються в WHERE/JOIN
2. Використання функцій у WHERE

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

3. Пошук по другій колонці складного індекса

   -- Індекс (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

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

   -- Перевірка на 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

1. Завжди використовуйте CONCURRENTLY в production
2. Моніторте pg_stat_user_indexes на невикористовувані індекси
3. Перевіряйте плани через EXPLAIN (ANALYZE, BUFFERS)
4. Consolidate індексів: (a), (b), (a,b,c) → один (a,b,c)
5. Використовуйте HypoPG для тестування гіпотетичних індексів
6. 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]] → обслуговування індексів
• [[Як оптимізувати повільні запити]] → практичне застосування