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

[kretuk]

мы фильтруем только пользователей. Всё удалённые заказы останутся в выборке, потому что у них своё 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;

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

[Kwisatz]

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

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

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

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

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

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

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

PS

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

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

[vlad4kr7]

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

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

is_deleted bool default false,

или

is_active bool default true

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

[Kwisatz]

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

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 добавить: тут ни в сказке сказать, ни пером описать...

[n0isy]

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

[Kwisatz]

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

[erogov]

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

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

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

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

[Kwisatz]

Благодарю,узнал много всяких прикольных штук. Однако, вот буквально только что читал что is [not] null не использует индексы, остальное прямо вот сяду изучать на выходных, много интересных штук пропустил.

[cupraer]

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

[Kwisatz]

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

[cupraer]

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

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

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

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

[Fragster]

del

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

[avvensis]

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

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

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

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

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

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

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

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

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

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

[Gromilo]

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

[Mur466]

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

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

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

[Fragster]

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

[Mur466]

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

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

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

[Fragster]

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

[aleksandy]

Тынц.

[surly]

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

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