🟢 Junior Level

MVCC (Multi-Version Concurrency Control) — механізм, який дозволяє PostgreSQL показувати кожному користувачу свою версію даних, не блокуючи читання при запису.

Проста аналогія: Уявіть Google Docs. Коли ви редагуєте документ, інші бачать стару версію, поки ви не збережете. При цьому вони можуть продовжувати читати документ — їм не потрібно чекати, поки ви закінчите.

Відмінність від інших СУБД: Oracle використовує undo-сегменти, SQL Server — tempdb. PostgreSQL зберігає ВСІ версії в тій самій таблиці → простіше, але bloat від UPDATE/DELETE.

Основна ідея:

• При UPDATE PostgreSQL не перезаписує рядок, а створює нову версію
• При DELETE рядок не видаляється фізично, а позначається як видалений
• Кожна транзакція бачить лише ті рядки, які існували на момент її початку

Приклад:

-- Транзакція 1                  -- Транзакція 2
BEGIN;                           BEGIN;
SELECT * FROM users WHERE id=1;  -- Бачить: name='Ivan'

UPDATE users SET name='Petr'
WHERE id=1;                      -- Все ще бачить: name='Ivan'!

SELECT * FROM users WHERE id=1;  -- Бачить: name='Petr'
COMMIT;

                                 SELECT * FROM users WHERE id=1;
                                 -- Тепер бачить: name='Petr'

Навіщо це потрібно:

• Читачі не блокують письменників
• Письменники не блокують читачів
• Немає проблеми “брудного читання”

---

🟡 Middle Level

Як це працює всередині

Кожен рядок (tuple) містить приховані системні поля:

Поле	Опис
xmin	ID транзакції, яка створила рядок
xmax	ID транзакції, яка видалила рядок (0 = не видалена)
ctid	Фізична адреса рядка (page_number, row_number)

Механізм:

-- Початковий стан
-- users таблиця:
-- xmin=100, xmax=0, name='Ivan', ctid=(1,1)

-- Транзакція 101 робить UPDATE
UPDATE users SET name='Petr' WHERE id=1;

-- Нова версія рядка:
-- xmin=101, xmax=0, name='Petr', ctid=(1,2)
-- Стара версія:
-- xmin=100, xmax=101, name='Ivan', ctid=(1,1) ← xmax=101 (позначена)

-- Транзакція 102 (почалася до 101):
-- → Бачить рядок з xmin=100 (xmax=101 ще не зафіксований для неї)
-- → name='Ivan'

-- Транзакція 103 (почалася після 101):
-- → Бачить рядок з xmin=101 (зафіксований)
-- → name='Petr'

Snapshots (Знімки)

Коли транзакція починає SELECT, вона робить знімок стану БД:

Snapshot:
  xmin = 100  ← Всі транзакції < 100 зафіксовані
  xmax = 105  ← Всі транзакції >= 105 ще не почалися
  xip_list = [101, 102, 103]  ← Активні транзакції

Правило видимості:
  Рядок видно, якщо:
    - його xmin < snapshot.xmin АБО xmin у xip_list (ще активна)
    - його xmax = 0 АБО xmax >= snapshot.xmax

UPDATE = DELETE + INSERT

-- Це не оновлення "на місці"!
UPDATE users SET name='Petr' WHERE id=1;

-- Фактично:
-- 1. Створюється НОВА версія рядка (name='Petr')
-- 2. Стара версія позначається (xmax = поточна транзакція)
-- 3. Індекси оновлюються (посилання на новий ctid)

VACUUM: очищення мертвих рядків

-- Після UPDATE/DELETE залишаються "мертві" рядки (dead tuples)
-- VACUUM видаляє їх, звільняючи місце

-- Перевірка мертвих рядків
SELECT
    schemaname,
    relname,
    n_live_tup,    -- Живі рядки
    n_dead_tup,    -- Мертві рядки
    last_vacuum
FROM pg_stat_user_tables
WHERE relname = 'users';

-- Ручний запуск
VACUUM users;

Типові проблеми

1. Bloat (роздування)

   -- Таблиця займає у 10 разів більше місця, ніж повинна
   -- Причина: багато dead tuples, VACUUM не встигає
   
   -- Рішення
   VACUUM FULL users;  -- ⚠️ Блокує таблицю!
   -- або
   pg_repack -t users; -- Без блокування
   

2. Довгі транзакції заважають VACUUM

   -- Якщо транзакція відкрита 2 години
   -- VACUUM не може видалити рядки, які вона бачить
   
   -- Знайти довгі транзакції
   SELECT pid, now() - xact_start as duration, query
   FROM pg_stat_activity
   WHERE state = 'active'
   ORDER BY xact_start ASC;
   

3. Transaction ID Wraparound

   -- XID — 32-бітне число (4 мільярди)
   -- Якщо не "заморожувати" старі рядки → переповнення
   
   -- Перевірка віку
   SELECT datname, age(datfrozenxid)
   FROM pg_database
   ORDER BY age DESC;
   
   -- > 200 млн → терміново VACUUM!
   -- > 1.5 млрд → база перейде у READ ONLY
   

---

🔴 Senior Level

Hint Bits оптимізація

Проблема: Перевірка статусу транзакції через CLOG (Commit Log) — дорога операція (читання з диска).

CLOG — файли у pg_xact/, де кожна транзакція = 2 біти (IN_PROGRESS/COMMITTED/ABORTED). Це НЕ WAL. Читання CLOG = мікросекунди, але при мільйонах рядків → секунди.

Рішення: PostgreSQL використовує Hint Bits — 2 біти у заголовку рядка:

Heap Tuple Header:
  ...
  t_infomask:
    HEAP_XMIN_COMMITTED    ← xmin зафіксований
    HEAP_XMIN_INVALID      ← xmin скасований
    HEAP_XMAX_COMMITTED    ← xmax зафіксований
    HEAP_XMAX_INVALID      ← xmax скасований
  ...

Механізм:

1. Перший читач перевіряє статус xmin у CLOG
2. Проставляє Hint Bits у заголовку рядка
3. Всі наступні читачі перевіряють Hint Bits миттєво
   → Без звернення до CLOG!

Це називається "Hint Bits propagation" або "Lazy hinting"

Важливо: Hint Bits не WAL-logged! Після crash вони перераховуються заново.

Multixacts

Коли кілька транзакцій блокують один рядок (SELECT FOR SHARE):

-- Транзакція 1: SELECT FOR SHARE → блокування
-- Транзакція 2: SELECT FOR SHARE → теж блокування

-- xmax не може зберігати два ID!
-- Рішення: MultiXact ID

-- В xmax записується посилання на pg_multixact
-- pg_multixact зберігає список транзакцій: [101, 102]

Проблема: Multixact старіє так само, як XID → потрібен VACUUM.

Serializable Snapshot Isolation (SSI)

PostgreSQL реалізує SERIALIZABLE без блокувань читання:

-- Рівень SERIALIZABLE в PG ≠ SERIALIZABLE в інших СУБД
-- PG використовує SSI (Serializable Snapshot Isolation)

-- Механізм:
-- 1. Відстежує SIRead locks (предикатні блокування)
-- 2. Виявляє RW-conflict cycles
-- 3. Якщо цикл знайдено → скасовує одну транзакцію з помилкою:
--    ERROR: could not serialize access due to read/write dependencies

Приклад:

-- Транзакція 1                          -- Транзакція 2
SET TRANSACTION ISOLATION LEVEL
  SERIALIZABLE;                          SET TRANSACTION ISOLATION LEVEL
                                           SERIALIZABLE;
SELECT SUM(balance) FROM accounts;
                                         UPDATE accounts SET balance = 1000
                                           WHERE id = 1;
UPDATE accounts SET balance = 500
  WHERE id = 2;
COMMIT;
                                         COMMIT;  ← ERROR: serialization failure!

⚠️ НЕ використовуйте SERIALIZABLE у високонавантажених OLTP: при частих RW-conflict транзакції постійно відкочуються. Використовуйте READ COMMITTED або REPEATABLE READ.

HOT (Heap Only Tuple) оновлення

Heap = основне сховище рядків таблиці (не індекс). «Heap Only Tuple» = нова версія на тій самій сторінці, індекси не оновлюються.

Механізм HOT:

Критична оптимізація для UPDATE:

-- Звичайний UPDATE:
-- 1. Новий рядок у Heap
-- 2. Оновити ВСІ індекси (новий ctid)
-- 3. Старий рядок позначити (xmax)

-- HOT UPDATE (якщо виконані умови):
-- 1. Новий рядок у Heap (на тій самій сторінці!)
-- 2. Індекси НЕ оновлюються
-- 3. Створюється HOT-ланцюжок: old → new

-- Умови для HOT:
-- - Колонки, що оновлюються, НЕ індексовані
-- - На сторінці Heap є місце (fillfactor < 100)

Моніторинг HOT:

SELECT
    relname,
    n_tup_hot_update,     -- HOT оновлення
    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 > 0;

-- hot_pct > 70% → чудово
-- hot_pct < 20% → проблема (забагато індексованих оновлень)

CLOG (pg_xact)

pg_xact/ — директорія з файлами по 256 КБ
Кожна транзакція = 2 біти:
  00 = IN_PROGRESS
  01 = COMMITTED
  10 = ABORTED
  11 = SUB_COMMITTED

-- 4 транзакції в 1 байті
-- 256 КБ = 1 мільярд транзакцій
-- Файли видаляються після VACUUM + Freeze

XID Wraparound: глибокий аналіз

-- 32-бітний XID = 4,294,967,296 транзакцій
-- Порівняння XID: циклічна арифметика

-- autovacuum робить "Freezing":
-- 1. Знаходить рядки з xmin < freeze_limit
-- 2. Замінює xmin на FrozenXID (спеціальне значення)
-- 3. FrozenXID завжди вважається "у минулому"
-- «Заморозка» = заміна xmin на FrozenXID, який ЗАВЖДИ «у минулому».
-- Заморожений рядок видно будь-якій транзакції. Як дата «1900 рік» на
-- документі — гарантовано «старша» за будь-яку поточну транзакцію.

-- Моніторинг:
SELECT
    datname,
    age(datfrozenxid),
    ROUND(100.0 * age(datfrozenxid) / 2000000000, 2) as pct_to_wraparound
FROM pg_database;

-- > 1.5 млрд = 75% → критично!
-- 2 млрд = 100% → база READ ONLY

Edge Cases

1. Long-running transactions

   -- На репліці: hot_standby_feedback = on
   -- Репліка просить майстер не видаляти рядки, які вона читає
   -- → Майстер копить dead tuples → Bloat!
   

2. TOAST та MVCC

   -- Великі поля (text, bytea) зберігаються у TOAST-таблиці
   -- TOAST-рядки теж версіонуються
   -- Але TOAST не використовує MVCC для читання!
   -- → Може повернути дані видаленого рядка у рідкісних випадках
   

3. Visibility Map

   -- VM: бітова карта сторінок, де ВСІ рядки видимі всім
   -- Використовується для:
   -- 1. Index Only Scan (перевірка видимості без Heap)
   -- 2. VACUUM (пропуск "чистих" сторінок)
   
   -- Оновлюється при VACUUM
   

Performance Impact

Операція	MVCC вплив
SELECT	Не блокується, створює Snapshot
INSERT	Створює новий рядок (xmin = поточна)
UPDATE	Створює новий + позначає старий (xmax)
DELETE	Позначає рядок (xmax = поточна)
VACUUM	Видаляє рядки з xmax < oldest_active_xid

Production Experience

Реальний сценарій #1: Bloat від довгої транзакції

• Аналітик відкрив транзакцію і пішов на обід (4 години)
• VACUUM не міг видалити dead tuples
• Таблиця роздулася з 10 ГБ до 80 ГБ
• Рішення: SELECT pg_terminate_backend(pid) → VACUUM FULL

Реальний сценарій #2: Wraparound crisis

• autovacuum був вимкнений “для продуктивності”
• age(datfrozenxid) = 1.9 млрд (95%)
• База почала попереджати у логах
• Рішення: терміновий VACUUM FREEZE на всій БД
• Урок: ніколи не вимикайте autovacuum!

Monitoring

-- 1. Dead tuples
SELECT relname, n_dead_tup, last_vacuum, last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

-- 2. XID age
SELECT datname, age(datfrozenxid)
FROM pg_database
ORDER BY age DESC;

-- 3. Long transactions
SELECT pid, now() - xact_start as duration, state, query
FROM pg_stat_activity
WHERE xact_start < now() - INTERVAL '1 hour'
ORDER BY xact_start;

-- 4. HOT ratio
SELECT
    relname,
    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;

Best Practices

1. Не тримайте транзакції відкритими довше необхідності
2. autovacuum — НЕ вимикати! Налаштовуйте, але не вимикайте
3. fillfactor = 70-80 для часто оновлюваних таблиць → більше HOT
4. Індексуйте усвідомлено — кожен індекс вбиває HOT для цієї колонки
5. Моніторте age(datfrozenxid) — запобігайте wraparound
6. pg_repack для боротьби з Bloat без блокування
7. hot_standby_feedback = on → моніторте Bloat на майстрі
8. VACUUM FULL лише у maintenance window (блокує!)

Резюме для Senior

• MVCC = версія рядка = xmin/xmax, не блокує читання
• Hint Bits прискорюють повторні читання без CLOG
• HOT = оптимізація UPDATE без оновлення індексів
• SSI = SERIALIZABLE без блокувань читання (PG унікальний!)
• XID Wraparound — реальна загроза → моніторте age()
• Довгі транзакції = головна причина Bloat
• VACUUM не зменшує файли → використовуйте pg_repack
• Visibility Map критичний для Index Only Scan

---

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

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

• MVCC: кожен рядок має xmin (створена) та xmax (видалена)
• Snapshot: транзакція бачить рядки, що існували на момент початку
• UPDATE = DELETE + INSERT (нова версія рядка, стара → dead tuple)
• Hint Bits: 2 біти у заголовку рядка → уникаємо читання CLOG після першого читача
• HOT (Heap Only Tuple): UPDATE без оновлення індексів (якщо колонка не індексована + є місце на сторінці)
• SSI (Serializable Snapshot Isolation): SERIALIZABLE без блокувань читання
• XID Wraparound: 32-біт → 4 млрд транзакцій → autovacuum робить Freeze
• Visibility Map: бітова карта «чистих» сторінок → Index Only Scan + VACUUM skip
• CLOG (pg_xact): 2 біти на транзакцію → IN_PROGRESS/COMMITTED/ABORTED
• Довгі транзакції → VACUUM не може видалити dead tuples → Bloat

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

• «Що буде, якщо вимкнути autovacuum?» → XID Wraparound → база перейде у READ ONLY
• «Чому VACUUM не стискає файли?» → Звільняє місце для нових даних, не повертає ОС
• «Як працює HOT?» → Нова версія на тій самій сторінці → індекси не оновлюються
• «Що таке hot_standby_feedback?» → Репліка просить майстер не видаляти рядки → Bloat на майстрі

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

• ❌ «VACUUM стискає файли таблиць» (ні, потрібен VACUUM FULL або pg_repack)
• ❌ «autovacuum можна вимкнути для продуктивності» (Wraparound → READ ONLY!)
• ❌ «MVCC блокує читання при запису» (читачі не блокують письменників!)
• ❌ «Hint Bits WAL-logged» (ні, після crash перераховуються)

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

• [[Що таке VACUUM в PostgreSQL]] → очищення dead tuples
• [[Навіщо потрібен ANALYZE]] → збір статистики
• [[Для чого потрібні індекси]] → HOT та Visibility Map