Архивация в PostgreSQL: как не перегрузить базу при чистке

После удаления старых записей PostgreSQL не обязательно освободит место на диске, зато может начать дольше обслуживать запросы и отставать на репликах. Ниже — реконструкция типичного инцидента для PostgreSQL 16, а не история конкретной компании: реальные объёмы и причины зависят от схемы, нагрузки и настроек.

Как чистка превращается в инцидент

У сервиса растёт таблица событий. Команда запускает один большой DELETE по дате, рассчитывая за раз убрать весь архивный хвост. Запрос работает долго, генерирует WAL, а параллельные операции конкурируют за ресурсы. Реплика может начать отставать: ей нужно воспроизвести изменения, которые создала основная база.

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

Мёртвая версия строки — версия записи, которая больше не видна актуальным транзакциям после UPDATE или DELETE. PostgreSQL должен сохранить её до тех пор, пока старые транзакции могут ссылаться на прежнее состояние данных.

DELETE меняет содержимое таблицы, но не превращает её автоматически в меньший файл. Если цель — вернуть место операционной системе, одной команды удаления недостаточно.

Что проверить до следующей попытки

Сначала выясните, что именно растёт: сама таблица, индексы, число мёртвых строк или очередь воспроизведения на реплике. Статистика PostgreSQL приблизительная: показатели n_live_tup и n_dead_tup не заменяют точный подсчёт. Но вместе с размером отношения и состоянием autovacuum они помогают выбрать, где искать причину.

-- PostgreSQL 16: размеры таблиц и статистика очистки
SELECT
    relname,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
    n_live_tup,
    n_dead_tup,
    last_autovacuum,
    last_autoanalyze
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 20;

-- Запросы, которые сейчас ждут событие или блокировку
SELECT
    pid,
    state,
    wait_event_type,
    wait_event,
    now() - query_start AS query_age,
    left(query, 160) AS query_sample
FROM pg_stat_activity
WHERE state = 'active'
  AND wait_event IS NOT NULL
ORDER BY query_start;

Если размер таблицы большой, а n_dead_tup заметно вырос после удаления, проверьте работу autovacuum и долгие транзакции. Открытая транзакция может мешать очистке старых версий строк. Если проблема проявляется на реплике, отдельно наблюдайте задержку воспроизведения и объём WAL: само по себе дробление удаления не гарантирует, что суммарного WAL станет меньше.

Что делать вместо одного большого DELETE

Сначала определите срок хранения данных и сценарии чтения архива. Если приложение регулярно запрашивает свежие данные, но почти никогда — записи нескольких лет назад, перенос стоит строить вокруг этой границы. Для подхода, который зависит от схемы, есть три варианта.

Когда подходят порционные удаления

Это наименее радикальный вариант, если таблица пока не партиционирована, а старые записи можно удалять окончательно. Удаляйте ограниченную порцию, фиксируйте транзакцию, делайте паузу и смотрите на длительность операций, нагрузку и задержку реплики. Не запускайте следующий пакет, если система не успевает восстанавливаться между ними.

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

Когда выгодны партиции

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

Для PostgreSQL 16 порядок операций зависит от того, как устроены ограничения и связи таблиц. До изменения схемы проверьте официальную документацию по партиционированию и протестируйте миграцию на копии. Нельзя обещать бесшовное переключение только потому, что таблицу разбили по датам.

Где подход ломается

• Нагрузка не снижается. Небольшие DELETE убирают риск одной длинной транзакции, но множество коротких операций всё равно расходуют ресурсы и создают WAL. Настройте паузы и контролируйте репликацию.
• Место не возвращается. Обычный VACUUM позволяет повторно использовать свободное пространство внутри таблицы. VACUUM FULL переписывает таблицу и требует блокировки, поэтому это не безопасная универсальная команда для запуска в рабочее время.
• Очистка не заканчивается. Долгие транзакции могут удерживать старые версии строк. Найдите транзакции, которые долго открыты, и выясните, можно ли безопасно завершить их работу.
• Архив неполный. При выносе данных из основной таблицы сначала подтвердите, что копия полная и читается приложением; только затем удаляйте исходные записи. Продумайте повторный запуск после сбоя, чтобы он не создавал дубликаты и не терял строки.

Как выбрать стратегию и проверить результат

Если очистка разовая, объём умеренный и удалённые записи не нужны, начните с порционных удалений и мониторинга. Если старые данные регулярно уходят целыми временными диапазонами, оцените партиционирование. Если архив нужен для редких запросов, но не должен нагружать основную базу, сравните отдельное хранилище с ценой поддержки второго пути чтения.

До запуска запишите исходные показатели: размер таблицы и индексов, число мёртвых строк, длительность типовых запросов, состояние autovacuum и задержку реплики. После изменений повторите замеры при сопоставимой нагрузке. Без этих условий разница до и после мало что доказывает: например, свободное место внутри таблицы — не то же самое, что уменьшившийся размер файлов.

Что делать дальше

Составьте политику хранения: какие данные остаются в основной таблице, когда они переходят в архив и когда удаляются окончательно. Затем проверьте её на копии производственной схемы и измерьте длительность переноса, объём WAL, задержку реплик и влияние на запросы приложения. Для партиций сверяйтесь с разделом PostgreSQL 16 о партиционировании; для обычной очистки — с документацией по VACUUM и обслуживанию таблиц.