Архивация данных в PostgreSQL: что проверить на ревью

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

Что именно проверять в коде архивации

Рассмотрим таблицу events с полями id, created_at и payload. Архивная таблица events_archive хранит те же поля, а id в ней уникален. Примеры рассчитаны на PostgreSQL 16; имена столбцов и структура здесь условные — перед применением их нужно сопоставить со схемой проекта.

Первое замечание на ревью: «Копирование и удаление должны быть одной транзакционной операцией». Схема «сначала INSERT, потом DELETE» опасна, если запросы выполняются в разных транзакциях или приложение может упасть между ними. Тогда повторный запуск создаст дубликаты либо удалит строки, которые так и не попали в архив. Одна транзакция решает проблему сбоя между запросами, но сама по себе не устраняет долгие блокировки при переносе огромного объёма.

Второе замечание: «Покажите, что будет с внешними ключами». Если другая таблица ссылается на events.id, удаление исходной строки может нарушить ограничение. Каскадное удаление способно убрать связанные данные, а не перенести их. Перед изменением нужно выяснить, какие таблицы ссылаются на события и какой смысл у этих связей после архивации.

Третье: «Условие отбора должно быть стабильным». Если дата события меняется во время обработки, строка может перестать подходить под фильтр или оказаться в неожиданной порции. Для архивации обычно выбирают неизменяемую временную метку и фиксированную границу — например, дату, старше которой строки считаются архивными. Если по бизнес-правилам дату можно исправлять, это поведение нужно отдельно учесть.

Хорошая архивация не просто убирает старые строки из рабочей таблицы. Она делает перенос повторяемым, ограничивает цену одной операции и сохраняет понятный путь к исходным данным.

Рефакторинг: переносить порциями и атомарно

Для PostgreSQL 16 можно выбрать ограниченную порцию строк, удалить её с возвратом данных и вставить возвращённые строки в архив в рамках одного SQL-оператора. Ниже предполагается, что запрос запускает приложение или задача с поддержкой параметра $1; это параметр границы даты, а не команда для ввода в обычную консоль psql.

WITH batch AS (
    SELECT id
    FROM events
    WHERE created_at < $1
    ORDER BY created_at, id
    LIMIT 1000
    FOR UPDATE SKIP LOCKED
), moved AS (
    DELETE FROM events AS e
    USING batch AS b
    WHERE e.id = b.id
    RETURNING e.id, e.created_at, e.payload
)
INSERT INTO events_archive (id, created_at, payload)
SELECT id, created_at, payload
FROM moved;

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

FOR UPDATE SKIP LOCKED блокирует выбранные строки и пропускает те, которые уже заняла другая транзакция. Это полезно, если архиватор работает параллельно с другими процессами или запущено несколько его экземпляров. Но пропущенная строка не исчезает навсегда: последующие проходы должны снова выбирать всё, что подходит под границу даты. Сам факт использования SKIP LOCKED не заменяет контроль результата и метрики задания.

CTE с изменением данных передаёт результат через RETURNING: архив получает именно те строки, которые удалены в этом операторе. Это снижает риск расхождения между отдельным предварительным SELECT и последующим DELETE. При этом уникальное ограничение в архиве остаётся полезной страховкой: если там уже есть такой id, вставка завершится ошибкой, а удаление откатится вместе с ней.

Что должно быть в ревью-комментариях

• «Какой индекс обслуживает выборку?» Фильтр по created_at и сортировка по created_at, id могут потребовать подходящего индекса. Его влияние оценивают на реальном плане и данных: новый индекс ускоряет чтение, но занимает место и добавляет стоимость записи.
• «Что произойдёт при конфликте ключа?» Не добавляйте ON CONFLICT DO NOTHING без ясной стратегии. Иначе строка может быть удалена из events, хотя архивная копия не была проверена на полное совпадение. Конфликт лучше считать сигналом для диагностики, пока команда не определила безопасное поведение.
• «Кто ещё читает эту таблицу?» Проверить нужно не только внешние ключи, но и отчёты, фоновые задачи, триггеры, аудит и потребителей изменений. Для них переезд строки может выглядеть как удаление, а не как перенос.
• «Что произойдёт при повторном запуске?» Повторный запуск должен обрабатывать только оставшиеся подходящие строки. Проверьте, что приложение фиксирует успешные порции и не отмечает задание завершённым после частичного сбоя.
• «Каков верхний предел одной транзакции?» Перенос всей истории одним запросом может долго удерживать блокировки, создавать большой объём WAL и затруднять обслуживание таблицы. Порции ограничивают размер одной операции, но требуют контролировать общее время работы.

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

Сравнение подходов

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

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

Маленькие порции не означают маленький общий объём работы. Архиватор всё ещё читает строки, пишет их в другую таблицу и удаляет исходные версии. После удаления PostgreSQL обычно не возвращает место операционной системе немедленно: освободившееся пространство может использоваться повторно внутри таблицы. Это стоит учитывать при оценке диска и не обещать, что архивирование автоматически уменьшит размер файлов.

Ещё один риск — незаметно накопить очередь. Если новых подходящих строк появляется быстрее, чем задача их переносит, архиватор будет работать постоянно, а отставание расти. Следите за числом перенесённых строк, длительностью порции, количеством ошибок и возрастом самой старой строки, которая всё ещё должна быть в архиве. В тестовой среде проверьте план выборки и измерьте запрос на данных, похожих на рабочие; EXPLAIN ANALYZE исполняет запрос, поэтому не запускайте его на изменяющем данные операторе в проде без безопасного плана проверки.

Осторожно и с конкурентным доступом. Пока строки переносятся, приложение может читать их из рабочей таблицы и не находить после удаления. Если пользователю или отчёту нужно видеть историю целиком, чтение должно учитывать и архив. Инвалидация кэша и изменение API чтения — отдельные темы; здесь речь только о переносе строк.

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

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

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