🟢 Junior Level

Оптимізація запитів — це процес прискорення повільних SQL-запитів.

Простий алгоритм:

1. Знайти повільний запит
2. Подивитися його план через EXPLAIN
3. Зрозуміти, що гальмує
4. Виправити (найчастіше — додати індекс)

Приклад:

-- 1. Знайти повільний запит
-- (логи додатку, pg_stat_statements)

-- 2. Подивитися план
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';

-- 3. Бачимо проблему
-- Seq Scan on users  ← читає всю таблицю!
--   Rows Removed by Filter: 999999

-- 4. Виправити
CREATE INDEX idx_users_email ON users(email);

-- Тепер:
-- Index Scan using idx_users_email  ← швидко!
-- Execution Time: 0.05 ms (було 500 ms)

Найчастіші причини повільних запитів:

• ❌ Немає індекса на полі у WHERE
• ❌ Індекс є, але не використовується (функція у WHERE)
• ❌ Вибірка занадто велика (немає LIMIT)
• ❌ JOIN без індексів на полях зв’язку

---

🟡 Middle Level

Алгоритм оптимізації

Крок 1: Знайти проблемні запити

-- pg_stat_statements — головний інструмент
CREATE EXTENSION pg_stat_statements;

-- Топ-10 найповільніших запитів
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- Топ-10 запитів з найбільшим читанням з диска
SELECT query, shared_blks_read, mean_exec_time
FROM pg_stat_statements
ORDER BY shared_blks_read DESC
LIMIT 10;

Крок 2: Проаналізувати план

EXPLAIN (ANALYZE, BUFFERS)
SELECT ...;

-- Шукайте:
-- Seq Scan на великих таблицях → потрібен індекс
-- Sort → Disk Spill → збільште work_mem
-- Nested Loop на великих таблицях → поганий план
-- rows ≠ actual rows → застаріла статистика

Крок 3: Виправити

Часті проблеми та рішення

1. Немає індекса

-- ❌ Seq Scan на 1 млн рядків
SELECT * FROM users WHERE email = 'test@example.com';

-- ✅ Створити індекс
CREATE INDEX idx_users_email ON users(email);

2. Функція у WHERE вбиває індекс

-- ❌ Індекс не використовується
SELECT * FROM users WHERE LOWER(email) = 'test@example.com';

-- ✅ Функціональний індекс
CREATE INDEX idx_users_email_lower ON users(LOWER(email));

3. Неправильний тип даних

-- ❌ Implicit cast блокує індекс
SELECT * FROM users WHERE phone = 89001234567;  -- phone = text

-- ✅ Правильний тип
SELECT * FROM users WHERE phone = '89001234567';

4. Не вистачає work_mem

-- ❌ Сортування на диску
EXPLAIN ANALYZE SELECT * FROM orders ORDER BY created_at DESC;
-- Sort Method: external merge  Disk: 5000kB

-- ✅ Збільшити work_mem
SET work_mem = '256MB';
-- Тепер: Sort Method: quicksort  Memory: 50000kB

5. JOIN без індексів

-- ❌ Hash Join без індексів
SELECT * FROM users u JOIN orders o ON u.id = o.user_id;

-- ✅ Індекс на полі зв'язку
CREATE INDEX idx_orders_user_id ON orders(user_id);
-- Тепер: Nested Loop + Index Scan → швидше

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

1. Занадто багато індексів
   • Кожен індекс сповільнює INSERT/UPDATE
   • Видаляйте невикористані: idx_scan = 0
2. SELECT *
   • Обирайте лише потрібні колонки
   • Менше даних → швидша передача
3. Проблема N+1

   -- ❌ 1000 запитів у циклі
   for user in users:
       SELECT * FROM orders WHERE user_id = user.id
   
   -- ✅ 1 запит з JOIN
   SELECT u.*, o.* FROM users u LEFT JOIN orders o ON u.id = o.user_id
   

---

🔴 Senior Level

Системний підхід до оптимізації

Рівні оптимізації:

1. SQL Level (запит)
   ├── Індекси
   ├── Рефакторинг запиту
   └── Статистика

2. Конфігурація
   ├── work_mem
   ├── shared_buffers
   ├── effective_cache_size
   └── random_page_cost

3. Архітектура
   ├── Денормалізація
   ├── Партиціювання
   ├── Materialized Views
   └── Read Replicas

4. Інфраструктура
   ├── SSD vs HDD
   ├── RAM
   ├── Connection Pooling
   └── Huge Pages

Plan Cache проблеми

Generic vs Custom Plans:

-- Проблема: запит швидкий у psql, повільний у додатку
-- Причина: JDBC використовує Prepared Statements → Generic Plan

-- Діагностика
EXPLAIN (ANALYZE) SELECT * FROM users WHERE email = $1;
-- Generic Plan: Seq Scan

-- Рішення 1: plan_cache_mode
SET plan_cache_mode = 'force_custom_plan';

-- Рішення 2: вимкнути Prepared Statements
-- JDBC URL: jdbc:postgresql://...?prepareThreshold=0

Implicit Casts

-- Перевірка неявних приведеннь типів
EXPLAIN (ANALYZE) SELECT * FROM users WHERE phone = 89001234567;
-- Filter: (phone = '89001234567'::text)  ← cast!
-- → Індекс не використовується

-- Рішення: сумісні типи
-- 1. Змінити тип колонки
ALTER TABLE users ALTER COLUMN phone TYPE bigint;

-- 2. Або передавати правильний тип
SELECT * FROM users WHERE phone = '89001234567';  -- text

Волатильність функцій

-- Функції маркуються:
-- IMMUTABLE  → завжди один результат для тих самих аргументів
-- STABLE     → один результат у межах запиту
-- VOLATILE   → може змінюватися (за замовчуванням)

-- ❌ VOLATILE функція не дозволяє оптимізації
CREATE OR REPLACE FUNCTION get_status(id int) RETURNS text AS $$
  SELECT status FROM users WHERE id = $1;
$$ LANGUAGE sql;  -- За замовчуванням VOLATILE

-- ✅ STABLE дозволяє кешування
CREATE OR REPLACE FUNCTION get_status(id int) RETURNS text AS $$
  SELECT status FROM users WHERE id = $1;
$$ LANGUAGE sql STABLE;

-- STABLE: у межах ОДНОГО запиту функція поверне той самий результат
-- для тих самих аргументів. Планувальник викличе її один раз і підставить.
-- Кеш живе до кінця запиту.

Архітектурні рішення

Денормалізація:

-- ❌ JOIN 10 таблиць для кожного запиту
SELECT u.name, o.amount, p.title, c.name as category
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN products p ON o.product_id = p.id
JOIN categories c ON p.category_id = c.id
WHERE ...

-- ✅ Denormalized колонка
ALTER TABLE orders ADD COLUMN product_title text;
ALTER TABLE orders ADD COLUMN category_name text;

-- Оновлювати через тригер
-- Запит: без JOIN → у 10 разів швидше

Materialized Views:

-- Для складних аналітичних запитів
CREATE MATERIALIZED VIEW mv_sales_summary AS
SELECT
    department,
    COUNT(*) as orders_count,
    SUM(amount) as total_sales,
    AVG(amount) as avg_order
FROM orders
GROUP BY department;

-- Оновлювати періодично
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_sales_summary;

-- Запит: миттєво
SELECT * FROM mv_sales_summary;

Partitioning:

-- Для таблиць > 100 млн рядків
CREATE TABLE orders (
    id serial,
    created_at date,
    amount numeric
) PARTITION BY RANGE (created_at);

CREATE TABLE orders_y2023 PARTITION OF orders
    FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

CREATE TABLE orders_y2024 PARTITION OF orders
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

-- Partition Pruning (відсікання партицій): планувальник виключає зайві
-- партиції з плану. Дані з них навіть не читаються з диска.
-- WHERE created_at > '2024-01-01' → сканує лише orders_y2024

Системний тюнінг

Параметри для SSD:

-- Планувальник повинен довіряти кешу
SET random_page_cost = 1.1;       -- За замовчуванням 4.0 (для HDD)
SET effective_cache_size = '24GB'; -- 75% RAM
SET shared_buffers = '8GB';       -- 25% RAM

Connection Pooling:

pgBouncer у режимі transaction:
  - 1000 з'єднань додатку → 50 з'єднань до БД
  - Економія 30% CPU на перемиканні контексту
  - Менше пам'яті на сесії

Huge Pages:

ОС + PostgreSQL:
  - Huge Pages = 2MB сторінки замість 4KB
  - Менше TLB misses
    (TLB — Translation Lookaside Buffer, кеш процесора для адрес.
    При 4KB для 8GB RAM → 2 млн записів TLB (не влазить).
    При 2MB → 4000 записів (все у TLB))
  - +10-20% продуктивності на високих навантаженнях

Алгоритм Senior-розробника

1. pg_stat_statements → знайти запити з високим shared_blks_read
2. EXPLAIN (ANALYZE, BUFFERS) → знайти bottleneck вузол
3. Перевірити блокування: pg_blocking_pids()
4. Перевірити статистику: rows ≠ actual → ANALYZE
5. Перевірити work_mem: Disk Spill → збільшити
6. Перевірити plan_cache_mode: Generic Plan проблеми
7. Рефакторинг: денормалізація, партиціювання, кешування
8. Якщо SQL оптимальний → шардування (Citus)

Коли НЕ використовувати архітектурні рішення

• Денормалізація: НЕ використовуйте при частих UPDATE денормалізованих полів (конфлікти тригерів, розсинхронізація)
• Materialized Views: НЕ використовуйте для даних, які мають бути актуальними у реальному часі (затримка між REFRESH)
• Partitioning: НЕ використовуйте для таблиць < 10 млн рядків — overhead управління партиціями переважає вигоду

Production Experience

Реальний сценарій #1: Комплексна оптимізація

• SaaS: API > 2 секунд (p95)
• Аналіз:
  1. N+1 запити (100 запитів на сторінку)
  2. Seq Scan на таблиці 50 млн рядків
  3. work_mem = 4MB → spill на диск
  4. Generic Plan у Hibernate
• Рішення:
  1. DataLoader → 5 запитів замість 100
  2. Індекс на hot полях
  3. work_mem = 256MB
  4. plan_cache_mode = force_custom_plan
• Результат: p95 < 200ms (прискорення у 10 разів)

Реальний сценарій #2: Архітектурні зміни

• Аналітика: звіт 5 хвилин (JOIN 12 таблиць)
• Рішення:
  • Materialized View з REFRESH кожні 5 хвилин
  • Denormalized колонки для hot полів
  • Партиціювання за датою
• Результат: звіт < 1 секунди

Monitoring Dashboard

-- 1. Топ повільних запитів
SELECT
    query,
    calls,
    mean_exec_time,
    total_exec_time,
    shared_blks_read,
    rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- 2. Блокування
SELECT
    blocked_locks.pid AS blocked_pid,
    blocked_activity.query AS blocked_query,
    blocking_locks.pid AS blocking_pid,
    blocking_activity.query AS blocking_query
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

-- 3. Bloat
SELECT
    relname,
    n_dead_tup,
    n_live_tup,
    ROUND(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) as dead_pct
FROM pg_stat_user_tables
WHERE n_live_tup > 10000
ORDER BY dead_pct DESC;

-- 4. Cache Hit Rate
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% → чудово

Best Practices

1. Моніторте через pg_stat_statements постійно
2. EXPLAIN (ANALYZE, BUFFERS) для кожного проблемного запиту
3. Індекси — перше рішення, але не єдине
4. work_mem контролюйте при Sort/Hash
5. plan_cache_mode перевіряйте при використанні ORM
6. Денормалізація для hot-path запитів
7. Materialized Views для аналітики
8. pgBouncer рекомендований при > 50 підключеннях. Для малих проєктів (< 10 користувачів) можна обійтися без нього.
9. Partitioning для таблиць > 100 млн рядків
10. Cache Hit Ratio > 99% — мета

Резюме для Senior

• Оптимізація — не лише індекси, це системний процес
• Plan Cache (Generic vs Custom) — часта причина розбіжностей prod/dev
• Implicit Casts блокують індекси → сумісні типи
• Денормалізація для hot-path, MV для аналітики
• work_mem контролюйте → Disk Spill = катастрофа
• pgBouncer у transaction mode → економія 30% CPU
• Cache Hit Ratio > 99% → головна метрика здоров’я БД
• Partitioning + Partition Pruning для великих таблиць
• Write Amplification (посилення запису): кожен INSERT/UPDATE записує не лише в таблицю, але й у ВСІ індекси. 10 індексів = запис × 10. Фізично записується в 10x більше даних, ніж змінив користувач.

---

🎯 Шпаргалка для інтерв’ю

Обов’язково знати:

• Алгоритм: pg_stat_statements → EXPLAIN (ANALYZE, BUFFERS) → виправити → перевірити
• Часті проблеми: немає індекса, функція у WHERE, неявний cast, spill на диск, N+1
• Generic vs Custom Plans: Prepared Statements → plan_cache_mode = force_custom_plan
• Implicit Casts: phone = 8900... (int) vs phone = '8900...' (text) → cast блокує індекс
• Волатильність функцій: IMMUTABLE, STABLE, VOLATILE → впливає на оптимізацію
• Денормалізація: гарячі колонки → тригер для оновлення
• Materialized Views: складні аналітичні запити → REFRESH CONCURRENTLY
• Partitioning: таблиці > 100 млн рядків → Partition Pruning
• Connection Pooling: pgBouncer у transaction mode → 1000 з’єднань → 50 до БД
• Системний тюнінг: random_page_cost = 1.1 (SSD), shared_buffers = 25% RAM
• Cache Hit Ratio > 99% — мета; < 95% → проблема

Часті уточнюючі запитання:

• «Як знайти повільні запити?» → pg_stat_statements ORDER BY total_exec_time DESC
• «Чому запит швидкий у консолі, повільний у додатку?» → Generic Plan
• «Що робити при Disk Spill?» → SET work_mem = ‘256MB’ для сесії
• «Коли денормалізація виправдана?» → Hot-path запити, JOIN 10+ таблиць

Червоні прапорці (НЕ говорити):

• ❌ «Оптимізація = лише індекси» (системний процес: SQL, конфіг, архітектура, інфраструктура)
• ❌ «plan_cache_mode не важливий» (Generic Plan — часта причина розбіжностей)
• ❌ «Materialized Views для OLTP» (лише для аналітики!)
• ❌ «pgBouncer не потрібен» (1000 з’єднань = 30% CPU на перемиканні)

Пов’язані теми:

• [[Для чого потрібні індекси]] → перше рішення для оптимізації
• [[Що таке explain plan]] → EXPLAIN (ANALYZE, BUFFERS)
• [[Навіщо потрібен ANALYZE]] → статистика для планувальника
• [[Як працює MVCC в PostgreSQL]] → Bloat і довгі транзакції