MVCC в PostgreSQL: как база показывает каждому свою правду

Почему SELECT в PostgreSQL не ждёт чужой UPDATE? Почему после удаления миллиона строк файл не уменьшился? Почему «забытая» транзакция может раздуть всю базу? На все три вопроса отвечает одна идея — MVCC. Разберём её на примерах, SQL и интерактивном симуляторе, где вы сами запустите две параллельные транзакции.

Актуально для PostgreSQL 18 (ветка docs/current). Связанная статья: DELETE vs TRUNCATE в PostgreSQL.

Два окна, один баланс

Ответ: она никуда не делась. Старая версия строки лежит рядом с новой, и каждый запрос читает ту версию, которая была актуальна на момент его «снимка». Это и называется Multi-Version Concurrency Control — многоверсионное управление конкурентным доступом.

Идея MVCC: читатели не блокируют писателей

Классический подход к конкурентности — блокировки: пока один пишет, остальные ждут. Это просто, но медленно: отчёт на полчаса остановит все платежи. MVCC решает проблему иначе: данные не перезаписываются, а версионируются. Каждая транзакция видит согласованный срез базы, а конфликтуют между собой только запросы, которые пытаются изменить одну и ту же строку.

Версия строки изнутри

Строка в таблице — это на самом деле версия строки (tuple). У каждой версии есть служебные поля. Их можно увидеть, добавив системные колонки в SELECT:

CREATE TABLE accounts (
    name    text PRIMARY KEY,
    balance numeric NOT NULL
);
INSERT INTO accounts VALUES ('Alice', 1000);

SELECT ctid, xmin, xmax, name, balance FROM accounts;
--  ctid  | xmin | xmax | name  | balance
-- -------+------+------+-------+---------
--  (0,1) |  781 |    0 | Alice |    1000

-- номер текущей транзакции (в PostgreSQL 13+)
SELECT pg_current_xact_id();

Как выглядит UPDATE на самом деле

UPDATE в PostgreSQL — это «вставь новую версию и закрой старую». Нет никакого изменения «на месте».

Снимок и правила видимости

Каждый запрос работает со снимком (snapshot): «какие транзакции на этот момент уже завершились, а какие нет». Версия строки видна, если одновременно:

1. транзакция xmin зафиксирована и попала в снимок (или это ваша собственная транзакция);
2. и при этом xmax либо равен 0, либо относится к откатившейся/незавершённой чужой транзакции, либо к транзакции, которой ваш снимок «ещё не видит».

-- текущий снимок: xmin:xmax:список активных транзакций
SELECT pg_current_snapshot();
-- 790:795:791,793

-- читается так: всё, что меньше 790, уже завершено;
-- всё от 795 — ещё не началось; 791 и 793 выполняются и невидимы

Что делает каждая операция

Теперь пройдёмся по операциям с точки зрения версий строк. Для каждой: что происходит в таблице, зачем и когда использовать, пример из практики и цена.

INSERT INTO orders (user_id, total) VALUES (42, 1990)
RETURNING id, xmin;

-- видим ли мы только живые версии? Смотрим всё через pageinspect
CREATE EXTENSION IF NOT EXISTS pageinspect;
SELECT lp, t_xmin, t_xmax, t_ctid
FROM heap_page_items(get_raw_page('accounts', 0));

-- сколько обновлений было «лёгкими»
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
WHERE relname = 'accounts';

-- оставляем место на странице под будущие HOT-обновления
ALTER TABLE accounts SET (fillfactor = 80);

DELETE FROM sessions WHERE expires_at < now() RETURNING id;

-- очередь задач: каждый воркер берёт свободные задачи и не мешает другим
WITH next AS (
    SELECT id FROM jobs
    WHERE status = 'new'
    ORDER BY id
    LIMIT 10
    FOR UPDATE SKIP LOCKED
)
UPDATE jobs j SET status = 'running'
FROM next WHERE j.id = next.id
RETURNING j.id;

INSERT INTO page_views (page_id, views)
VALUES (7, 1)
ON CONFLICT (page_id)
DO UPDATE SET views = page_views.views + 1;

MERGE INTO accounts a
USING incoming i ON a.name = i.name
WHEN MATCHED THEN UPDATE SET balance = a.balance + i.delta
WHEN NOT MATCHED THEN INSERT (name, balance) VALUES (i.name, i.delta);

VACUUM (VERBOSE, ANALYZE) accounts;

-- кто мешает убрать мусор: самые старые транзакции
SELECT pid, state, xact_start, backend_xmin, left(query, 60) AS query
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC
LIMIT 5;

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

Ниже — рабочая модель MVCC. Слева сессия A, справа сессия B. Внизу — «куча» таблицы accounts: все версии строк со значениями xmin и xmax и пометкой, кто какую версию видит. Выберите готовый сценарий и жмите «Следующий шаг» или нажимайте кнопки сами. Уровень изоляции применяется при BEGIN.

Симулятор упрощает: нет hint bits, нет multixact и HOT, а номера транзакций выдаются при BEGIN, тогда как PostgreSQL делает это лениво — при первом изменении. Поведение видимости и ожидания на строках при этом совпадает с настоящим.

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

Уровень изоляции определяет, когда берётся снимок. Это меняет то, что вы видите внутри одной транзакции.

• READ UNCOMMITTED в PostgreSQL работает как READ COMMITTED: «грязное» чтение невозможно.
• REPEATABLE READ в PostgreSQL строже стандарта SQL — фантомов тоже нет. Платой за это становятся ошибки could not serialize access due to concurrent update, если другая транзакция уже изменила нужную строку.
• SERIALIZABLE использует SSI (Serializable Snapshot Isolation) и может прервать транзакцию с кодом 40001. Приложение должно уметь повторить транзакцию целиком.

BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE name = 'Alice';
-- ... другие сессии меняют и фиксируют Alice ...
SELECT balance FROM accounts WHERE name = 'Alice'; -- то же значение
COMMIT;

SHOW default_transaction_isolation;  -- read committed

Как MVCC влияет на производительность

Что получаем

• Высокая конкурентность: чтение и запись не блокируют друг друга.
• Быстрый ROLLBACK: ничего не откатывается физически.
• Согласованные чтения без явных блокировок.

Что платим

• Bloat. Мёртвые версии занимают место в таблицах и индексах. Больше страниц — больше чтений с диска и в память, медленнее скан.
• Write amplification. UPDATE пишет целую новую версию, а при не-HOT обновлении ещё и новые записи во всех индексах. Увеличивается WAL.
• Нагрузка на VACUUM. Autovacuum потребляет CPU и диск. Чем чаще UPDATE и DELETE, тем он нужнее.
• Долгие транзакции. Любая открытая транзакция «закрепляет» горизонт: всё, что удалено после её начала, нельзя убрать. Раздувается вся база, а не только ваши таблицы.
• Wraparound. Номера транзакций 32-битные; старые версии нужно периодически «замораживать» (VACUUM FREEZE), иначе база остановит запись в целях защиты данных.

Оцените раздувание

Двигайте ползунки: сколько обновлений в секунду получает таблица и как часто их убирает autovacuum. Модель показывает, как быстро накапливается мусор.

Истории из практики

SELECT pid, now() - xact_start AS age, state, left(query, 50) AS query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY age DESC;

-- защита: обрывать забытые транзакции
ALTER SYSTEM SET idle_in_transaction_session_timeout = '60s';
SELECT pg_reload_conf();

-- вместо UPDATE одной строки: писать события...
INSERT INTO hits_log (at) VALUES (now());
-- ...и сворачивать их пачками
WITH moved AS (DELETE FROM hits_log RETURNING 1)
UPDATE stats SET hits = hits + (SELECT count(*) FROM moved);

UPDATE orders
SET status = 'paid', version = version + 1
WHERE id = 10 AND version = 3;
-- UPDATE 0 → кто-то успел раньше: перечитать и повторить

-- на реплике конфликты с рекавери решаются настройкой
-- hot_standby_feedback / max_standby_streaming_delay
SHOW hot_standby_feedback;

Где больно

• Забытая транзакция. Самая частая причина раздувания. Всегда закрывайте транзакции и задайте idle_in_transaction_session_timeout.
• Массовый UPDATE «на все строки». UPDATE big SET flag = true удвоит размер таблицы: каждая строка получит новую версию. Делайте пачками и давайте autovacuum успевать.
• Индексы на часто меняемых столбцах. Лишают UPDATE возможности быть HOT. Индексируйте только то, что нужно для запросов.
• Ожидание, что «DELETE освободит диск». Освободит место внутри файла только после VACUUM, ОС — только после VACUUM FULL или пересоздания таблицы.
• Отключённый или слабый autovacuum. На горячих таблицах уменьшайте autovacuum_vacuum_scale_factor индивидуально.
• Слоты репликации без потребителя. Удерживают горизонт и WAL так же, как забытая транзакция.
• Нет повтора при serialization failure. На REPEATABLE READ и SERIALIZABLE ошибки 40001 — норма. Код должен повторять транзакцию.

ALTER TABLE sessions SET (
    autovacuum_vacuum_scale_factor = 0.02,
    autovacuum_vacuum_threshold    = 1000,
    fillfactor                     = 85
);

Сводная таблица: последствия и побочные эффекты

Плюсы и минусы MVCC

Проверь себя

Четыре вопроса по статье. Можно пробовать, пока не нажмёте верный вариант.

Шпаргалка

• Каждая строка — набор версий с xmin и xmax; видимость определяет снимок.
• UPDATE = новая версия, DELETE = проставить xmax, ROLLBACK = пометить транзакцию откатившейся.
• Мёртвые версии убирает VACUUM; файл обычно не уменьшается.
• Держите транзакции короткими; следите за idle in transaction.
• Не индексируйте часто меняемые столбцы, используйте fillfactor для HOT.
• На REPEATABLE READ и SERIALIZABLE готовьте повтор транзакции.

Полезные материалы

Все ссылки ведут в документацию PostgreSQL ветки current — на последнюю стабильную версию (на момент публикации — 18).

• Concurrency Control — раздел про MVCC целиком
• MVCC: Introduction — основная идея
• Transaction Isolation — уровни изоляции и аномалии
• Explicit Locking — блокировки таблиц и строк
• System Columns — xmin, xmax, ctid
• Routine Vacuuming — VACUUM, autovacuum, wraparound
• VACUUM — команда и опции
• Automatic Vacuuming (настройки) — пороги и параметры autovacuum
• Heap-Only Tuples (HOT) — лёгкие обновления
• Database Page Layout — устройство страницы и заголовка версии
• Visibility Map — карта видимости и index-only scan
• INSERT — включая ON CONFLICT
• UPDATE — RETURNING, OLD и NEW в версии 18
• DELETE — синтаксис и RETURNING
• MERGE — условные вставка, обновление, удаление
• SELECT: блокирующие предложения — FOR UPDATE, NOWAIT, SKIP LOCKED
• SET TRANSACTION — выбор уровня изоляции
• System Information Functions — pg_current_xact_id, pg_current_snapshot
• Cumulative Statistics System — pg_stat_user_tables, pg_stat_activity
• Client Connection Defaults — idle_in_transaction_session_timeout, lock_timeout
• pageinspect — заглянуть внутрь страницы таблицы
• pgstattuple — измерить bloat
• Storage Parameters — fillfactor и параметры autovacuum на таблицу
• Hot Standby — запросы на репликах и конфликты с VACUUM