Проблема мягкого удаления (deleted_at) и как её решить / Хабр

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

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

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

Юридические: нужно хранить следы операций для аудита.
Бизнесовые: надо «откатывать» удалённые записи.
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
);

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

Для новых записей — вставляем с valid_from = NOW(), valid_to = NULL.
Для изменённых — ищем is_current = TRUE, обновляем его valid_to = NOW(), is_current = FALSE, и вставляем новую версию.
Для «мягко удалённых» — аналогично обновляем 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.

Если вы давно уже работаете с аналитикой, проектированием или проектными данными, самое время проверить свои силы. Пройдите бесплатное тестирование по курсу «Системный аналитик. Advanced» — заодно поймёте, на чём уже уверенно стоите, а какие пробелы стоит закрыть.

А если хотите заглянуть вглубь профессии и пообщаться с экспертами — загляните в календарь открытых уроков и выберите ближайшие события. Всё по делу, с практикой и живыми кейсами.

Проблема мягкого удаления (deleted_at) и как её решить