Мягкое удаление чаще всего не нужно

Те, кто знаком хотя бы с парой разных окружений баз данных продакшена, скорее всего, знают паттерн «мягкого удаления» (soft deletion): вместо удаления данных напрямую конструкцией DELETE таблицы получают дополнительную временную метку deleted_at и удаление выполняется конструкцией обновления:

UPDATE foo SET deleted_at = now() WHERE id = $1;

Мягкое удаление необходимо для того, чтобы удаление выполнялось безопаснее и было обратимым. После того, как запись удалили «жёстким» DELETE, теоретически её всё равно можно восстановить, углубившись в слой хранения, но нужно сказать, что вернуть её очень сложно. В теории, при мягком удалении достаточно снова присвоить deleted_at значение NULL, и на этом всё:

-- и волшебным образом всё вернулось на место!!
UPDATE foo SET deleted_at = NULL WHERE id = $1;

Недостатки: утечка кода

Однако этот паттерн имеет серьёзные недостатки. Во-первых, логика мягкого удаления растекается на все части кода. Все SELECT будут выглядеть примерно так:

SELECT *
FROM customer
WHERE id = @id
    AND deleted_at IS NULL;

А если забыть о дополнительном предикате для deleted_at, то это может иметь серьёзные последствия: случайно будут возвращены данные, которые больше не должны быть видимы.

Некоторые ORM или ORM-плагины упрощают ситуацию, автоматически добавляя дополнительный оператор deleted_at в каждый запрос (см., например, acts_as_paranoid), но только то, что записи оказываются скрытыми, не улучшает положение. Если конструкция выполнит запрос к базе данных напрямую, то, скорее всего, о deleted_at забудут, потому что обычно этот оператор добавляет ORM.

Утеря внешних ключей

Ещё одним последствием мягкого удаления является, по сути, утеря внешних ключей.

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

Благодаря внешним ключам попытка удаления такого клиента без удаления накладных будет являться ошибкой:

ERROR:  update or delete on table "customer" violates
    foreign key constraint "invoice_customer_id_fkey" on table "invoice"

DETAIL:  Key (id)=(64977e2b-40cc-4261-8879-1c1e6243699b) is still
    referenced from table "invoice".

Как и другие элементы реляционной БД наподобие заданных схем, типов и ограничений check, ключи помогают поддерживать валидность БД.

Но из-за мягкого удаления вся система разваливается. Клиент может быть удалён установкой флага deleted_at, но мы снова можем забыть сделать то же самое для накладных. Их внешние ключи по-прежнему валидны, поскольку запись клиента, строго говоря, всё ещё существует, но нет эквивалентной проверки мягкого удаления накладных, поэтому у нас может остаться «удалённый» клиент и его действительные накладные.

Усложняется отсечение данных

За последние несколько лет сделаны серьёзные шаги для обеспечения защиты данных потребителей, например, ввод в действие GDPR в Европе. Поэтому в общем случае стоит опасаться неограниченного по времени хранения данных, а по умолчанию большинство мягко удалённых строк будет находиться именно в таком состоянии.

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

Однако те же внешние ключи, которые из-за мягкого удаления стали практически бесполезными, теперь усложняют эту работу, потому что запись нельзя удалить, не гарантировав удаления и всех её зависимостей (ON DELETE CASCADE может делать это автоматически, однако использование каскада достаточно опасно и не рекомендуется для данных с повышенной точностью воспроизведения).

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

WITH team_deleted AS (
    DELETE FROM team
    WHERE (
        team.archived_at IS NOT NULL
        AND team.archived_at < @archived_at_horizon::timestamptz
    )
    RETURNING *
),

--
-- team resources
--
cluster_deleted AS (
    DELETE FROM cluster
    WHERE team_id IN (
        SELECT id FROM team_deleted
    )
    OR (
        archived_at IS NOT NULL
        AND archived_at < @archived_at_horizon::timestamptz
    )
    RETURNING *
),
invoice_deleted AS (
    DELETE FROM invoice
    WHERE team_id IN (
        SELECT id FROM team_deleted
    )
    OR (
        archived_at IS NOT NULL
        AND archived_at < @archived_at_horizon::timestamptz
    )
    RETURNING *
),

--
-- cluster + team resources
--
subscription_deleted AS (
    DELETE FROM subscription
    WHERE cluster_id IN (
        SELECT id FROM cluster_deleted
    ) OR team_id IN (
        SELECT id FROM team_deleted
    )
    RETURNING *
)

SELECT 'cluster', array_agg(id) FROM cluster_deleted
UNION ALL
SELECT 'invoice', array_agg(id) FROM invoice_deleted
UNION ALL
SELECT 'subscription', array_agg(id) FROM subscription_deleted
UNION ALL
SELECT 'team', array_agg(id) FROM team_deleted;

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

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

Действительно ли работает восстановление?

Повторюсь, теоретически мягкое удаление является препятствием для случайной утери данных. И в качестве последнего аргумента против него я прошу вас реалистично оценить, действительно ли восстановление удалённого когда-то выполняется.

Когда я работал в Heroku, мы использовали мягкое удаление.

Когда я работал в Stripe, мы использовали мягкое удаление.

И на моей нынешней работе мы используем мягкое удаление.

Насколько я знаю, ни разу за десять с лишним лет ни в одной из этих компаний мягкое удаление не использовалось для восстановления данных. [Дополнение: бывший коллега из Stripe только что написал мне, что, по крайней мере, давно мы иногда восстанавливали записи клиентов для пользователей, попавших в реальные неприятности, но это было редко и не приветствовалось. Спасибо, О. Б.!]

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

Пару раз в Heroku важный пользователь случайно удалял приложение и хотел его восстановить. У нас использовалось мягкое удаление, и теоретически другие побочные эффекты удаления невозможно было откатить, но нас убеждали не пробовать, потому что никто раньше этого не делал, а пытаться делать что-то в срочном порядке — самое неподходящее для этого время; что-то совершенно точно пойдёт не так, и пользователь окажется в плохом состоянии. Вместо этого мы создавали новое приложение и помогали пользователю скопировать в него окружение и данные из удалённого приложения. То есть даже в ситуации, когда мягкое удаление теоретически наиболее полезно, мы его не использовали.

Альтернатива: таблица удалённых записей

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

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

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

CREATE TABLE deleted_record (
    id uuid PRIMARY KEY DEFAULT gen_ulid(),
    deleted_at timestamptz NOT NULL default now(),
    original_table varchar(200) NOT NULL,
    original_id uuid NOT NULL,
    data jsonb NOT NULL
);

Тогда удаление будет выглядеть так:

WITH deleted AS (
    DELETE FROM customer
    WHERE id = @id
    RETURNING *
)
INSERT INTO deleted_record
		(original_table, original_id, data)
SELECT 'foo', id, to_jsonb(deleted.*)
FROM deleted
RETURNING *;

Такая система имеет недостаток по сравнению с deleted_at: процесс выбора столбцов в jsonb не так-то легко обратить. Хотя это можно сделать, для этого, скорее всего, пришлось бы создавать запросы для единичных случаев и вмешиваться вручную. Но это вполне может вас устроить — просто вспомните, как часто вам на самом деле приходилось пробовать восстанавливать данные после удаления.

Эта техника решает все перечисленные выше проблемы:

• В запросы для обычных неудалённых данных больше не нужно добавлять повсюду deleted_at IS NULL.
• Внешние ключи продолжают работать. Попытка удаления записи без её зависимостей будет являться ошибкой.
• Жёсткое удаление старых записей по требованию законодательства становится очень простым: DELETE FROM deleted_record WHERE deleted_at < now() - '1 year'::interval.

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