← назад к разделу

Вы заметили, что таблица на диске занимает 10 ГБ, хотя реальных данных там в три раза меньше. Или PostgreSQL вдруг начинает тормозить без видимой причины. Скорее всего, дело в bloat — накопившихся мёртвых строках. Разберём, почему это происходит и как с этим справляться.

страница таблицы: [ 2 ] — живая версия, [ 2†] — мёртвая, [ ·] — свободный слот размер файла не меняется: 6 слотов 1. в странице четыре живые версии строк, два слота свободны 1 2 3 4 · · 2. UPDATE строк 2 и 4: новые версии дописаны, старые остались мёртвыми 1 2† 3 4† 2 4 3. VACUUM освобождает мёртвые слоты и заносит их в карту свободного места 1 · 3 · 2 4 4. новая строка занимает освободившийся слот — файл не растёт 1 5 3 · 2 4

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

Почему строки не удаляются сразу

PostgreSQL использует подход, который называется MVCC (Multi-Version Concurrency Control — управление конкурентным доступом через версии). Идея такая: когда вы делаете UPDATE или DELETE, старая строка не стирается немедленно. Она помечается как «мёртвая» и остаётся в файле таблицы.

Зачем? Потому что в этот момент другая транзакция может читать старую версию данных — и должна видеть её консистентной. Пока хоть одна открытая транзакция может видеть старую строку, PostgreSQL не имеет права её удалить.

В итоге после интенсивной работы с данными в таблицах накапливаются мёртвые строки (dead tuples). Именно от них и раздувается размер таблицы — это и есть bloat.

Что делает VACUUM

VACUUM — это команда уборки. Она проходит по таблице и делает три вещи:

Освобождает место от мёртвых строк. Место не возвращается операционной системе — файл таблицы не уменьшается. Зато освобождённые страницы заносятся в FSM (Free Space Map, карту свободного места), и новые строки записываются туда, не раздувая файл дальше. Исключение одно: пустые страницы в самом хвосте файла VACUUM отрезает, взяв на короткое время исключительную блокировку.

Обновляет visibility map. Это карта страниц, где все строки гарантированно видны всем транзакциям. Она нужна для эффективной работы Index Only Scan — без неё планировщик вынужден лезть в саму таблицу даже при запросе только по индексу.

Предотвращает XID wraparound. Об этом подробнее в отдельном разделе ниже.

VACUUM не блокирует чтение и запись — он работает с блокировкой типа SHARE UPDATE EXCLUSIVE. Ждать придётся только командам DDL вроде ALTER TABLE.

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

Механику видно и без базы. Страница здесь — шесть слотов под версии строк: UPDATE помечает старую версию мёртвой и дописывает новую в свободный слот, а уборка освобождает те мёртвые версии, которых уже никто не видит.

живой пример

public class VacuumDemo {

    static final int[] slot = new int[6];
    static final long[] deletedBy = new long[6];
    static long xid = 100;

    public static void main(String[] args) {
        for (int row = 1; row <= 4; row++) {
            insert(row);
        }
        update(2);
        update(4);
        System.out.println("после UPDATE строк 2 и 4:  " + page());
        System.out.println("VACUUM при открытой транзакции 101: освобождено " + vacuum(101) + "  " + page());
        System.out.println("VACUUM после её завершения:        освобождено " + vacuum(xid + 1) + "  " + page());
        insert(5);
        System.out.println("после INSERT строки 5:     " + page());
        System.out.println("слотов в файле было и осталось: " + slot.length);
    }

    static void insert(int row) {
        int free = 0;
        while (slot[free] != 0) {
            free++;
        }
        slot[free] = row;
    }

    static void update(int row) {
        long now = ++xid;
        for (int i = 0; i < slot.length; i++) {
            if (slot[i] == row && deletedBy[i] == 0) {
                deletedBy[i] = now;
            }
        }
        insert(row);
    }

    static int vacuum(long horizon) {
        int freed = 0;
        for (int i = 0; i < slot.length; i++) {
            if (deletedBy[i] != 0 && deletedBy[i] < horizon) {
                slot[i] = 0;
                deletedBy[i] = 0;
                freed++;
            }
        }
        return freed;
    }

    static String page() {
        StringBuilder page = new StringBuilder();
        for (int i = 0; i < slot.length; i++) {
            page.append(slot[i] == 0 ? "[  ·]" : deletedBy[i] == 0 ? "[ " + slot[i] + " ]" : "[ " + slot[i] + "†]");
        }
        return page.toString();
    }
}
Запустить

Запуск примеров доступен в платном доступе. Там этот же код выполняется прямо в статье: редактор, запуск и проверка рядом с абзацем. Неделя бесплатно →

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

Три варианта команды

-- Обычный VACUUM: освобождает мёртвые строки, не блокирует таблицу
VACUUM order_doc;

-- С анализом: заодно обновляет статистику для планировщика
VACUUM ANALYZE order_doc;

-- Полная перезапись таблицы: возвращает место ОС, но блокирует всё
VACUUM FULL order_doc;

VACUUM и VACUUM ANALYZE — безопасно запускать в любое время.

VACUUM FULL — принципиально другая операция. Она полностью перезаписывает таблицу с нуля и при этом берёт блокировку ACCESS EXCLUSIVE. Это значит: пока идёт VACUUM FULL, таблица недоступна ни для чтения, ни для записи. На больших таблицах это могут быть часы. В продакшне почти никогда не применяют.

Альтернатива VACUUM FULL без блокировки — утилита pg_repack (или pg_squeeze). Она перестраивает таблицу в фоне, не мешая работе приложения:

pg_repack -d mydb -t order_doc

VACUUM ANALYZE стоит запускать вручную после массового UPDATE или DELETE — не ждите, пока сработает автоматика: планировщик может долго работать с устаревшей статистикой.

autovacuum — автоматическая уборка

PostgreSQL запускает VACUUM автоматически через демон autovacuum. Он следит за каждой таблицей и запускает уборку, когда накопилось достаточно мёртвых строк.

Порог срабатывания по умолчанию:

мёртвые строки > 50 + 0.2 × количество живых строк

На таблице в миллион строк autovacuum запустится при ~200 000 мёртвых. Для большинства таблиц этого достаточно.

Но для больших и активных таблиц дефолтные 20% — это слишком много. Настройку можно задать прямо на таблице:

ALTER TABLE order_doc SET (
    autovacuum_vacuum_scale_factor = 0.05,
    autovacuum_analyze_scale_factor = 0.05
);

Теперь autovacuum запустится при ~50 000 мёртвых строк вместо 200 000 — bloat будет меньше.

Как обнаружить bloat

Быстрый взгляд через системную статистику:

SELECT relname,
       n_live_tup,
       n_dead_tup,
       round(100.0 * n_dead_tup / NULLIF(n_live_tup, 0), 1) AS dead_pct,
       last_autovacuum,
       last_autoanalyze
FROM pg_stat_user_tables
WHERE n_live_tup > 1000
ORDER BY n_dead_tup DESC
LIMIT 10;

На что смотреть:

  • dead_pct > 20% — таблица кандидат на ручной VACUUM.
  • last_autovacuum давно (больше суток на горячей таблице) — autovacuum не успевает.

Для точной картины есть расширение pgstattuple:

CREATE EXTENSION pgstattuple;
SELECT * FROM pgstattuple('order_doc');
-- dead_tuple_percent > 30% — таблица явно распухла

Индексы тоже распухают — отдельно от таблиц. Лечится командой REINDEX CONCURRENTLY (доступна с PostgreSQL 12) — не блокирует таблицу во время перестройки.

fillfactor — место под обновления

По умолчанию PostgreSQL заполняет страницы данными до конца (fillfactor = 100). При UPDATE обновлённая строка чаще всего не помещается на ту же страницу и пишется на новую — а в индексах появляется лишняя запись.

Если таблица активно обновляется, имеет смысл оставить на страницах запас:

ALTER TABLE order_doc SET (fillfactor = 85);
VACUUM FULL order_doc;  -- применяет новый fillfactor (один раз, в окно обслуживания)

С fillfactor = 85 страницы заполняются только на 85%. Когда приходит UPDATE, обновлённая строка часто помещается на ту же страницу — это называется HOT update (Heap-Only Tuple). Индекс при этом не трогается, нагрузка меньше.

Когда autovacuum не справляется

Есть несколько ситуаций, когда autovacuum работает, но мёртвые строки всё равно накапливаются.

Долгая транзакция. MVCC запрещает удалять строки, которые видит хоть одна открытая транзакция. Если у вас висит транзакция часами — bloat будет расти независимо от autovacuum.

SELECT pid,
       age(now(), xact_start) AS xact_age,
       state,
       query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_age DESC
LIMIT 10;

Если xact_age больше 5 минут — стоит разобраться, что там. Типичные причины: незакрытая сессия в коде, зависший фоновый процесс.

Хорошая практика — установить таймаут idle_in_transaction_session_timeout = '30s': он автоматически закрывает транзакции, которые начали, но ничего не делают.

Зависший replication slot. Если реплика или подписчик сильно отстала, PostgreSQL держит WAL-файлы до момента, пока они не будут прочитаны, — и диск заполняется. Уборке старых строк слот сам по себе не мешает; это происходит, только если на реплике включён hot_standby_feedback: тогда она просит мастер придержать версии строк, нужные её собственным запросам.

Prepared-транзакции. Команды PREPARE TRANSACTION создают «подвешенные» транзакции, которые живут до явного COMMIT PREPARED или ROLLBACK PREPARED.

SELECT * FROM pg_prepared_xacts;

Если там что-то зависло — это блокирует уборку так же, как долгая обычная транзакция.

autovacuum выключен. Иногда его отключают перед массовой загрузкой данных и забывают включить обратно.

SELECT name, setting FROM pg_settings WHERE name = 'autovacuum';

SELECT relname, reloptions
FROM pg_class
WHERE relkind = 'r' AND reloptions::text LIKE '%autovacuum%';

XID wraparound — критическая ситуация

У PostgreSQL есть 32-битный счётчик транзакций (XID). Когда он исчерпывает ~2.1 миллиарда значений и «проходит круг», база может перепутать старые и новые транзакции. Чтобы этого не произошло, VACUUM «замораживает» старые строки — помечает их как видимые всем навсегда.

Проверить, насколько близко до опасной отметки:

SELECT datname,
       age(datfrozenxid)         AS xid_age,
       2147483647 - age(datfrozenxid) AS xids_left
FROM pg_database;

Если xid_age больше 1.5 миллиарда — autovacuum не справляется, нужно вмешаться вручную. Сначала PostgreSQL пишет в лог всё более настойчивые предупреждения. Если их проигнорировать до конца, он не «замедлит» запись, а просто перестанет её принимать: новые транзакции начать будет нельзя, пока не отработает уборка. База при этом продолжит отвечать на чтение, но для приложения это полноценная авария.

Коротко

  • PostgreSQL не удаляет строки сразу: UPDATE и DELETE оставляют старые версии (MVCC), из них и растёт bloat.
  • VACUUM освобождает место внутри файла, обновляет visibility map и замораживает старые строки от XID wraparound; файл при этом не уменьшается — отрезается только пустой хвост.
  • VACUUM FULL место операционной системе возвращает, но блокирует таблицу целиком: в продакшне вместо него pg_repack.
  • autovacuum срабатывает на «50 + 0.2 × живых строк»; на больших таблицах scale_factor снижают до 0.05.
  • Уборке мешают долгие транзакции, prepared-транзакции и отстающая реплика с hot_standby_feedback — их и ищут первыми, а не крутят настройки.
  • fillfactor 80–90 на активно обновляемых таблицах даёт HOT update; за age(datfrozenxid) следят отдельно — это не тормоза, а остановка записи.

Что почитать дальше