Привет, Хабр!
В этой статье рассмотрим классическую проблему «мягкого удаления» на уровне схемы баз данных и её влияние на аналитику.
Почти в каждой системе встречается требование «не удалять данные окончательно». Причины разные:
-
Юридические: нужно хранить следы операций для аудита.
-
Бизнесовые: надо «откатывать» удалённые записи.
-
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. Они помогут расширить горизонты в области системного анализа:
ссылка на оригинал статьи https://habr.com/ru/articles/910882/
Добавить комментарий