MVCC, транзакції та VACUUM: як PostgreSQL працює з версіями рядків

PostgreSQL дозволяє читати й писати одночасно без блокувань між читачами і писачами. Ціна цього — механізм MVCC (Multi-Version Concurrency Control) і необхідність прибирати старі версії рядків. Якщо не розуміти, як це працює, рано чи пізно база «розпухне» або почне гальмувати без видимих причин.

UPDATE = INSERT + позначка «застарілий»

У PostgreSQL рядки не змінюються на місці. UPDATE створює нову версію рядка, а стару позначає як недійсну для нових транзакцій. DELETE лише позначає рядок мертвим.

Кожна версія має службові поля:

• xmin — транзакція, яка створила версію;
• xmax — транзакція, яка її видалила або замінила.

SELECT xmin, xmax, id, status FROM orders WHERE id = 42;
--  xmin  | xmax | id | status
-- 918231 |    0 | 42 | paid

Кожна транзакція бачить знімок даних: версії, створені транзакціями, що завершилися до її початку (або до початку запиту — залежно від рівня ізоляції). Тому читачі не блокують писачів і навпаки.

Звідки береться bloat

Старі версії рядків (dead tuples) лишаються у файлах таблиці, доки їх не прибере VACUUM. Якщо вони накопичуються швидше, ніж прибираються, таблиця і індекси розростаються — це bloat. Запити читають більше сторінок, кеш працює гірше.

SELECT relname, n_live_tup, n_dead_tup,
       round(n_dead_tup * 100.0 / greatest(n_live_tup, 1), 1) AS dead_pct,
       last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

VACUUM і autovacuum

Звичайний VACUUM не зменшує розмір файлу — він робить місце доступним для нових рядків. Тому стабільна таблиця з правильно налаштованим autovacuum не росте безкінечно.

Autovacuum запускає VACUUM автоматично, коли кількість змінених рядків перевищує поріг. Для великих таблиць значення за замовчуванням (20% рядків) — забагато: на таблиці з 100 млн рядків VACUUM почнеться лише після 20 млн змін.

ALTER TABLE events SET (
  autovacuum_vacuum_scale_factor = 0.02,   -- 2% замість 20%
  autovacuum_analyze_scale_factor = 0.01
);

Обережно

Не вимикайте autovacuum «бо він навантажує базу». Окрім прибирання, він захищає від wraparound лічильника транзакцій. Якщо до цього дійде, PostgreSQL перестане приймати запис, доки не буде виконано VACUUM.

Головний ворог VACUUM — довгі транзакції

VACUUM не може прибрати версію, яку ще може бачити хоч одна активна транзакція. Одна забута транзакція, відкрита годину тому (BEGIN у консолі розробника або завислий воркер), блокує прибирання по всій базі.

SELECT pid, usename, state, xact_start, now() - xact_start AS duration, left(query, 60)
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start
LIMIT 5;

Захист:

ALTER DATABASE app SET idle_in_transaction_session_timeout = '5min';
ALTER DATABASE app SET statement_timeout = '60s';

Рівні ізоляції

Класична проблема: втрачене оновлення

-- Дві транзакції одночасно списують з балансу 100
SELECT balance FROM accounts WHERE id = 1;          -- обидві бачать 500
UPDATE accounts SET balance = 400 WHERE id = 1;     -- обидві пишуть 400 замість 300

Рішення в Read Committed — атомарний вираз або явне блокування:

UPDATE accounts SET balance = balance - 100 WHERE id = 1 AND balance >= 100;

-- або
BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;   -- друга транзакція чекатиме тут
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

Порада

Для черг завдань у таблиці використовуйте SELECT ... FOR UPDATE SKIP LOCKED: кожен воркер бере вільні рядки, не чекаючи на заблоковані іншими.

Що варто моніторити

• кількість dead tuples і час останнього autovacuum для великих таблиць;
• найстаршу активну транзакцію (pg_stat_activity);
• вік найстаршого xid у базі — age(datfrozenxid) з pg_database;
• ріст розміру таблиць та індексів відносно кількості рядків.