Для чого потрібні індекси?

Індекс зберігає значення колонок у відсортованому вигляді та вказівники (TID — Tuple ID, пара номер_блоку:зміщення_в_блоці) на фізичні рядки в таблиці. Найпоширеніший тип індекс...

🟢 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 
    s.schemaname,
    s.relname as table_name,
    s.indexrelname as index_name,
    pg_size_pretty(pg_relation_size(s.indexrelid)) as size
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0 
  AND NOT i.indisunique  -- не видаляємо UNIQUE
ORDER BY pg_relation_size(s.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)
Консолідуйте індекси: (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 → обслуговування індексів
Як оптимізувати повільні запити → практичне застосування