Проблема мягкого удаления (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.
Для тех, кто заинтересован в развитии своих профессиональных навыков и углубленном понимании ключевых процессов, рекомендую к посещению несколько открытых уроков от Otus. Они помогут расширить горизонты в области системного анализа: