---

🟢 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 (note не індексовано)
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
• Що таке складений індекс → консолідація індексів
• Як оптимізувати повільні запити → баланс індексів