Модуль: Базы данных · Уровень: 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: блокировочники 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 слоты и почему «застрявший» слот раздувает мастер.