Флаг is_deleted превратил базу в свалку: цена мягкого удаления

MAATRIX

В какой-то момент кто-то в команде предложил: «давайте не удалять записи физически, а просто помечать флагом — вдруг понадобится восстановить». Решение приняли за пять минут на созвоне, и полгода спустя таблица orders весит вчетверо больше, чем реально активных заказов, половина запросов тормозит на seq scan, а один забытый WHERE без фильтра по флагу показал клиенту заказ, который он «удалил» три месяца назад. Мягкое удаление — рабочий паттерн, но у него есть цена, и её редко считают заранее. Разберём, зачем нужен is_deleted, что он стоит на самом деле и как держать эту стоимость под контролем.

Зачем вообще помечать, а не удалять

Идея мягкого удаления (soft delete) простая: вместо DELETE FROM orders WHERE id = 42 вы делаете UPDATE orders SET is_deleted = true, deleted_at = now() WHERE id = 42. Строка остаётся на месте, просто помечается как недействительная. У этого есть две по-настоящему весомые причины.

Первая — обратимость. Люди и код ошибаются: админ случайно жмёт «удалить» не в той строке, скрипт миграции сносит не тот диапазон id, пользователь удаляет аккаунт и через час пишет в поддержку «это была ошибка, верните». С физическим DELETE это либо восстановление из бэкапа (долго, грубо, задевает все изменения после снимка), либо данные потеряны навсегда. С флагом восстановление — это UPDATE ... SET is_deleted = false, секунды работы без даунтайма.

Вторая — ссылочная целостность. В реляционной базе на строку почти всегда что-то ссылается: заказ ссылается на клиента, платёж — на заказ, комментарий — на пост. Физическое удаление клиента, у которого есть 40 заказов, — это либо каскадное удаление всей истории (обычно нежелательно — вы теряете бухгалтерские и аналитические данные), либо ON DELETE SET NULL, которое оставляет заказы с оборванной связью, либо вообще запрет на удаление, пока не разберёшься с зависимостями вручную. Флаг is_deleted решает это без миграций схемы: клиент помечен удалённым, но order.customer_id по-прежнему валиден, JOIN не ломается, отчёты за прошлые периоды остаются корректными.

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

Честная цена: база растёт и никогда не уменьшается

Вот здесь начинается то, что на созвоне обычно не проговаривают. Физический DELETE освобождает место (точнее, помечает страницы как свободные для переиспользования — в PostgreSQL это делает VACUUM, но в конечном счёте объём данных перестаёт расти). Мягкое удаление — это всегда INSERT или UPDATE, то есть строка остаётся в таблице навсегда, если её явно не почистить отдельным процессом. База с is_deleted монотонно растёт, даже если реальный объём активных данных не меняется или падает.

Это не абстрактная проблема. Возьмём типичный SaaS с активным churn: 10 000 активных пользователей, но за три года через сервис прошло 60 000 регистраций, из которых 50 000 удалили аккаунт. Если вы храните это мягким удалением без очистки, таблица users и все связанные с ней таблицы (sessions, notifications, activity_log) физически хранят данные для всех 60 000, а не для 10 000 живых. Индексы растут вместе с таблицей — B-tree индекс на 60 000 строк тяжелее, чем на 10 000, и это отражается на каждой операции чтения и записи через этот индекс (подробнее о том, как размер индекса связан со скоростью запроса, — в разборе когда индекс перестаёт помогать).

Отдельная головная боль в PostgreSQL — это то, что UPDATE (которым делается мягкое удаление) создаёт мёртвую версию строки (dead tuple) в MVCC-модели, точно так же, как обычный DELETE. То есть сама операция мягкого удаления производит тот же мусор для VACUUM, что и жёсткое удаление, — просто «удалённая» версия строки при этом остаётся видимой как новая живая строка с флагом true. Если на таблице параллельно идёт активный поток обновлений (например, часто редактируемые заказы), bloat растёт с двух сторон: от самих UPDATE-ов и от накопления помеченных-но-не-вычищенных строк. Если у вас уже есть проблема с медленным автовакуумом, накопление мягко удалённых записей её усугубляет — стоит свериться с разбором почему VACUUM не успевает и что с этим делать.

Ловушка №2: забытый фильтр по флагу

Более коварная цена мягкого удаления — не размер, а корректность. DELETE физически убирает строку, и никакой код никогда её больше не увидит — гарантия дана на уровне СУБД. is_deleted = true — это просто ещё одно поле, и гарантия того, что «удалённые» записи не всплывут, лежит целиком на дисциплине разработчиков: каждый SELECT, каждый JOIN, каждый агрегат должен не забыть добавить WHERE is_deleted = false.

На практике это забывают регулярно:

• новый разработчик пишет запрос к таблице orders, не зная о существовании флага, и в отчёт попадают удалённые заказы;
• добавляется JOIN с другой таблицей, у которой тоже есть is_deleted, и фильтр ставят только на одну сторону;
• меняется ORM-модель, дефолтный scope теряется при кастомном запросе (raw SQL, unscoped), и внезапно API отдаёт «удалённых» пользователей;
• агрегирующий отчёт (COUNT(*), SUM(amount)) считает по всей таблице, включая мягко удалённые строки, и цифры не сходятся с реальностью.

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

Практические способы снизить риск:

• Представление (view) вместо прямого обращения к таблице. Создайте CREATE VIEW active_orders AS SELECT * FROM orders WHERE is_deleted = false и приучите команду работать через него для обычных операций, оставляя прямой доступ к таблице только для административных задач и самой логики удаления/восстановления.
• Default scope на уровне ORM, если фреймворк это поддерживает (Laravel Eloquent, Django с кастомным менеджером, ActiveRecord с default_scope) — так фильтр применяется автоматически, и его нужно явно снимать, а не явно добавлять.
• Частичный индекс в PostgreSQL под самый частый случай:

CREATE INDEX idx_orders_active ON orders (customer_id, created_at)
WHERE is_deleted = false;

Такой индекс не просто ускоряет типовые запросы к активным строкам — он физически меньше полного индекса на всю таблицу, потому что не включает мягко удалённые строки вообще. Это частично компенсирует рост от накопленных is_deleted = true записей, хотя сама таблица всё равно продолжает расти.

• Уникальные constraint'ы с учётом флага. Частая ошибка: UNIQUE(email) не даёт зарегистрироваться заново тем же email, если старый (удалённый) аккаунт с этим email всё ещё физически в базе. Решение — частичный уникальный индекс: CREATE UNIQUE INDEX ON users (email) WHERE is_deleted = false.

Как накапливается «свалка» на практике

Возьмём таблицу логов активности (activity_log) в реальном проекте среднего размера. На старте — чистая идея: хотим уметь показать пользователю историю его действий и на всякий случай ничего не терять. Год спустя картина типичная:

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

Это не только вопрос дискового пространства (которое на VPS и выделенных серверах тоже стоит денег и конечно по объёму). Это вопрос скорости бэкапов — pg_dump или mysqldump по таблице с 16 млн строк идёт заметно дольше, чем по таблице с 2 млн, и восстановление из такого бэкапа тоже дольше. Это вопрос памяти — если у вас недостаточно RAM, чтобы держать рабочий набор (активные строки) в буферном кеше, а таблица раздута мусором в 8 раз, вытесняются из кеша именно нужные данные, и производительность падает не постепенно, а скачком.

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

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

Ключевой вопрос — какой срок считать разумным. Это не техническое решение, а продуктовое и иногда юридическое: обсудите его с командой и, если применимо, с юристом (требования вроде GDPR или локального законодательства о персональных данных могут диктовать конкретные сроки хранения). Ориентировочные, часто встречающиеся окна:

• 30 дней — для случайных удалений пользователем через интерфейс (корзина, «отменить» в течение месяца);
• 90 дней — для данных, где возможны споры или обращения в поддержку (заказы, платежи);
• дольше или бессрочно с переносом в архивное хранилище — для данных, где требуется аудит.

Сам процесс физической очистки — простой batch-джоб, который можно повесить на cron:

-- Пример: физически удаляем то, что помечено удалённым больше 90 дней назад
DELETE FROM orders
WHERE is_deleted = true
  AND deleted_at < now() - interval '90 days';

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

DO $$
DECLARE
  deleted_count integer;
BEGIN
  LOOP
    DELETE FROM orders
    WHERE id IN (
      SELECT id FROM orders
      WHERE is_deleted = true AND deleted_at < now() - interval '90 days'
      LIMIT 1000
    );
    GET DIAGNOSTICS deleted_count = ROW_COUNT;
    EXIT WHEN deleted_count = 0;
    COMMIT;
  END LOOP;
END $$;

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

Если данные перед физическим удалением жалко терять насовсем (для аналитики задним числом, для аудита) — выгружайте их в холодное хранилище перед очисткой: отдельная архивная таблица, партиция, или выгрузка в объектное хранилище (S3-совместимое) в виде Parquet/CSV. Это разделяет задачи: горячая таблица остаётся маленькой и быстрой, а история никуда не пропадает, просто лежит там, где её редко читают. Здесь же стоит свериться с тем, как мягко и физически удалённые данные соотносятся с политикой хранения бэкапов — если запись физически вычищена из продовой базы, но продолжает жить в бэкапах ещё год, это та же проблема «удалено, но не совсем», только уровнем ниже (подробный разбор — в статье про регламент удаления данных и копии в бэкапах).

Альтернативы и промежуточные варианты

Полный отказ от мягкого удаления в пользу вечного DELETE — не единственная альтернатива классическому is_deleted boolean. Несколько вариантов, которые стоит держать в голове.

deleted_at timestamp вместо is_deleted boolean. Функционально то же самое (WHERE deleted_at IS NULL), но сразу даёт момент удаления без отдельного поля и удобнее ложится в условия batch-очистки и партиционирование.

Партиционирование по времени. Партиционируете таблицу по deleted_at или created_at (например, помесячно) — тогда физическая очистка старых удалённых записей превращается в DROP PARTITION, что на порядки быстрее построчного DELETE и не создаёт bloat вообще: партиция уходит одной операцией на уровне файловой системы.

Отдельная архивная таблица. Перенос строки в orders_archive при удалении (триггером или в коде). Плюс — основная таблица компактна с самого начала. Минус — сложнее с ссылочной целостностью и требует дисциплины не забывать переносить связанные строки.

Event sourcing. Для систем, где важна полная история изменений, а не просто факт удаления, иногда правильнее хранить поток событий (OrderCreated, OrderDeleted) и строить текущее состояние проекцией. Тяжеловеснее в реализации и оправдано не всегда, но снимает саму дилемму — «удалённость» становится последним событием в потоке, а не мутацией строки.

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

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