Что такое MVCC (Multi-Version Concurrency Control) и как PostgreSQL его реализует?

Системные колонки

Каждая строка в PostgreSQL содержит скрытые служебные поля:

• xmin — ID транзакции, создавшей эту версию строки.
• xmax — ID транзакции, удалившей или обновившей строку (0 = строка актуальна).
• ctid — физический адрес строки (блок, смещение).

SELECT ctid, xmin, xmax, id, name
FROM users
WHERE id = 1;
-- ctid=(0,1)  xmin=100  xmax=0  -- актуальная строка

-- После UPDATE:
-- старая версия: xmax = 102 (транзакция обновления)
-- новая версия:  xmin = 102, xmax = 0, ctid=(0,5)

MVCC в PostgreSQL

MVCC (Multi-Version Concurrency Control) — механизм управления конкурентным доступом без традиционных блокировок чтения. Вместо блокировки читателей при записи PostgreSQL создаёт новые версии строк. Каждая транзакция видит снимок (snapshot) базы данных, актуальный на момент её начала.

Как работает снимок

При старте транзакции PostgreSQL фиксирует:

• xmin снимка — минимальный активный XID; все транзакции ниже завершены.
• xmax снимка — следующий XID, который будет выдан; всё выше невидимо.
• список активных транзакций — их изменения тоже невидимы.

Строка видима транзакции, если:

• xmin строки завершён и не в списке активных.
• xmax строки = 0, или транзакция xmax ещё не завершена, или она в списке активных.

Уровни изоляции

-- READ COMMITTED (по умолчанию): снимок обновляется на каждый запрос
BEGIN;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- каждый SELECT видит свежие зафиксированные данные
COMMIT;

-- REPEATABLE READ: снимок фиксируется при первом запросе транзакции
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- повторный SELECT вернёт те же данные, даже если другая транзакция закоммитила изменения
COMMIT;

-- SERIALIZABLE: полная сериализуемость через SSI (Serializable Snapshot Isolation)
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- PostgreSQL отслеживает зависимости между транзакциями
-- при конфликте откатывает одну из них с ERROR 40001
COMMIT;

Table Bloat и VACUUM

UPDATE не изменяет строку на месте, а создаёт новую версию. Старые версии («мёртвые кортежи») накапливаются и занимают место. VACUUM освобождает их, VACUUM FULL перезаписывает таблицу (блокирует!).

-- Посмотреть мёртвые кортежи
SELECT relname, n_dead_tup, n_live_tup, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

-- Ручной VACUUM
VACUUM ANALYZE users;

-- Настройка autovacuum для горячей таблицы
ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor = 0.01,  -- вместо 0.2 (20%)
    autovacuum_analyze_scale_factor = 0.005
);

Transaction ID Wraparound

XID — 32-битное число, исчерпывается примерно через 4 млрд транзакций. VACUUM «замораживает» старые строки, устанавливая их xmin в FrozenTransactionId (2), чтобы они были видимы всем. Без регулярного VACUUM база рискует уйти в аварийный режим.

-- Проверить расстояние до wraparound
SELECT datname,
       age(datfrozenxid) AS xid_age,
       2^31 - age(datfrozenxid) AS xids_remaining
FROM pg_database
ORDER BY xid_age DESC;

Подводные камни

• Долгие транзакции удерживают снимок и не дают VACUUM убрать старые версии строк — таблица раздувается. Мониторьте pg_stat_activity и idle in transaction.
• XID Wraparound: если age(datfrozenxid) приближается к 1.5–2 млрд, PostgreSQL начинает выдавать предупреждения, а при 2 млрд уходит в режим «только VACUUM». Настройте мониторинг.
• VACUUM FULL перезаписывает таблицу с AccessExclusiveLock — используйте pg_repack в продакшене для неблокирующей дефрагментации.
• При уровне READ COMMITTED одна транзакция может видеть разные снимки в разных командах — это исключает phantom reads только при REPEATABLE READ.
• Оптимистичная блокировка через xmin ненадёжна: VACUUM freeze меняет xmin на константу, и ваша проверка WHERE xmin = old_xmin перестаёт работать корректно.
• SERIALIZABLE работает через SSI и добавляет overhead отслеживания зависимостей; при ошибке 40001 приложение обязано перезапускать транзакцию.
• Hot standby реплики используют те же снимки: долгий запрос на реплике тоже удерживает cleanup-горизонт через hot_standby_feedback.

• Отвечать про реализация mvcc как про абстрактный SQL без учета MVCC, WAL, planner или конкретной версии PostgreSQL.
• Переносить поведение MySQL или ClickHouse на PostgreSQL без проверки документации.
• Не связывать механизм с последствиями для индексов, транзакций, блокировок или эксплуатации.
• Показывать синтаксис, который не поддерживается PostgreSQL.

• Кандидат объясняет реализация mvcc через реальный механизм PostgreSQL, а не общими словами.
• Приводит корректный SQL или диагностический запрос для этой СУБД.
• Называет ограничения, версионные отличия или эксплуатационные последствия.
• Связывает ответ с проектированием приложения, производительностью, надежностью или безопасностью.