Бази даних SQL PostgreSQL
21 питань і відповідей у розділі Бази даних SQL PostgreSQL.
Питання цього розділу
- Для чого потрібні індекси?
- Як працює B-tree індекс?
- Що таке складений індекс?
- Коли варто створювати індекс?
- Які недоліки є у індексів?
- Що таке кардинальність індексу?
- Які типи JOIN існують?
- В чому різниця між INNER JOIN та LEFT JOIN?
- Що краще JOIN чи підзапит
- Що таке корельований підзапит?
- У чому різниця між WHERE та HAVING?
- Що робить GROUP BY?
- Коли потрібно використовувати HAVING?
- Що таке віконні функції (Window Functions)?
- Що робить ROW_NUMBER()?
- Що роблять RANK() та DENSE_RANK()?
- Як працює MVCC в PostgreSQL?
- Що таке VACUUM в PostgreSQL?
- Навіщо потрібен ANALYZE?
- Що таке explain plan?
- Як оптимізувати повільні запити?
Навігатор по розділу
21 питання для підготовки до співбесіди на Middle Java Developer.
📋 Усі питання
| # | Питання | Рівень складності |
|---|---|---|
| 1 | Для чого потрібні індекси | ⭐⭐ |
| 2 | Як працює B-tree індекс | ⭐⭐⭐ |
| 3 | Що таке складений індекс | ⭐⭐ |
| 4 | Коли варто створювати індекс | ⭐⭐ |
| 5 | Які недоліки є у індексів | ⭐⭐ |
| 6 | Що таке кардинальність індексу | ⭐⭐⭐ |
| 7 | Які типи JOIN існують | ⭐⭐⭐ |
| 8 | В чому різниця між INNER JOIN та LEFT JOIN | ⭐⭐ |
| 9 | Що краще: JOIN чи підзапит | ⭐⭐⭐ |
| 10 | Що таке корельований підзапит | ⭐⭐⭐ |
| 11 | В чому різниця між WHERE та HAVING | ⭐ |
| 12 | Що робить GROUP BY | ⭐⭐ |
| 13 | Коли використовувати HAVING | ⭐ |
| 14 | Що таке віконні функції | ⭐⭐⭐ |
| 15 | Що робить ROW_NUMBER() | ⭐⭐ |
| 16 | Що робить RANK() та DENSE_RANK() | ⭐⭐ |
| 17 | Як працює MVCC в PostgreSQL | ⭐⭐⭐ |
| 18 | Що таке VACUUM в PostgreSQL | ⭐⭐⭐ |
| 19 | Навіщо потрібен ANALYZE | ⭐⭐⭐ |
| 20 | Що таке explain plan | ⭐⭐ |
| 21 | Як оптимізувати повільні запити | ⭐⭐⭐ |
🗺️ Карта залежностей тем
┌─────────────────────────────────────────┐
│ 21. Оптимізація запитів (Центральна) │
│ pg_stat_statements → EXPLAIN → Fix │
└──────┬──────────┬──────────┬────────────┘
│ │ │
┌────────────┘ │ └────────────┐
▼ ▼ ▼
┌─────────────────┐ ┌─────────────────────┐ ┌──────────────────┐
│ ІНДЕКСИ (1-6) │ │ JOIN (7-10) │ │ ПЛАНУВАЛЬНИК │
│ 1. Навіщо │ │ 7. Типи JOIN │ │ 19. ANALYZE │
│ 2. B-tree │ │ 8. INNER vs LEFT │ │ 20. EXPLAIN │
│ 3. Складний │ │ 9. JOIN vs підзапит│ │ │
│ 4. Коли створити│ │ 10. Корельований │ └────────┬─────────┘
│ 5. Недоліки │ │ підзапит │ │
│ 6. Кардинальність│ └─────────────────────┘ │
└────────┬────────┘ │
│ │
▼ ▼
┌──────────────────────────────────────────────────────────────────┐
│ MVCC та ОБСЛУГОВУВАННЯ (17-18) │
│ 17. MVCC (xmin/xmax, HOT, Hint Bits, XID Wraparound) │
│ 18. VACUUM (dead tuples, Bloat, pg_repack, autovacuum) │
└──────────────────────────────────────────────────────────────────┘
┌──────────────────────────────────────────────────────────────────┐
│ ГРУПУВАННЯ та ВІКНА (11-16) │
│ 11. WHERE vs HAVING │
│ 12. GROUP BY (HashAggregate, GroupAggregate) │
│ 13. Коли HAVING │
│ 14. Віконні функції (ROWS, RANGE, FILTER) │
│ 15. ROW_NUMBER() │
│ 16. RANK() / DENSE_RANK() │
└──────────────────────────────────────────────────────────────────┘
🎯 Рекомендований порядок вивчення
🟢 Рівень Junior (тижні 1-2)
| Крок | Тема | Файли | Мета |
|---|---|---|---|
| 1 | Основи індексів | Q1, Q4, Q5 | Навіщо потрібні, коли створювати, недоліки |
| 2 | Основи JOIN | Q7, Q8 | Типи JOIN, INNER vs LEFT |
| 3 | WHERE / HAVING / GROUP BY | Q11, Q12, Q13 | Порядок виконання, агрегація |
| 4 | MVCC базовий | Q17 (Junior) | Навіщо MVCC, UPDATE = DELETE + INSERT |
| 5 | VACUUM базовий | Q18 (Junior) | Навіщо VACUUM, звичайний vs FULL |
| 6 | EXPLAIN базовий | Q20 (Junior) | Як читати план |
| 7 | ROW_NUMBER, RANK | Q15, Q16 (Junior) | Нумерація та ранжування |
🟡 Рівень Middle (тижні 3-4)
| Крок | Тема | Файли | Мета |
|---|---|---|---|
| 1 | B-tree внутрішності | Q2, Q3, Q6 | Структура, складні, кардинальність |
| 2 | Фізичні JOIN | Q7 (Middle/Senior) | Nested Loop, Hash, Merge |
| 3 | Підзапити | Q9, Q10 | Semi-Join, SubPlan, декореляція |
| 4 | Віконні функції | Q14, Q15, Q16 | ROWS vs RANGE, FILTER, індекси |
| 5 | VACUUM деталі | Q18 (Middle) | Autovacuum, Hot Standby, Cost-based |
| 6 | ANALYZE | Q19 | Статистика, MCV, correlation |
| 7 | Оптимізація | Q21 | Алгоритм, часті проблеми |
🔴 Рівень Senior (тижні 5-6)
| Крок | Тема | Файли | Мета |
|---|---|---|---|
| 1 | B-tree internals | Q2 (Senior) | Lehman-Yao, High Key, дедуплікація |
| 2 | JOIN оптимізація | Q7 (Senior) | GEQO, Join Reordering, Memoize |
| 3 | LATERAL + Memoize | Q9, Q10 (Senior) | PG 14+, Hits/Misses, CTE |
| 4 | MVCC глибина | Q17 (Senior) | Hint Bits, HOT, SSI, CLOG, Wraparound |
| 5 | VACUUM глибина | Q18 (Senior) | Visibility Map, Parallel, pg_repack |
| 6 | ANALYZE глибина | Q19 (Senior) | Extended Statistics, Reservoir Sampling |
| 7 | EXPLAIN глибина | Q20 (Senior) | Generic vs Custom, JIT, WAL |
| 8 | Системна оптимізація | Q21 (Senior) | Plan Cache, денормалізація, pgBouncer |
🔗 Ключові зв’язки між темами
Тема: Індекси
Q1 (Навіщо) → Q2 (B-tree) → Q3 (Складний) → Q4 (Коли) → Q5 (Недоліки) → Q6 (Кардинальність)
↓
Q21 (Оптимізація запитів)
Ключові зв’язки:
- Q1 ↔ Q17 (MVCC): HOT-оновлення неможливі для індексованих колонок
- Q2 ↔ Q18 (VACUUM): Bloat → REINDEX
- Q3 ↔ Q6 (Кардинальність): порядок колонок за селективністю
- Q4 ↔ Q19 (ANALYZE): статистика впливає на вибір індекса планувальником
- Q5 ↔ Q21 (Оптимізація): Write Amplification, баланс індексів
Тема: JOIN та підзапити
Q7 (Типи JOIN) → Q8 (INNER vs LEFT) → Q9 (JOIN vs підзапит) → Q10 (Корельований)
↓
Q20 (EXPLAIN: шукайте SubPlan)
Ключові зв’язки:
- Q7 ↔ Q20 (EXPLAIN): фізичні стратегії у плані (Nested Loop, Hash, Merge)
- Q8 ↔ Q11 (WHERE vs HAVING): фільтр на правій таблиці → в ON, не в WHERE
- Q9 ↔ Q10: Semi-Join, декореляція, SubPlan vs InitPlan
- Q10 ↔ Q14 (Віконні функції): віконні функції як альтернатива корельованим підзапитам
Тема: GROUP BY та віконні функції
Q11 (WHERE vs HAVING) → Q12 (GROUP BY) → Q13 (Коли HAVING)
↓
Q14 (Віконні функції) → Q15 (ROW_NUMBER) → Q16 (RANK)
Ключові зв’язки:
- Q11 ↔ Q12: WHERE до GROUP BY, HAVING після
- Q12 ↔ Q14: віконні функції НЕ згортають рядки (на відміну від GROUP BY)
- Q13 ↔ Q14: віконні функції рахуються ПІСЛЯ HAVING → підзапит
- Q14 ↔ Q15, Q16: ROW_NUMBER, RANK, DENSE_RANK, NTILE, PERCENT_RANK
Тема: MVCC та обслуговування
Q17 (MVCC) → Q18 (VACUUM) → Q19 (ANALYZE) → Q20 (EXPLAIN) → Q21 (Оптимізація)
Ключові зв’язки:
- Q17 ↔ Q18: MVCC створює dead tuples → VACUUM видаляє
- Q18 ↔ Q19: Autovacuum запускає ANALYZE
- Q19 ↔ Q20: Застаріла статистика → wrong plan → ANALYZE
- Q20 ↔ Q21: EXPLAIN (ANALYZE, BUFFERS) — головний інструмент оптимізації
🎓 Шпаргалка: що знати для кожного рівня
🟢 Junior
- Що таке індекс і навіщо потрібен
- Типи JOIN (INNER, LEFT, RIGHT, FULL, CROSS)
- WHERE vs HAVING: коли що використовувати
- GROUP BY + агрегаційні функції (COUNT, SUM, AVG)
- MVCC: читачі не блокують писателів
- VACUUM: навіщо потрібен, звичайний vs FULL
🟡 Middle
- B-tree структура, складні індекси, правило лівого префікса
- Фізичні стратегії JOIN (Nested Loop, Hash, Merge)
- EXISTS vs JOIN, SubPlan vs InitPlan
- Віконні функції: ROW_NUMBER, RANK, LAG/LEAD
- Autovacuum: threshold, scale_factor, налаштування
- ANALYZE: статистика, n_distinct, MCV
- EXPLAIN (ANALYZE, BUFFERS): читання плану
🔴 Senior
- Lehman-Yao, High Key, дедуплікація (PG 13+), Skip Scan емуляція
- GEQO, Join Reordering, Outer Join Removal, Memoize (PG 14+)
- MVCC: Hint Bits, HOT, SSI, CLOG, XID Wraparound
- VACUUM: Visibility Map, Parallel, pg_repack, Cost-based
- Extended Statistics, Reservoir Sampling
- Generic vs Custom Plans, plan_cache_mode, JIT, WAL
- Системна оптимізація: денормалізація, pgBouncer, Partitioning
📝 Формат кожного файлу
Кожен файл містить:
- 🟢 Junior Level — базове розуміння, прості аналогії, приклади
- 🟡 Middle Level — внутрішності, типові помилки, практичні приклади
- 🔴 Senior Level — deep dive, edge cases, production experience, monitoring
- 🎯 Шпаргалка для співбесіди — ключові тези, часті питання, червоні прапорці, пов’язані теми