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