MVCC изнутри: версии строк, xmin и xmax

Кластер про JSONB и поиск закончился уроком про полнотекстовый поиск. Теперь спускаемся под капот. В разделе остались два обещания: в уроке про транзакции мы откладывали вопрос, как PostgreSQL держит изоляцию, а в уроке про индексы и VACUUM увидели, что после UPDATE строка меняет адрес ctid. Этот урок объясняет оба факта одной идеей: PostgreSQL не переписывает строки, а хранит их версии.

Доска объявлений

Представь доску объявлений в подъезде, где действует правило: объявления не срывают. Если цена на ремонт поменялась, мастер вешает новый листок, а на старом пишет «отменено». На каждом листке две пометки: кто его повесил и кто его отменил.

Жилец, который подошёл к доске утром, читает листки, повешенные до его прихода и не отменённые к этому моменту. Жилец, который пришёл вечером, видит уже новую цену. Никто никого не ждёт: мастер вешает листок, пока жильцы читают.

PostgreSQL устроен так же. Это называется MVCC, многоверсионное управление параллельным доступом. Каждое изменение создаёт новую версию строки, а старая остаётся лежать в таблице с пометкой «отменена».

Пометки на листке

У каждой версии строки есть скрытые служебные колонки. В SELECT * они не попадают, но их можно запросить по имени:

• ctid уже знаком по уроку 20: физический адрес версии, номер страницы и позиция на ней.
• xmin это номер транзакции, которая версию создала. Пометка «кто повесил».
• xmax это номер транзакции, которая версию удалила или заменила. Пометка «кто отменил». Ноль значит, что версию никто не трогал.

Каждая транзакция, которая что-то меняет, получает номер транзакции. Номера растут по порядку. Свой номер транзакция узнаёт функцией txid_current().

Три транзакции на одной полке

Заведём маленькую полку и проведём её через три транзакции: вставку, изменение цены и удаление, которое передумали.

CREATE TABLE shelf (id integer PRIMARY KEY, title text, price numeric(8, 2));

BEGIN;
INSERT INTO shelf VALUES (1, 'Шинель', 250.00), (2, 'Нос', 200.00);
COMMIT;

BEGIN;
UPDATE shelf SET price = 300.00 WHERE id = 1;
COMMIT;

BEGIN;
DELETE FROM shelf WHERE id = 2;
ROLLBACK;

SELECT ctid, xmin, xmax, id, title, price FROM shelf ORDER BY id;

 ctid  | xmin | xmax | id | title  | price
-------+------+------+----+--------+--------
 (0,3) |  754 |    0 |  1 | Шинель | 300.00
 (0,2) |  753 |  755 |  2 | Нос    | 200.00
(2 rows)

Номера транзакций у тебя будут другими, важна только их разница. Прочитаем ответ по строкам.

Шинель. Транзакция 753 вставила две строки в позиции (0,1) и (0,2). Транзакция 754 поменяла цену, но не переписала строку на месте. Она поставила старой версии в (0,1) пометку xmax = 754 и положила рядом новую версию в (0,3) с xmin = 754. Так что UPDATE в PostgreSQL это «удалить старую версию и вставить новую». Старая версия в (0,1) всё ещё лежит на странице, но SELECT её больше не показывает.

Нос. Транзакция 755 удаляла его, но откатилась. Удаление в MVCC это тоже только пометка: 755 записала себя в xmax, и пометка осталась даже после ROLLBACK. Тем не менее строка видна. Почему, разберём ниже.