Таблица истории изменений выросла больше самой базы

MAATRIX

Смотрите на \d+ в psql или на вывод SHOW TABLE STATUS в MySQL и не верите цифрам: таблица orders_history весит 40 ГБ, а сама orders — 6 ГБ. Бэкапы стали занимать втрое больше места, чем нужно для реальных данных, VACUUM на истории идёт часами, а искал кто-то один раз, кто поменял статус заказа три месяца назад. Если это про вас — ниже разбор, откуда берётся такой перекос и что с ним делать, не удаляя историю вслепую и не переезжая на сервер с диском в два раза больше.

Зачем вообще заводят таблицу истории изменений

Таблица истории (audit log, change history, версионирование записей — называйте как удобно) решает две задачи, которые обычная таблица с текущим состоянием не решает в принципе:

Во-первых, это ответ на вопрос «кто и когда это изменил». Обычная таблица orders хранит только текущее состояние заказа: статус shipped, сумму, адрес. Если вчера статус был processing, а позавчера pending, эта информация не сохраняется — UPDATE просто перезаписывает поле. Как только в дело идут споры с клиентами, отмены платежей, разбор инцидентов «почему заказ отменился сам собой» или требования комплаенса вроде 152-ФЗ (кто обрабатывал персональные данные и когда) — без истории изменений расследование упирается в стену.

Во-вторых, это возможность посмотреть предыдущую версию записи целиком, а не только факт «что-то поменялось». Отличие от обычных логов приложения важное: там пишут события («заказ #4521 обновлён пользователем 88»), а таблица истории хранит именно данные — значения полей до и после изменения. Это нужно, чтобы откатить ошибочное массовое обновление, восстановить случайно затёртые данные или показать клиенту, что адрес доставки менял именно он, а не служба поддержки.

Типичная реализация — триггер на UPDATE и DELETE, который при каждом изменении строки кладёт копию (старую или новую версию, а часто обе) в отдельную таблицу:

CREATE TABLE orders_history (
    history_id  bigserial PRIMARY KEY,
    order_id    bigint NOT NULL,
    operation   char(1) NOT NULL,      -- 'U' или 'D'
    changed_at  timestamptz NOT NULL DEFAULT now(),
    changed_by  integer,
    status      text,
    amount      numeric(12,2),
    address     text                  -- и так далее, по одному столбцу на каждый столбец orders
);

CREATE OR REPLACE FUNCTION orders_audit_trigger() RETURNS trigger AS $$
BEGIN
    INSERT INTO orders_history (order_id, operation, changed_by, status, amount, address)
    VALUES (OLD.id, CASE WHEN TG_OP = 'DELETE' THEN 'D' ELSE 'U' END,
            current_setting('app.current_user_id', true)::int,
            OLD.status, OLD.amount, OLD.address);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER orders_audit AFTER UPDATE OR DELETE ON orders
    FOR EACH ROW EXECUTE FUNCTION orders_audit_trigger();

Задача изначально правильная и почти всегда оправданная. Проблема начинается не с решения завести историю, а с того, что её рост никто заранее не спланировал.

Почему история растёт быстрее, чем кажется

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

Возьмём заказ, который живёт своей обычной жизнью: создан → оплачен → передан в сборку → передан в доставку → доставлен → возможно, оформлен возврат. Это уже 5-6 переходов статуса. Добавьте изменение адреса, правку суммы после перерасчёта скидки, комментарий поддержки, повторную попытку оплаты — и на один заказ набегает 10-15 записей в истории при том, что в основной таблице он как был одной строкой, так и остался. Умножьте это на активно используемую таблицу с миллионами строк — и таблица истории оказывается больше основной не в разы, а на порядок, причём это не аномалия, а нормальное поведение схемы.

Есть и второй эффект, который усиливает разрыв: триггер на UPDATE обычно не разбирает, изменилось ли что-то по сути. Многие ORM (Django, Rails ActiveRecord, Hibernate) при сохранении объекта выполняют UPDATE по всем столбцам модели, даже если реально поменялось одно поле, а updated_at — просто побочный эффект. Если триггер стоит на AFTER UPDATE без условия, каждое такое «пустое» сохранение тоже улетает в историю — так же, как фоновая задача, которая раз в минуту продлевает last_activity_at, генерирует историю с частотой, никак не связанной с реальными бизнес-событиями.

Третий фактор — ширина строки. Если триггер копирует всю строку целиком, а не только изменившиеся поля, одна запись истории по размеру сопоставима со строкой основной таблицы плюс метаданные (changed_at, changed_by, operation). История растёт не только числом строк — каждая строка ещё и «тяжелее» основной на несколько столбцов.

Итог: для активной OLTP-таблицы совершенно реалистично увидеть историю, которая в 5-20 раз больше основной таблицы — это не признак того, что что-то сломано, а прямое следствие модели «одна строка истории на каждое сохранение». Похожая картина, только в другом контексте, разбирается в статье про векторную базу, которая внезапно выросла в 10 раз: в обоих случаях источник роста — не увеличение полезных данных, а неучтённая кратность операций.

Сначала измерьте масштаб, а не гадайте

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

SELECT
    relname AS table_name,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
    pg_size_pretty(pg_relation_size(relid)) AS table_only,
    pg_size_pretty(pg_indexes_size(relid)) AS indexes_size
FROM pg_catalog.pg_statio_user_tables
WHERE relname IN ('orders', 'orders_history')
ORDER BY pg_total_relation_size(relid) DESC;

Дальше полезно посмотреть распределение записей истории по времени (SELECT date_trunc('month', changed_at), count(*) FROM orders_history GROUP BY 1 ORDER BY 1 DESC) — часто оказывается, что основная масса строк накопилась не равномерно, а всплесками из-за конкретной причины: массовый импорт, баг в фоновой задаче, разовая миграция данных.

Стоит также прикинуть долю «пустых» изменений — записей, где все отслеживаемые поля совпадают со значением в предыдущей версии (типичный след триггера без условия WHEN, реагирующего на любой UPDATE). Если таких записей заметная часть — это самый дешёвый источник сокращения объёма, ещё до всякой архивации. В MySQL аналогичная оценка размера — через information_schema.tables:

SELECT table_name, 
       round(data_length / 1024 / 1024, 1) AS data_mb,
       round(index_length / 1024 / 1024, 1) AS index_mb
FROM information_schema.tables
WHERE table_schema = 'shop' AND table_name IN ('orders', 'orders_history');

Отдельно проверьте индексы истории: если на orders_history висит индекс по каждому столбцу «на всякий случай», они сами по себе могут занимать объём, сопоставимый с данными, — при том, что реально используется только (order_id, changed_at) для выборки истории конкретной записи за период. Лишние индексы на таблице, которая почти не читается, — чистый оверхед на каждой вставке.

Политика хранения: сколько версий и сколько времени

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

• Срок хранения по типу данных. Финансовые операции и изменения, связанные с персональными данными, часто попадают под требования законодательства — там срок задаётся не вами (в РФ для части бухгалтерских данных это годы, детали разбираются в статье о сроках хранения бухгалтерских данных). Для остального — решение бизнеса: как правило, достаточно 6-24 месяцев подробной истории, дальше нужны только агрегаты или ничего.
• Число версий, а не только время. Для записей с аномально высокой частотой изменений (статус в очереди, счётчики) осмысленнее ограничивать не срок, а количество хранимых версий на запись — например, последние 20 изменений, а не «всё за 2 года».
• Разные политики для разных таблиц. История изменений заказов и история изменений пользовательских UI-настроек — это не одна и та же ценность данных. Не обязательно применять одинаковый SLA хранения ко всем таблицам с аудитом.

Технически удаление старых записей истории лучше не делать через DELETE ... WHERE changed_at < ... по многомиллионной таблице — это создаёт мёртвые кортежи, раздувает таблицу ещё больше (в PostgreSQL) и требует последующего VACUUM FULL, который блокирует таблицу. Правильный путь — партиционировать orders_history по времени (PARTITION BY RANGE (changed_at), помесячно) и удалять целые партиции вместо построчного DELETE:

CREATE TABLE orders_history_2026_08 PARTITION OF orders_history
    FOR VALUES FROM ('2026-08-01') TO ('2026-09-01');

-- удаление устаревшей партиции — мгновенная операция, а не долгий DELETE
DROP TABLE orders_history_2024_08;

Для автоматизации создания и удаления партиций по расписанию удобно расширение pg_partman — оно само создаёт партиции на будущее и умеет по правилу (например, «хранить 24 месяца») дропать устаревшие. В MySQL похожая механика — PARTITION BY RANGE (TO_DAYS(changed_at)) с ALTER TABLE ... DROP PARTITION вместо DELETE.

Архивация: разделяем горячую и холодную историю

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

Практический паттерн — разделение на два слоя:

1. Горячая история — последние несколько месяцев, в той же базе, с индексами под типичные запросы («покажи историю заказа X»). Именно этот слой должен быть быстрым.
2. Холодная история — партиции старше порога, выгруженные из активной базы в дешёвое хранилище: отдельная таблица в той же СУБД, но на медленном томе или в отдельной базе, либо файлы (CSV/Parquet) в объектном хранилище.

Перенос партиции в архив перед дропом обычно выглядит как выгрузка в файл и последующий DROP:

psql -d shop -c "\COPY orders_history_2024_08 TO '/backup/archive/orders_history_2024_08.csv' CSV HEADER"
gzip /backup/archive/orders_history_2024_08.csv
# после проверки, что архив читаемый и полный:
psql -d shop -c "DROP TABLE orders_history_2024_08;"

Если старую историю всё же нужно поднимать быстро (например, по требованию комплаенса за 3 года), вместо плоских файлов удобнее держать вторую, «архивную» таблицу без лишних индексов — только с одним составным индексом по (order_id, changed_at), без остальных, которые нужны лишь горячим запросам.

Перенос данных в архив и удаление из горячей таблицы стоит гонять по расписанию — cron, systemd timer или расширение pg_cron прямо в базе, а не вручную раз в год, когда диск уже загорелся красным.

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

Что реально логировать, а что избыточно

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

Практические меры:

• Сравнивайте старое и новое значение в триггере, а не пишите строку при любом UPDATE. В PostgreSQL это делается условием WHEN прямо на уровне триггера — тогда «пустые» UPDATE (когда ORM пересохранил объект без реальных изменений) вообще не доходят до функции:

CREATE TRIGGER orders_audit AFTER UPDATE ON orders
    FOR EACH ROW
    WHEN (OLD.status IS DISTINCT FROM NEW.status
          OR OLD.amount IS DISTINCT FROM NEW.amount
          OR OLD.address IS DISTINCT FROM NEW.address)
    EXECUTE FUNCTION orders_audit_trigger();

• Храните дельту, а не полный снимок строки, если столбцов много, а меняется обычно один-два: вместо отдельного столбца на каждое поле orders — одна колонка jsonb с изменившимися парами ключ-значение ({"status": {"old": "pending", "new": "paid"}}). Это резко сокращает средний размер строки истории на широких таблицах — платите только за то, что реально изменилось, а не за копию всей строки ради одного поля.
• Не аудируйте всё подряд одним и тем же способом. Разделите столбцы на «бизнес-значимые» (статус, сумма, адрес — то, что реально может стать предметом спора или проверки) и «технические» (счётчики синхронизации, кэш-метки, updated_at) — вторые вообще не должны попадать в аудит-триггер.
• Для таблиц со сверхвысокой частотой обновлений (очереди, счётчики просмотров, статусы задач) рассмотрите вариант вообще не вести построчную историю в той же СУБД, а выносить поток изменений через логическую репликацию или CDC-инструмент во внешнее хранилище — тогда OLTP-база не платит место и I/O за аудит, который читают редко и не в проде.

Частые вопросы