Все заметки
Программирование

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.

// комментарии

Загружаем комментарии...

    // следующий шаг

    Давайте построим что-нибудь вместе.

    Написать в Telegram
    aslbek@asldev: ~
    ❯