MVCC в PostgreSQL: версии строк, видимость, VACUUM и bloat#

Модуль: Базы данных · Уровень: Senior

TL;DR#

• PostgreSQL использует MVCC (Multi-Version Concurrency Control): каждое изменение строки создаёт новую физическую версию (tuple), а старая помечается «мёртвой», но физически остаётся на странице до очистки.
• Видимость версий определяется системными полями xmin (транзакция-создатель), xmax (транзакция-удалитель), cmin/cmax (command id внутри транзакции) и снапшотом транзакции (xmin, xmax, список активных xip).
• Главное следствие: читатели не блокируют писателей, а писатели не блокируют читателей — в отличие от блокировочников (2PL, MySQL MyISAM с table-level locks).
• Цена MVCC — dead tuples и bloat (раздувание таблиц/индексов). Их убирает VACUUM (помечает место как переиспользуемое + freezing против wraparound) и autovacuum (автоматически).
• VACUUM обычный не отдаёт место ОС и не блокирует; VACUUM FULL переписывает таблицу (ACCESS EXCLUSIVE lock), отдаёт место, но кладёт нагрузку. Для онлайн-дефрагментации — pg_repack.
• 32-битный xid (2^31 «прошлых» транзакций) требует freezing, иначе transaction id wraparound = катастрофа. HOT updates снижают bloat индексов.

Простыми словами#

MVCC — способ обойтись без блокировок при чтении: каждое изменение строки создаёт её новую версию, а старая продолжает жить, пока её кто-то ещё видит.

Отсюда фирменное свойство PostgreSQL, которое стоит уметь сформулировать одной фразой: читатели не блокируют писателей, писатели не блокируют читателей. Длинный отчёт не мешает обновлять те же строки, потому что он смотрит на свою, зафиксированную на момент старта версию данных.

Плата за это — мусор. Старые версии остаются лежать на диске, и убирать их приходит VACUUM. Если он не успевает или его держит долгая транзакция, таблица и индексы распухают: места на диске всё больше, а данных столько же.

Второе следствие, которое удивляет пришедших из других СУБД: UPDATE здесь — это фактически вставка новой версии строки плюс пометка старой. Обновить одну колонку стоит почти столько же, сколько вставить строку целиком, и все индексы таблицы тоже придётся обновить (частично от этого спасает механизм HOT).

Если ты с другого языка. В Oracle тоже MVCC, но старые версии живут в отдельном сегменте отката, а не вперемешку с данными, поэтому там нет разговоров про bloat и autovacuum. В MySQL с InnoDB — похожая схема с undo-логом. Специфика PostgreSQL именно в том, что мусор лежит прямо в таблице.

Словарь этой статьи:

• tuple (кортеж) — конкретная физическая версия строки;
• xmin / xmax — номера транзакций, создавшей и удалившей версию; по ним и определяется видимость;
• dead tuple — мёртвая версия, которую уже никто не видит;
• bloat — раздувание таблицы или индекса из-за неубранных мёртвых версий;
• freezing — пометка очень старых строк как «видимых всем», защита от переполнения счётчика транзакций.

Теория#

Зачем нужен MVCC: блокировочники vs многоверсионники#

В классических блокировочных СУБД (двухфазная блокировка, 2PL) согласованность чтения обеспечивается блокировками: транзакция, читающая строку, ставит shared-lock, что мешает писателям; писатель ставит exclusive-lock, что мешает читателям. Крайний случай — MySQL MyISAM, где блокировки уровня таблицы: один писатель останавливает всех.

MVCC решает это иначе: вместо того чтобы блокировать данные, СУБД хранит несколько версий одной логической строки. Каждая транзакция видит «свой» консистентный срез данных на момент старта (или старта запроса). Поэтому:

Важный нюанс: writer-writer одной и той же строки всё равно блокируются друг другом в PostgreSQL — это row-level lock. MVCC устраняет конфликты read↔write, но не write↔write по одной строке.

Физическое устройство: tuple и системные поля#

Таблица в PostgreSQL — это набор страниц (page, по умолчанию 8 КБ). Каждая строка хранится как heap tuple с заголовком, где есть системные (скрытые) поля. Их можно увидеть напрямую:

SELECT ctid, xmin, xmax, cmin, cmax, * FROM accounts;

Дополнительно в заголовке есть t_infomask с hint bits (HEAP_XMIN_COMMITTED, HEAP_XMAX_COMMITTED и т.д.) — кэш статуса коммита, чтобы не дёргать clog/commit log (а ныне pg_xact) на каждое чтение.

Важно: ctid не стабилен — он меняется при UPDATE и VACUUM FULL/pg_repack. Использовать его как «первичный ключ» нельзя, но он удобен для точечного доступа в рамках одной транзакции.

Как UPDATE и DELETE работают на самом деле#

В PostgreSQL нет in-place update для версий, видимых другим транзакциям.

• INSERT: создаётся новый tuple с xmin = текущий txid, xmax = 0.
• DELETE: существующий tuple не удаляется физически — ему проставляется xmax = текущий txid. Версия становится «мёртвой» после коммита.
• UPDATE = DELETE + INSERT: старой версии проставляется xmax = txid, создаётся новая версия с xmin = txid. У старой версии в заголовке остаётся ссылка (t_ctid) на новую — это нужно для EvalPlanQual (повторной проверки при write-write конфликте).

-- Демонстрация смены версии
BEGIN;
SELECT ctid, xmin, xmax FROM accounts WHERE id = 1;  -- (0,1) | 1001 | 0
UPDATE accounts SET balance = balance + 100 WHERE id = 1;
SELECT ctid, xmin, xmax FROM accounts WHERE id = 1;  -- (0,2) | 1002 | 0  (новая версия)
COMMIT;
-- старая версия (0,1) теперь dead: её xmax = 1002

Следствие: частые UPDATE на «горячих» строках = поток мёртвых версий = bloat. Это типичная причина деградации (счётчики, статусы, очереди в таблице).

Снапшоты и правила видимости#

Снапшот (snapshot) — это «фотография» состояния транзакций на определённый момент. Состоит из трёх частей:

• xmin снапшота — самый младший ещё активный txid; всё, что меньше и закоммичено, точно видимо.
• xmax снапшота — следующий неназначенный txid (граница «будущего»); всё >= xmax невидимо.
• xip[] (in-progress) — список txid, активных на момент снапшота (между xmin и xmax), которые невидимы.

        видимо (если committed)        невидимо
  ┌──────────────────────────────┐  ┌─────────────┐
  │  txid < snapshot.xmin        │  │ txid >= xmax │
  └──────────────────────────────┘  └─────────────┘
           ▲ из диапазона [xmin, xmax) невидимы те, что в xip[]

Версия tuple видима транзакции, если упрощённо:

1. xmin закоммичен и виден по снапшоту (создатель завершился до снапшота и не в xip), И
2. xmax либо 0/aborted, либо не виден по снапшоту (удалитель ещё не завершился относительно снапшота).

Это реализует функция HeapTupleSatisfiesMVCC. Статус коммита транзакции берётся из pg_xact (бывший clog) и кэшируется hint-битами.

SELECT txid_current();                 -- текущий txid (64-битный счётчик-обёртка над 32-битным xid)
SELECT * FROM txid_current_snapshot(); -- xmin:xmax:xip_list, например 1005:1010:1006,1008
SELECT pg_current_xact_id();           -- PG 13+, аналог txid_current()

Уровни изоляции и снапшоты#

• READ COMMITTED (дефолт): новый снапшот берётся на каждую команду. Поэтому в одной транзакции два SELECT могут увидеть разные данные (non-repeatable read).
• REPEATABLE READ: снапшот берётся один раз на первую команду и держится всю транзакцию — стабильный срез. В PostgreSQL это фактически snapshot isolation (нет phantom reads).
• SERIALIZABLE: REPEATABLE READ + SSI (Serializable Snapshot Isolation) — отслеживание опасных паттернов чтения/записи (rw-dependencies), может откатить транзакцию с serialization_failure (SQLSTATE 40001).

// pgx: явный уровень изоляции + обязательный retry на 40001
tx, err := pool.BeginTx(ctx, pgx.TxOptions{
    IsoLevel: pgx.Serializable, // или pgx.RepeatableRead
})

// Паттерн retry для SERIALIZABLE / serialization_failure
func withRetry(ctx context.Context, pool *pgxpool.Pool, fn func(pgx.Tx) error) error {
    for attempt := 0; attempt < 5; attempt++ {
        tx, err := pool.BeginTx(ctx, pgx.TxOptions{IsoLevel: pgx.Serializable})
        if err != nil {
            return err
        }
        err = fn(tx)
        if err == nil {
            if err = tx.Commit(ctx); err == nil {
                return nil
            }
        }
        _ = tx.Rollback(ctx)

        var pgErr *pgconn.PgError
        if errors.As(err, &pgErr) && (pgErr.Code == "40001" || pgErr.Code == "40P01") {
            continue // 40001 serialization_failure, 40P01 deadlock_detected
        }
        return err
    }
    return errors.New("retry limit exceeded")
}

// database/sql: уровни через sql.TxOptions
tx, err := db.BeginTx(ctx, &sql.TxOptions{Isolation: sql.LevelSerializable})

txid, 32-битный xid и freezing#

Внутри xid — 32-битное беззнаковое число. Оно по кругу «обёртывается» (wraparound). Видимость работает по принципу «половина прошлого / половина будущего»: для любого xid примерно 2^31 значений считаются прошлыми (видимыми), 2^31 — будущими.

Проблема: если очень старый tuple не «заморозить», то спустя ~2 млрд транзакций его xmin окажется в «будущем» и строка внезапно исчезнет из видимости — это transaction id wraparound, потенциальная потеря данных.

Freezing — это пометка старых, видимых всем версий как «вечно прошлых». Раньше xmin заменялся на спец-значение FrozenTransactionId (2); в современных версиях ставится hint-bit HEAP_XMIN_FROZEN (xid остаётся для отладки). Замороженная версия видна всем всегда.

-- Сколько транзакций осталось до принудительного wraparound-vacuum
SELECT datname,
       age(datfrozenxid) AS xid_age,
       2^31 - age(datfrozenxid) AS xids_left
FROM pg_database ORDER BY xid_age DESC;

Ключевые параметры:

• vacuum_freeze_min_age — возраст, с которого tuple можно замораживать.
• autovacuum_freeze_max_age (дефолт 200 млн) — при достижении возраста запускается обязательный anti-wraparound autovacuum, который нельзя «отменить» (он перетерпит даже autovacuum = off).
• При приближении к лимиту PostgreSQL начинает кричать в лог, а потом переходит в режим только-чтение, чтобы не потерять данные. Это известный «инцидент Sentry/Mailchimp».

В PG 14+ механизм частично переработан: vacuum_failsafe_age и более агрессивное замораживание, чтобы не доводить до read-only.

VACUUM: что делает обычный VACUUM#

Обычный VACUUM (и autovacuum):

1. Находит dead tuples (чьи xmax закоммичен и не виден ни одному снапшоту по OldestXmin горизонту).
2. Помечает занятое ими место как переиспользуемое в пределах той же страницы (через free space map, FSM). Место ОС не возвращается (за исключением полностью пустых страниц в хвосте таблицы).
3. Чистит соответствующие записи в индексах.
4. Выполняет freezing старых версий.
5. Обновляет Visibility Map (страницы, где все версии видимы всем) — это включает Index-Only Scans и ускоряет будущие vacuum.

VACUUM берёт лишь SHARE UPDATE EXCLUSIVE lock — не блокирует SELECT/INSERT/UPDATE/DELETE, конфликтует только с DDL и другим vacuum по той же таблице.

VACUUM (VERBOSE, ANALYZE) accounts;   -- очистка + обновление статистики планировщика
VACUUM (FREEZE) accounts;             -- агрессивная заморозка

ANALYZE (часть VACUUM ANALYZE) — это отдельная вещь: обновляет статистику в pg_statistic для планировщика, к очистке отношения не имеет, но часто запускается вместе.

VACUUM FULL vs обычный VACUUM#

VACUUM FULL нельзя использовать на проде «вживую» под нагрузкой — он остановит все запросы к таблице. Для онлайн-сценария — pg_repack.

autovacuum#

Фоновый процесс, запускающий VACUUM/ANALYZE по порогам. Триггер для vacuum по умолчанию:

threshold = autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * n_live_tuples

• autovacuum_vacuum_scale_factor = 0.2 (20% мёртвых) — для больших таблиц это слишком много, часто снижают до 0.01–0.05 пер-таблично.
• autovacuum_vacuum_cost_limit / autovacuum_vacuum_cost_delay — троттлинг I/O.
• autovacuum_max_workers — число параллельных воркеров.

-- Пер-табличная настройка для горячей таблицы
ALTER TABLE events SET (
    autovacuum_vacuum_scale_factor = 0.02,
    autovacuum_vacuum_cost_delay   = 2,
    autovacuum_vacuum_cost_limit   = 1000
);

Типичная senior-проблема: на больших, активно обновляемых таблицах дефолтный autovacuum не успевает, копится bloat и растёт dead_tuples; лечится агрессивными пер-табличными настройками, а не глобальным выключением.

Bloat: раздувание таблиц и индексов#

Bloat — это «пустое» место внутри файлов таблицы/индекса от мёртвых версий, которое не отдано ОС и которое всё равно читается с диска и кэшируется в shared_buffers.

Причины:

• Массовые/частые UPDATE и DELETE.
• Долгие транзакции и зависшие idle in transaction / открытые курсоры / реплики с hot_standby_feedback: они держат снапшот, поэтому vacuum не может удалить версии новее самого старого снапшота (горизонт xmin). Это причина №1 «vacuum работает, а bloat растёт».
• Откаты больших операций.

Диагностика:

SELECT relname,
       n_live_tup, n_dead_tup,
       round(n_dead_tup::numeric / nullif(n_live_tup,0), 3) AS dead_ratio,
       last_vacuum, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

-- Кто держит старый снапшот и мешает vacuum
SELECT pid, state, age(backend_xmin) AS xmin_age, query
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC;

-- Самая старая активная транзакция
SELECT max(age(backend_xmin)) FROM pg_stat_activity;

Для оценки реального bloat обычно используют расширение pgstattuple или известные query от check_postgres/pgexperts.

Борьба с bloat:

1. Профилактика: адекватный autovacuum, отсутствие долгих транзакций, fillfactor < 100 для HOT.
2. Index bloat: REINDEX CONCURRENTLY idx_name; (PG 12+, без долгой блокировки).
3. Table bloat без простоя: pg_repack — пересоздаёт таблицу/индексы в фоне, держит блокировку только на короткое финальное переключение (через триггеры собирает изменения).
4. С простоем: VACUUM FULL или CLUSTER.

# pg_repack: онлайн-устранение bloat таблицы и её индексов
pg_repack -h localhost -d mydb -t accounts --jobs 4

HOT updates (Heap-Only Tuples)#

HOT — оптимизация, снижающая стоимость UPDATE и index bloat. Условие: обновляемые колонки не входят ни в один индекс и на странице есть место (fillfactor).

При HOT-update:

• Новая версия создаётся на той же странице.
• Индексы НЕ обновляются — старая индексная запись указывает на старый tuple, а старый tuple через t_ctid образует HOT-chain к новой версии (redirect через item pointer).
• Очистка HOT-цепочек возможна даже page-level «HOT pruning» при обычном SELECT, без полноценного vacuum.

Польза: меньше записей в индексы (меньше WAL, меньше index bloat), быстрее UPDATE.

-- Оставляем место на странице под HOT-обновления
ALTER TABLE accounts SET (fillfactor = 80);
VACUUM FULL accounts;  -- чтобы применить fillfactor к существующим данным

-- Доля HOT-обновлений
SELECT relname, n_tup_upd, n_tup_hot_upd,
       round(100.0 * n_tup_hot_upd / nullif(n_tup_upd,0), 1) AS hot_pct
FROM pg_stat_user_tables ORDER BY n_tup_upd DESC;

Senior-вывод: чтобы UPDATE были HOT, не индексируйте часто меняющиеся колонки (например, updated_at, счётчики), и держите fillfactor ниже 100 на горячих таблицах.

Подводные камни / gotchas#

• ctid нестабилен. Меняется при UPDATE, VACUUM FULL, pg_repack, CLUSTER. Никогда не использовать как идентификатор строки между транзакциями.
• VACUUM не уменьшает файл. Если ждали возврата места ОС после DELETE — его не будет; нужен VACUUM FULL/pg_repack.
• Долгая транзакция замораживает горизонт vacuum. Одна забытая idle in transaction или аналитический запрос на час может раздуть всю БД. Ставьте idle_in_transaction_session_timeout, мониторьте backend_xmin.
• Реплика с hot_standby_feedback = on удерживает горизонт на мастере — bloat «прилетает» от долгих запросов на standby.
• SELECT ... FOR UPDATE создаёт write-write точку и может вызвать ожидания/deadlock-и, хотя обычный SELECT — нет.
• READ COMMITTED ≠ стабильный снапшот. В пределах одной транзакции два запроса могут вернуть разное. Для отчётов нужен REPEATABLE READ.
• SERIALIZABLE требует retry-логики на 40001. Без неё приложение будет падать под конкуренцией.
• Anti-wraparound vacuum нельзя пропустить. Он запустится даже на «тихой» таблице и нагрузит I/O; на огромных таблицах это сюрприз в проде. Лучше замораживать заранее.
• HOT ломается от индекса на горячей колонке. Добавили индекс на updated_at — UPDATE перестали быть HOT, индексы начали раздуваться.
• Hint bits = «первое чтение медленное». После массового INSERT первый SELECT/VACUUM дописывает hint-биты (генерируя dirty pages и WAL при checksum), что выглядит как «непонятная» нагрузка.
• n_dead_tup в pg_stat_user_tables — оценка, обновляется по статистике; для точной картины — pgstattuple.

Вопросы на собеседовании#

В: Что физически происходит при UPDATE одной строки в PostgreSQL? О: Создаётся новая версия tuple с xmin = txid (часто на той же странице, если HOT), старой версии проставляется xmax = txid и ссылка t_ctid на новую. In-place изменения нет, пока версия видима другим. Старая версия становится dead после коммита и удаляется vacuum-ом. Если обновлённая колонка не в индексе — UPDATE может быть HOT и не трогать индексы.

В: Как PostgreSQL определяет, какую версию строки видит транзакция? О: По снапшоту (xmin, xmax, xip[]) и системным полям версии. Версия видна, если её xmin закоммичен и виден по снапшоту, а xmax равен 0 / aborted / ещё не виден по снапшоту. Статус коммита берётся из pg_xact и кэшируется hint-битами. READ COMMITTED берёт новый снапшот на каждую команду, REPEATABLE READ — один на транзакцию.

В: В чём принципиальное отличие MVCC от блокировочных СУБД? О: В MVCC читатели не блокируют писателей и наоборот — каждый видит свою консистентную версию. В 2PL/MyISAM согласованность чтения держится на блокировках, что создаёт контеншн read↔write. Платой за MVCC являются мёртвые версии и необходимость vacuum. При этом write↔write по одной строке блокируются и в PostgreSQL.

В: Что делает VACUUM и почему после него не уменьшается размер файла? О: VACUUM помечает место от dead tuples как переиспользуемое (через FSM), чистит индексы, замораживает старые версии и обновляет visibility map. Но он не дефрагментирует файл и не отдаёт место ОС (кроме пустых страниц в хвосте). Для возврата места нужен VACUUM FULL (переписывает таблицу под ACCESS EXCLUSIVE) или онлайн pg_repack.

В: Что такое transaction id wraparound и как он предотвращается? О: xid 32-битный; видимость работает по принципу «2^31 прошлых / 2^31 будущих». Если очень старую версию не заморозить, через ~2 млрд транзакций её xmin окажется «в будущем» и строка исчезнет — потеря данных. Предотвращается freezing-ом (hint-bit HEAP_XMIN_FROZEN). При возрасте autovacuum_freeze_max_age запускается принудительный anti-wraparound vacuum; у самого края БД уходит в read-only.

В: Почему vacuum работает, а bloat продолжает расти? О: Скорее всего есть долгоживущий снапшот, удерживающий горизонт OldestXmin: idle in transaction, долгий аналитический запрос, открытый курсор, или standby с hot_standby_feedback. Vacuum не может удалить версии новее самого старого снапшота. Диагностика — pg_stat_activity.backend_xmin и age(backend_xmin); лечение — idle_in_transaction_session_timeout, борьба с долгими транзакциями.

В: Что такое HOT updates и когда они не срабатывают? О: Heap-Only Tuple — UPDATE, при котором новая версия создаётся на той же странице, а индексы не обновляются (старая запись индекса через HOT-chain ведёт к новой версии). Условия: обновляемые колонки не входят в индексы и на странице есть место (fillfactor < 100). Не срабатывает, если меняется индексируемая колонка или страница переполнена. HOT снижает WAL и index bloat.

В: Чем VACUUM FULL отличается от pg_repack? О: Оба возвращают место и устраняют bloat, но VACUUM FULL держит ACCESS EXCLUSIVE lock на всю таблицу всё время (полная недоступность) и требует ~двойного места. pg_repack пересоздаёт таблицу/индексы в фоне, собирая изменения через триггеры, и берёт короткую исключительную блокировку только на финальное переключение — пригоден для онлайн-прода.

В: Как настроить autovacuum для большой горячей таблицы? О: Дефолтный scale_factor = 0.2 означает ожидание 20% мёртвых строк — на большой таблице это огромный объём. Снижают пер-таблично autovacuum_vacuum_scale_factor до 0.01–0.05, при необходимости ослабляют троттлинг (autovacuum_vacuum_cost_delay/cost_limit), увеличивают autovacuum_max_workers. Глобально выключать autovacuum нельзя — упрётесь в wraparound.

На что копают на senior+#

• Горизонт видимости (OldestXmin) и почему его держат разные сущности: prepared transactions, replication slots (pg_replication_slots), hot_standby_feedback, долгие транзакции — умение найти виновника через pg_stat_activity, pg_replication_slots, pg_prepared_xacts.
• Snapshot isolation vs serializable: чем REPEATABLE READ в PG отличается от настоящей сериализуемости, что такое write skew, как SSI ловит rw-конфликты, цена SERIALIZABLE под нагрузкой и обязательность retry на 40001.
• Переход на 64-битные xid: понимание, что txid_current() отдаёт 64-битное значение (epoch + xid), но на диске xmin/xmax всё ещё 32-битные — отсюда необходимость freezing. Знание про vacuum_failsafe_age (PG 14+).
• Visibility Map и Index-Only Scan: как all-visible страницы позволяют не ходить в heap, и почему свежезаписанные/часто обновляемые таблицы теряют этот выигрыш.
• Стоимость MVCC на чтение: hint bits, обращения к pg_xact, «первое чтение после загрузки медленнее», влияние на WAL при wal_log_hints/checksums.
• Bloat-инжиниринг: измерение через pgstattuple, выбор между REINDEX CONCURRENTLY, pg_repack и VACUUM FULL, подбор fillfactor под паттерн нагрузки, мониторинг n_tup_hot_upd.
• Anti-wraparound в проде: как мониторить age(datfrozenxid), что делать при приближении к read-only, как форсировать VACUUM FREEZE без даунтайма и почему нельзя «просто перезапустить».
• Взаимодействие MVCC и логической репликации: как старые версии и горизонт влияют на pg_logical слоты и почему «застрявший» слот раздувает мастер.