Java Interview Questions

Главная / Все ответы / Базы данных SQL PostgreSQL

Вопрос 17 · Раздел 1

Как работает MVCC в PostgreSQL?

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

Russian Базы данных SQL PostgreSQL Исходный Markdown
Версии по языкам: English Russian Ukrainian

🟢 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