20.05.2025 19:01
badcasedaily1 18 6000 Источник

Привет, Хабр!

В этой статье рассмотрим классическую проблему «мягкого удаления» на уровне схемы баз данных и её влияние на аналитику.

Почти в каждой системе встречается требование «не удалять данные окончательно». Причины разные:

  • Юридические: нужно хранить следы операций для аудита.

  • Бизнесовые: надо «откатывать» удалённые записи.

  • UX: в интерфейсе показывать корзину, откуда можно восстановить элемент.

Самый простой способ — добавить в таблицу поле deleted_at TIMESTAMP NULL и при «удалении» записывать туда текущую метку времени. Всё остальное — при выборке добавляете WHERE deleted_at IS NULL. Казалось бы — дело в шляпе. Но на практике происходит пару проблемок.

Что происходит с JOIN’ами при наличии soft delete

Пример схемы

-- Таблица пользователей
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    name TEXT NOT NULL,
    deleted_at TIMESTAMP NULL
);

-- Таблица заказов
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL REFERENCES users(id),
    amount NUMERIC(10,2) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    deleted_at TIMESTAMP NULL
);

Если вы неаккуратно пишете JOIN:

SELECT
    u.id   AS user_id,
    u.name AS user_name,
    o.id   AS order_id,
    o.amount
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.deleted_at IS NULL;

мы фильтруем только пользователей. Всё удалённые заказы останутся в выборке, потому что у них своё deleted_at. В результате запись пользователя без активности выпадет, а заказы удалённого пользователя по-прежнему попадут в отчёт.

Если фильтр на каждый JOIN забыть, столбцы могут расти на удалённых сущностях.

Всегда дублируйте условие для каждой таблицы:

FROM users u
JOIN orders o
  ON o.user_id = u.id
  AND o.deleted_at IS NULL
WHERE u.deleted_at IS NULL;

Как моделировать удаление

Audit log через INSERT-триггеры

Если нужно сохранить полное «что было в момент T», заводите отдельную таблицу аудита.

-- Таблица аудита
CREATE TABLE users_audit (
    audit_id SERIAL PRIMARY KEY,
    id INT,
    email TEXT,
    name TEXT,
    deleted_at TIMESTAMP NULL,
    changed_at TIMESTAMP NOT NULL DEFAULT NOW(),
    operation CHAR(1) NOT NULL  -- I = insert, U = update, D = delete
);

-- Триггер на users
CREATE OR REPLACE FUNCTION users_audit_trigger() RETURNS trigger AS $$
BEGIN
  IF (TG_OP = 'INSERT') THEN
    INSERT INTO users_audit (id, email, name, deleted_at, operation)
    VALUES (NEW.id, NEW.email, NEW.name, NEW.deleted_at, 'I');
  ELSIF (TG_OP = 'UPDATE') THEN
    INSERT INTO users_audit (id, email, name, deleted_at, operation)
    VALUES (NEW.id, NEW.email, NEW.name, NEW.deleted_at, 'U');
  ELSIF (TG_OP = 'DELETE') THEN
    INSERT INTO users_audit (id, email, name, deleted_at, operation)
    VALUES (OLD.id, OLD.email, OLD.name, OLD.deleted_at, 'D');
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_users_audit
  AFTER INSERT OR UPDATE OR DELETE
  ON users
  FOR EACH ROW EXECUTE PROCEDURE users_audit_trigger();

SCD — Type 2

Если вы строите хранилище данных по Kimball-методологии, SCD Type 2 поможет сохранять историю до и после изменений, включая удаление.

CREATE TABLE dim_users (
    user_key SERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    email TEXT,
    name TEXT,
    valid_from TIMESTAMP NOT NULL,
    valid_to   TIMESTAMP NULL,  -- NULL означает «до сих пор валидно»
    is_current BOOLEAN NOT NULL DEFAULT TRUE
);

При загрузке:

  1. Для новых записей — вставляем с valid_from = NOW(), valid_to = NULL.

  2. Для изменённых — ищем is_current = TRUE, обновляем его valid_to = NOW(), is_current = FALSE, и вставляем новую версию.

  3. Для «мягко удалённых» — аналогично обновляем valid_to, is_current = FALSE.

Так отчётность по времени аккуратно покажет, какие записи существовали на любой момент. SCD удобно автоматизировать в dbt или Airflow.

Построение витрины с фильтрацией по живым записям

Предположим, хочется построить витрину активных пользователей с суммой заказов. Для скорости — воспользуемся materialized view.

CREATE MATERIALIZED VIEW active_user_orders AS
SELECT
  u.id        AS user_id,
  u.email,
  u.name,
  SUM(o.amount)::NUMERIC(14,2) AS total_amount,
  COUNT(o.id)               AS orders_count,
  MAX(o.created_at)         AS last_order_at
FROM users u
JOIN orders o
  ON o.user_id = u.id
  AND o.deleted_at IS NULL
WHERE u.deleted_at IS NULL
GROUP BY u.id, u.email, u.name;

Чтобы view всегда было актуальным, настраиваем simple cron:

# crontab -e
*/5 * * * * psql -d analytics -c "REFRESH MATERIALIZED VIEW CONCURRENTLY active_user_orders;"

Или интегрируем в Airflow:

from airflow import DAG
from airflow.providers.postgres.operators.postgres import PostgresOperator
from datetime import datetime, timedelta

with DAG(
    dag_id="refresh_active_user_orders",
    schedule_interval="*/5 * * * *",
    start_date=datetime(2025, 5, 20),
    catchup=False,
) as dag:
    refresh = PostgresOperator(
        task_id="refresh_mv",
        postgres_conn_id="analytics_db",
        sql="REFRESH MATERIALIZED VIEW CONCURRENTLY active_user_orders;",
    )

Тепер любой отчёт подтягиваете только живых пользователей и их заказы.

Когда стоит использовать партиции по deleted_at

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

-- Основная таблица с включённой партиционной логикой
CREATE TABLE orders (
    id SERIAL,
    user_id INT,
    amount NUMERIC(10,2),
    created_at TIMESTAMP NOT NULL,
    deleted_at TIMESTAMP NULL
) PARTITION BY RANGE (deleted_at);

-- Партиция «активные» (deleted_at IS NULL)
CREATE TABLE orders_active
  PARTITION OF orders
  FOR VALUES FROM (MINVALUE) TO ('infinity')
  WHERE deleted_at IS NULL;

-- Партиции для удалённых: например, ежемесячные архивы
CREATE TABLE orders_deleted_2025_04
  PARTITION OF orders
  FOR VALUES FROM ('2025-04-01') TO ('2025-05-01');

Все это дело ускоряет DELETE, т.к фактически вы просто меняете партицию или очищаете её. Фильтрация живых записей быстрее — индекс скан не идёт по всем партициям.

Подведем итоги

Soft delete — удобная штука, но требует дисциплины: фильтруйте deleted_at IS NULL в каждом JOIN.

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

Комментарии (18)


  1. kretuk
    20.05.2025 20:48
    #28331052

    мы фильтруем только пользователей. Всё удалённые заказы останутся в выборке, потому что у них своё deleted_at. В результате запись пользователя без активности выпадет, а заказы удалённого пользователя по-прежнему попадут в отчёт.

    SELECTu.idAS user_id,u.nameAS user_name,o.idAS order_id, o.amountFROMusers uJOINorders oONo.user_id= u.idWHERE u.deleted_at IS NULL;

    что у вас за странная СУБД такая, что этот запрос вам заказы удалённого пользователя?


  1. Kwisatz
    20.05.2025 20:48
    #28331082

    Вы же в курсе что null не индексируется, правда?

    Посему тут должна быть статья про is_deleted = false как явный или генерируемый столбец.

    А так же про индексы с where is_deleted=false, например уникальные.

    Чтобы view всегда было актуальным, настраиваем simple cron:

    Жесть... во первых чтобы оно ВСЕГДА было актуальным, пересчитывать надо при заключении заказов, во вторых лучше не пересчитывать всю таблицу (матвью) а иметь отдельную таблицу агрегатов

    Всегда дублируйте условие для каждой таблицы:

    Чушь редкостная. Так вы порушите логическую целостность, которую софтдел как раз призван сохранить.

    PS

    Для тех, кто заинтересован в развитии своих профессиональных навыков

    До уровня статьи? Крутая реклама....


    1. vlad4kr7
      20.05.2025 20:48
      #28331282

      А еще бывает нужно soft delete И уникальность.

      Тогда приходится делать 

      is_deleted bool default false,

      или 

      is_active bool default true

      и включать соответствующую колонку в uniq constraint


      1. Kwisatz
        20.05.2025 20:48
        #28331328

        не нужно. Есть такая штука, называется частичный индекс.

        create unique index users_email_uindex 
on acl.users (email) 
where is_deleted=false;

        если же ваша субд не поддерживает такое то есть вот такая хитрость

        create unique index users_email_uindex 
on acl.users ((case when is_deleted=false then email else null end));

        Тут мы используем функциональный индекс и тот факт что null не индексируется

        Тем более что включение is_deleted/is_active в индекс проблему не решает, стоит, например, два раза подряд удалить пользователя лицо с одним и тем же почтовым адресом.

        PS

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


        1. n0isy
          20.05.2025 20:48
          #28331610

          Какое решение тогда вы предлагаете использовать?


          1. Kwisatz
            20.05.2025 20:48
            #28331654

            Частичный индекс


    1. erogov
      20.05.2025 20:48
      #28331752

      Чушь редкостная

      Истинная правда. Но

      Вы же в курсе что null не индексируется

      в Постгресе как раз индексируются.


    1. cupraer
      20.05.2025 20:48
      #28331768

      А что не так с созданием рядом таблицы DeletedUsers которая будет точной копией Users и куда записи будут попадать из триггера на удаление? View тогда будет просто юнионом, если он вдруг часто нужен.


      1. Kwisatz
        20.05.2025 20:48
        #28331780

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


        1. cupraer
          20.05.2025 20:48
          #28331788

          зачем если есть частичные индексы

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

          таблица со всеми версиями

          Да, разумеется, я неточно выразился.


          1. Fragster
            20.05.2025 20:48
            #28334180

            del

            я буду читать комментарии перед ответом


      1. avvensis
        20.05.2025 20:48
        #28332180

        Идея с DeletedUsers и VIEW через UNION кажется простой и элегантной, но на практике влечёт за собой немало сложностей.

        Если у пользователя есть связанные сущности — заказы, активности, сообщения — то при его удалении (и переносе в DeletedUsers) придётся переносить и их. Иначе нарушится целостность (внешние ключи, каскадные удаления), да и восстановить данные будет невозможно. Получается, нужно создавать DeletedOrders, DeletedMessages, DeletedWhatever — с теми же колонками и логикой. С ростом модели количество «зеркальных» таблиц быстро удваивает схему.

        А самое главное — восстановление данных перестаёт быть надёжным. Например:

        • Не получится отличить заказы, удалённые пользователем вручную, от тех, что удалились каскадно;

        • Восстанавливать нужно уже кодом, не через триггеры, строго в порядке каскадного удаления;

        • Могут нарушиться уникальные индексы или внешние ключи при восстановлении;

        • Придётся придумывать идентификатор удаления наподобие request_id в микросервисах, чтобы связывать удалённые группы записей между собой. Сделать доступным такой request_id во всех триггерах удаления это нетривиальная задача;

        В итоге «простое» удаление с переносом в Deleted* может превратиться в мину замедленного действия, потому что успех восстановления будет зависеть от конкретных данных в таблицах и связей между ними. Тестировать такие ситуации будет сложно.

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

        В большинстве случаев soft delete (is_deleted, deleted_at, deleted_by и пр.) — это куда более управляемый и безопасный подход.


    1. Gromilo
      20.05.2025 20:48
      #28331806

      Можно использовать частичный индекс, в котором будут только не удалённые записи. + можно на него навешать уникальность, если нужно.


  1. Mur466
    20.05.2025 20:48
    #28332060

    Использование партиции как раз замедлит удаление, потому что приводит к копированию записи в новую партицию и удалению записи из старой партиции. С точки зрения скорости запроса с join, у которой ведущая таблица users, никакого улучшения не будет (если в плане используется доступ nested loops). То есть для данного конкретного отчёта из примера партиционирование вредно.

    Плюс ошибки с пониманием работы inner join и оперативности обновления matview, на которые уже указали выше.

    И в конце предложение кого-то чему-то научить на открытых уроках. Может стоит самим вернуться к RTFM?


    1. Fragster
      20.05.2025 20:48
      #28334194

      Удаление замедлит, но, кажется, чтение происходит намного чаще? Случаи, конечно, разные бывают, но в основном так.


      1. Mur466
        20.05.2025 20:48
        #28335686

        Автор обосновывает партиционирование именно ускорением удаления: "Все это дело ускоряет DELETE ". А это с точностью до наоборот. Если нет других причин, то зачем городить партиционирование? Оно имеет как плюсы, так и минусы, поэтому должны быть явные причины. Просто потому что "много записей" - это еще не аргумент.

        Допустим мы используем партиционирование по дате для таблиц которые по сути своей являются логами. То есть данные в них имеют ценность ограниченное время и не имеют на себя ссылок в виде внешних ключей. Партиционирование позволяет дешево очищать логи за прошлые периоды, так как drop партиции целиком гораздо дешевле, чем удаление всех записей в таблице или партиции. Это я сейчас про Oracle, может в Postgres свои особенности.

        В OLAP-базах можно использовать партиционирование для оптимизации запросов. Но в примере автора юзер-заказ имеется ввиду OLTP система. Там партиционирование для оптимизации запросов реже бывает полезно, так как запросы в основном идут по юзеру, по заказу, а не по дате за большой период (месяцы). А по дате за малый период (день) индекс по дате будет эффективнее.


  1. aleksandy
    20.05.2025 20:48
    #28332914

    Тынц.


  1. surly
    20.05.2025 20:48
    #28334904

    Чтобы не забыть отбросить удалённые данные, создайте view для каждой таблицы, включив в него условие «deleted_at IS NULL». Когда в приложении требуются только актуальные данные, используйте эти представления вместо таблиц.

    Непонятно, за чем введён дополнительный столбец is_active. Достаточно проверки по условию «valid_to IS NULL».