MVCC и VACUUM: удалили строки, а таблица не похудела
В PostgreSQL UPDATE на самом деле пишет новую строку, а DELETE ничего не стирает. Разбираем изнутри мёртвые кортежи, bloat, autovacuum и то, как одна забытая транзакция раздувает всю базу.
Проблема: удалили миллион строк, а диск не освободился
Представьте: в таблице events несколько миллионов строк, половина из них устарела. Вы пишете ночную Celery-задачу, которая делает DELETE старых записей. Утром проверяете pg_relation_size() — таблица не стала меньше ни на байт. Другой сценарий: на каждый платёж вы обновляете статус в orders, количество строк почти не меняется, но таблица и её индексы растут неделя за неделей, а обычные SELECT медленнеют.
Это не баг. Это прямое следствие того, как устроен MVCC в PostgreSQL и как работает его «уборщик» — VACUUM. Разберём механизм изнутри: версии строк, xmin/xmax, мёртвые кортежи, bloat, настройки autovacuum и почему одна забытая транзакция вредит всей базе.
Почему наивная модель не работает
Обычно базу представляют так: UPDATE меняет строку на месте, DELETE стирает её с диска, и место сразу освобождается. Будь это так, читатель ждал бы блокировку, пока писатель меняет строку, и наоборот.
PostgreSQL выбрал другой путь: читатели не блокируют писателей, писатели не блокируют читателей. Цена — у одной логической строки одновременно живёт несколько физических версий. Каждая транзакция по своему снимку (snapshot) решает, какая версия ей видна. А старые версии, ставшие никому не нужными, сами не исчезают — их должен кто-то убрать.
MVCC изнутри: xmin, xmax и версии кортежей
Каждая физическая строка (tuple) хранит в заголовке служебные поля. Главные из них:
xmin— ID транзакции (XID), создавшей эту версию (INSERT или UPDATE);xmax— ID транзакции, удалившей или обновившей версию (0— никто не трогал; поле также используется при блокировке строки);ctid— физический адрес версии:(номер страницы, позиция на странице).
Эти системные столбцы можно выбрать обычным SELECT:
CREATE TABLE accounts (
id bigint PRIMARY KEY,
balance numeric(14, 2) NOT NULL
);
INSERT INTO accounts VALUES (1, 100.00);
SELECT ctid, xmin, xmax, * FROM accounts;
-- ctid | xmin | xmax | id | balance
-- (0,1) | 755 | 0 | 1 | 100.00
UPDATE accounts SET balance = 150.00 WHERE id = 1;
SELECT ctid, xmin, xmax, * FROM accounts;
-- ctid | xmin | xmax | id | balance
-- (0,2) | 756 | 0 | 1 | 150.00
Обратите внимание: ctid сменился с (0,1) на (0,2). UPDATE не изменил строку на месте — он записал новую версию, а в xmax старой проставил свой XID. То есть UPDATE — это на самом деле INSERT + DELETE. Номера XID у вас будут другими, суть та же.
Старая версия всё ещё лежит на странице. Её видно через расширение pageinspect (нужны права суперпользователя):
CREATE EXTENSION IF NOT EXISTS pageinspect;
SELECT lp, t_xmin, t_xmax, t_ctid
FROM heap_page_items(get_raw_page('accounts', 0));
-- lp | t_xmin | t_xmax | t_ctid
-- 1 | 755 | 756 | (0,2)
-- 2 | 756 | 0 | (0,2)
У первой версии t_xmax = 756, а t_ctid указывает на новую. DELETE работает так же, только новая версия не создаётся: заполняется xmax, и всё. Данные остаются на диске.
Упрощённое правило видимости: версия видна, если транзакция xmin зафиксирована и находится «в прошлом» для снимка, а xmax пуст или принадлежит транзакции, которую снимок не видит (ещё не зафиксирована, откатилась или началась после снимка).
Мёртвые кортежи и bloat
Когда транзакция из xmax зафиксирована и ни одна открытая транзакция больше не может увидеть эту версию, она становится мёртвым кортежем (dead tuple). Он занимает место, заполняет страницы, Seq Scan всё равно читает его, а в индексах остаются ссылки на него.
Когда мёртвые кортежи накапливаются и таблица или индекс становятся заметно больше полезных данных, это называют bloat. Воспроизвести легко:
CREATE TABLE events AS
SELECT g AS id, now() AS created_at, 'pending'::text AS status
FROM generate_series(1, 1000000) AS g;
SELECT pg_size_pretty(pg_relation_size('events')); -- A
UPDATE events SET status = 'done';
SELECT pg_size_pretty(pg_relation_size('events')); -- ~2 x A
VACUUM events;
SELECT pg_size_pretty(pg_relation_size('events')); -- still ~2 x A
UPDATE events SET status = 'archived';
SELECT pg_size_pretty(pg_relation_size('events')); -- roughly the same: freed space reused
Что произошло? Первый UPDATE записал новую версию каждой строки. Старые страницы были заполнены, поэтому новые версии легли на новые страницы, и таблица выросла почти вдвое. VACUUM убрал мёртвые кортежи, но файл не уменьшился: освобождённое место записывается в карту свободного пространства (FSM) и переиспользуется следующими INSERT/UPDATE. Последний шаг это показывает — второй UPDATE почти не добавляет размера. Цифры измерьте сами: они зависят от версии PostgreSQL и ширины строки.
Обычный VACUUM не возвращает место операционной системе — он помечает его для повторного использования внутри таблицы. Отрезаются только полностью пустые страницы в конце файла.
Чтобы реально вернуть место, нужен VACUUM FULL: он переписывает таблицу в новый файл, но всё это время держит ACCESS EXCLUSIVE — таблицу нельзя ни читать, ни писать. В продакшене обычно используют расширение pg_repack: оно тоже переписывает таблицу, но берёт только короткие блокировки.
VACUUM делает ещё две важные вещи: обновляет visibility map (она нужна для эффективного index-only scan) и «замораживает» старые версии (freeze) — об этом ниже.
HOT update: обновление подешевле
Если UPDATE не трогает ни одного индексированного столбца и на той же странице есть место, PostgreSQL делает HOT-обновление (heap-only tuple): новая версия пишется на ту же страницу, в индексы ничего не добавляется. Такие цепочки PostgreSQL может частично чистить прямо при чтении страницы, не дожидаясь VACUUM (page pruning). Поэтому для часто обновляемых таблиц полезно снизить fillfactor (например, до 90) и не плодить лишние индексы: индекс на столбце status лишает HOT все обновления этого столбца.
Когда запускается autovacuum
Запускать VACUUM вручную не обязательно — этим занимается демон autovacuum. Раз в autovacuum_naptime он обходит базы и чистит таблицу, когда выполняется условие:
n_dead_tup > autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * reltuples
Основные параметры и значения по умолчанию:
| Параметр | По умолчанию | Что делает |
|---|---|---|
autovacuum_vacuum_threshold |
50 | базовый порог мёртвых кортежей |
autovacuum_vacuum_scale_factor |
0.2 | доля размера таблицы, добавляемая к порогу |
autovacuum_vacuum_insert_threshold |
1000 | для таблиц, куда только вставляют (PG 13+) |
autovacuum_analyze_scale_factor |
0.1 | когда запускать ANALYZE |
autovacuum_naptime |
1min | пауза между проверками |
autovacuum_max_workers |
3 | число одновременных воркеров |
autovacuum_vacuum_cost_delay |
2ms | пауза, чтобы не перегружать диск |
autovacuum_freeze_max_age |
200 млн | порог принудительного anti-wraparound vacuum |
Проблема в scale_factor = 0.2. Для таблицы в 10 миллионов строк autovacuum начнёт работу примерно после 2 миллионов мёртвых кортежей. Для больших и активно меняющихся таблиц это слишком поздно. Решение — не трогать глобальные настройки, а изменить параметры конкретной таблицы. В Django это удобно сделать миграцией:
# orders/migrations/0013_order_autovacuum.py
from django.db import migrations
class Migration(migrations.Migration):
dependencies = [("orders", "0012_order_psp_id")]
operations = [
migrations.RunSQL(
sql="""
ALTER TABLE orders_order SET (
autovacuum_vacuum_scale_factor = 0.01,
autovacuum_vacuum_threshold = 1000,
autovacuum_analyze_scale_factor = 0.02,
fillfactor = 90
);
""",
reverse_sql="""
ALTER TABLE orders_order RESET (
autovacuum_vacuum_scale_factor,
autovacuum_vacuum_threshold,
autovacuum_analyze_scale_factor,
fillfactor
);
""",
),
]
fillfactor влияет только на новые страницы; существующие перераспределятся лишь при перезаписи таблицы (VACUUM FULL или pg_repack). И ещё: autovacuum намеренно притормаживается механизмом cost-based delay. Если на большой таблице он не успевает за скоростью появления мёртвых кортежей, придётся поднять autovacuum_vacuum_cost_limit.
Долгая транзакция — враг всей базы
VACUUM может удалить мёртвый кортеж, только если ни одна транзакция не может его увидеть. PostgreSQL определяет это по xmin horizon — самой старой ещё работающей транзакции или снимку. Если какая-то сессия час назад открыла транзакцию и забыла её закрыть, мёртвые кортежи, появившиеся за этот час во всех таблицах базы, не будут удалены. Autovacuum работает, тратит CPU и диск, но удалить ничего не может — в выводе VACUUM VERBOSE такие строки помечены как «dead but not yet removable».
В Django-проекте это часто выглядит так:
# orders/tasks.py — WRONG: the transaction stays open during every HTTP call
import requests
from celery import shared_task
from django.db import transaction
from .models import Order
PSP_URL = "https://psp.example.com/status/{}"
@shared_task
def sync_orders():
with transaction.atomic():
for order in Order.objects.filter(status="pending"):
resp = requests.get(PSP_URL.format(order.psp_id), timeout=10)
order.status = resp.json()["status"]
order.save(update_fields=["status"])
Задача ходит во внешний API за каждым заказом, а весь цикл обёрнут в один transaction.atomic(). Если API тормозит, транзакция висит открытой минутами. Правильно — вынести сетевые вызовы из транзакции и оборачивать в короткую транзакцию только запись:
# orders/tasks.py — FIXED: network outside, short transaction only for writes
import requests
from celery import shared_task
from django.db import transaction
from .models import Order
PSP_URL = "https://psp.example.com/status/{}"
@shared_task
def sync_orders(batch_size=200):
# one short SELECT in autocommit mode
pending = list(
Order.objects.filter(status="pending")
.order_by("id")
.values_list("id", "psp_id")[:batch_size]
)
# slow network calls, no transaction open
results = {}
for order_id, psp_id in pending:
resp = requests.get(PSP_URL.format(psp_id), timeout=10)
resp.raise_for_status()
results[order_id] = resp.json()["status"]
# short transaction just for the writes
with transaction.atomic():
for order_id, status in results.items():
Order.objects.filter(id=order_id, status="pending").update(status=status)
С ATOMIC_REQUESTS = True та же опасность: медленный внешний вызов внутри view держит транзакцию открытой на весь запрос.
Как найти того, кто держит горизонт:
SELECT pid,
usename,
state,
now() - xact_start AS xact_age,
backend_xid,
backend_xmin,
left(query, 60) AS last_query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start
LIMIT 10;
SELECT slot_name, active, xmin, catalog_xmin
FROM pg_replication_slots;
SELECT gid, prepared, owner
FROM pg_prepared_xacts;
Главные подозреваемые — сессии в состоянии idle in transaction со старым backend_xmin или backend_xid. Сюда же относятся неиспользуемые слоты репликации, забытые prepared-транзакции и долгие аналитические запросы на реплике с hot_standby_feedback = on — все они тоже удерживают горизонт.
В качестве защиты задайте таймауты в настройках Django:
# settings.py
DATABASES = {
"default": {
"ENGINE": "django.db.backends.postgresql",
"NAME": "shop",
"USER": "shop",
"HOST": "127.0.0.1",
"PORT": "5432",
"OPTIONS": {
"options": (
"-c idle_in_transaction_session_timeout=60s "
"-c statement_timeout=30s"
),
},
}
}
Если вы подключаетесь через PgBouncer в режиме transaction, стартовые options могут не дойти до сервера; тогда задайте значения на уровне роли: ALTER ROLE shop SET idle_in_transaction_session_timeout = '60s';.
Большие UPDATE и DELETE — порциями
Обновить всю таблицу одним запросом (Event.objects.update(status="done")) — значит получить одну длинную транзакцию и миллионы мёртвых кортежей разом. Разбивайте такие backfill-операции на батчи:
# events/backfill.py
import time
from django.db import connection, transaction
from .models import Event
def backfill_status(batch_size=5_000, vacuum_every=20):
last_id = 0
batches = 0
while True:
with transaction.atomic():
ids = list(
Event.objects.filter(id__gt=last_id, status="pending")
.order_by("id")
.values_list("id", flat=True)[:batch_size]
)
if not ids:
break
Event.objects.filter(id__in=ids).update(status="done")
last_id = ids[-1]
batches += 1
if batches % vacuum_every == 0:
# VACUUM cannot run inside a transaction block; here we are in autocommit
with connection.cursor() as cursor:
cursor.execute("VACUUM (ANALYZE) events_event")
time.sleep(0.1) # let replicas and autovacuum breathe
Батчи не уменьшают общее число мёртвых кортежей, но каждая транзакция короткая, горизонт двигается вперёд, а место, освобождённое VACUUM между батчами, переиспользуется следующими. VACUUM нельзя выполнить внутри блока транзакции, поэтому он вызывается вне atomic(), в режиме autocommit.
Мониторинг: pg_stat_user_tables
Статистика по каждой таблице лежит в представлении pg_stat_user_tables:
SELECT relname,
n_live_tup,
n_dead_tup,
round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
n_tup_upd,
n_tup_hot_upd,
last_autovacuum,
autovacuum_count,
pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;
На что смотреть:
n_dead_tupпостоянно растёт, аlast_autovacuumдавний — autovacuum не справляется или горизонт удерживается;- низкое отношение
n_tup_hot_upd / n_tup_upd— обновления задевают индексированные столбцы или на страницах нет места; autovacuum_countбыстро растёт, аn_dead_tupне уменьшается — почти всегда виновата долгая транзакция или слот.
Эти числа приблизительные — они берутся из системы статистики. Для точной оценки bloat есть расширение pgstattuple: SELECT * FROM pgstattuple('events'); покажет точный процент мёртвых кортежей и свободного места, но прочитает таблицу целиком — на больших таблицах осторожнее.
Коротко о transaction ID wraparound
XID — 32-битное число. Около 4 миллиардов значений используются по кругу, и для каждой транзакции примерно 2 миллиарда считаются «прошлым», остальные — «будущим». Если старые строки не заморозить, однажды они выглядели бы созданными «будущей» транзакцией и стали бы невидимыми. Чтобы этого не случилось, VACUUM помечает достаточно старые версии как frozen.
Когда возраст relfrozenxid таблицы превышает autovacuum_freeze_max_age (по умолчанию 200 миллионов), запускается принудительный anti-wraparound vacuum — даже если autovacuum выключен. Начиная с PostgreSQL 14, при достижении vacuum_failsafe_age (по умолчанию 1,6 миллиарда) VACUUM снимает cost-ограничения и делает самое необходимое быстрее. Если и это не помогает, PostgreSQL сначала выдаёт предупреждения, а затем, защищая данные, перестаёт выдавать новые XID — запись останавливается. Обычно до этого доходит там, где долгие транзакции и забытые слоты надолго блокировали очистку.
SELECT datname, age(datfrozenxid) AS xid_age
FROM pg_database
ORDER BY xid_age DESC;
SELECT c.oid::regclass AS table_name,
age(c.relfrozenxid) AS xid_age
FROM pg_class c
WHERE c.relkind IN ('r', 'm', 't')
ORDER BY xid_age DESC
LIMIT 10;
Достаточно вывести эти значения в мониторинг и алертить, когда они заметно превышают autovacuum_freeze_max_age.
Подводные камни в продакшене
- VACUUM FULL «для чистоты». Он полностью блокирует таблицу. В продакшене безопаснее
pg_repack, а для bloat индексов —REINDEX CONCURRENTLY. - Таблицы-очереди. В outbox-, task- и event-таблицах строки быстро вставляются и быстро удаляются. Дайте им агрессивные настройки autovacuum; на больших объёмах используйте партиционирование и вместо
DELETEделайтеDETACH/DROPстарых партиций. - «Горячая» строка. Если одну строку-счётчик обновляют много раз в секунду, её мёртвые версии копятся. HOT и pruning помогают, но при открытой долгой транзакции бессильны и они.
autovacuum_enabled = false. Поставить и забыть легко. Bloat растёт бесконечно, а защита от wraparound всё равно запустит принудительный vacuum в самый неудобный момент.- Shell и отладка. Блок
atomic()вmanage.py shellили Jupyter, транзакция, остановленная наbreakpoint(), — готовая сессияidle in transaction. - Забытый ANALYZE после большого DELETE. Статистика планировщика устаревает;
VACUUM (ANALYZE)делает и то, и другое. - Bloat индексов. Освобождённое место в странице B-tree переиспользуется только для новых ключей из того же диапазона. Следите за размером индексов отдельно от таблицы (
pg_relation_size('имя_индекса')).
Чеклист
- Для больших и активно меняющихся таблиц снижайте
autovacuum_vacuum_scale_factorна уровне таблицы. - Выносите сетевые вызовы, загрузку файлов и медленные вычисления за пределы
transaction.atomic(). - Настройте
idle_in_transaction_session_timeoutиstatement_timeout. - Мониторьте самый старый
xact_start,backend_xminвpg_stat_activityиpg_replication_slots. - Следите за
n_dead_tup,last_autovacuumи долей HOT вpg_stat_user_tables. - Делите backfill и массовые удаления на батчи.
- Поставьте алерт на
age(datfrozenxid). - Вместо
VACUUM FULLиспользуйтеpg_repack.
Итог
В PostgreSQL UPDATE пишет новую версию строки, а DELETE лишь проставляет xmax; старые версии убирает VACUUM и отдаёт место под повторное использование, но не возвращает его на диск. Autovacuum обычно справляется, но для больших таблиц его нужно настраивать, а перед долгой открытой транзакцией он бессилен. Держите транзакции короткими и следите за pg_stat_user_tables и pg_stat_activity — эти две привычки предотвращают большую часть проблем с bloat.
// комментарии
Загружаем комментарии...