В этой статье мы рассмотрим: что такое мягкое удаление, зачем оно нужно и как его лучше реализовать.
В отличии от физического удаления (hard deletion), которое выполняется при помощи оператора DELETE, при мягком удалении (soft deletion) строка таблицы только помечается как удаленная, но остается в таблице. Во-первых, это позволяет быстро удалять сразу много строк (напр, когда пользователь в CRM-системе удаляет все сделки, которые удовлетворяют определенному условию), либо удалять 1 строку с большим количеством связей (напр, когда пользователь интернет-магазина удаляет свой аккаунт, с которым связано много заказов). Во-вторых, мягкое удаление позволяет восстанавливать уже удаленные строки (напр, восстановить случайно удаленные сделки в CRM-системе).
Мягкое удаление можно реализовать следующими способами:
- Добавить колонку deleted_at, которая будет хранить метку времени, когда произошло удаление строки. Если строка не удалена (далее – «активна»), то значение будет равно NULL. Запросы к таблице должны содержать условие WHERE deleted_at IS NULL, если запрашиваются активные строки, либо WHERE deleted_at IS NOT NULL, если запрашиваются удаленные строки.
- Создать отдельную таблицу для хранения удаленных строк. Как вариант, можно для этого создать отдельную схему deleted, в которой будут храниться все таблицы с удаленными строками. Например, у нас есть таблицы chats и deleted.chats. При удалении строки необходимо выполнить операцию DELETE в таблице chats и INSERT в таблице deleted.chats.
При реализации первого способа необходимо некоторые индексы сделать частичными (подробнее ниже) и использовать во всех запросах условия WHERE deleted_at IS NULL и WHERE deleted_at IS NOT NULL (можно упростить это; подробнее ниже). Операции мягкого удаления и восстановления будут производится быстро, т.к. необходимо лишь обновить колонку deleted_at у нужных строк.
Во втором случае, индексы изменять не нужно, да и условия добавлять не потребуется, т.к. активные и удаленные строки хранятся в разных таблицах. Однако массовое удаление и восстановление будет производится гораздо медленнее, т.к. необходимо удалить строки из одной таблицы и добавить их в другую.
Если строки удаляются редко, поштучно и на них не ссылается множество других строк (напр, в чате может быть несколько миллионов сообщений), то второй вариант будет более оптимальным. Однако, чаще всего удаляемые строки имеют много связей (напр, при удалении пользователя надо удалить все его чаты, в каждом из которых могут быть миллионы сообщений).
Разбиение таблицы на секции (table partitioning) я не рассматриваю, т.к. данное решение будет иметь ту же проблему: при массовом удалении необходимо удалить строки из одной секции и добавить их во вторую (это будет производится под капотом).
Предположим, что у пользователя есть чат с 1 млн сообщений (связь с пользователем опустим для простоты):
CREATE TABLE chats (id serial primary key, name text);
CREATE TABLE messages (id serial primary key, chat_id int REFERENCES chats ON DELETE CASCADE, body text);
INSERT INTO chats (name) VALUES ('first chat');
INSERT INTO messages (chat_id, body) SELECT 1, md5(random()::text) FROM generate_series(1, 1000000);Если пользователь захочет удалить чат, то ему придется подождать какое-то время, т.к. из таблицы необходимо удалить и чат, и все связанные с ним сообщения. Помимо этого, нужно удалить значения из индексов.
DELETE FROM chats WHERE id = 1; -- 6.1 секЕсли ON DELETE CASCADE заменить на ON DELETE SET NULL, то запрос по-прежнему будет выполняться долго, т.к. необходимо обновить все связанные строки, заменив chat_id на NULL.
На помощь приходит мягкое удаление. Давайте добавим в таблицу chats колонку deleted_at. По умолчанию, это поле будет равно NULL (чат активен).
ALTER TABLE chats ADD COLUMN deleted_at timestamp;Когда пользователь будет удалять чат, в колонку deleted_at необходимо установить текущую дату.
UPDATE chats SET deleted_at = now() WHERE id = 1; -- 3.5 мсТеперь ко всем запросам (SELECT, UPDATE, DELETE), которые будут обращаться к таблице с активными чатами, необходимо добавить условие deleted_at IS NULL:
SELECT * FROM chats WHERE deleted_at IS NULL;
UPDATE chats SET name = 'my chat' WHERE id = 1 AND deleted_at IS NULL;
DELETE FROM chats WHERE id = 1 AND deleted_at IS NULL;SELECT запрос возвращает пустой результат, т.к. предыдущим запросом мы пометили наш чат как удаленный. UPDATE запрос не изменит удаленный чат, т.к. у него поле deleted_at не равно NULL. То же самое касается и DELETE запроса.
Т.к. колонка deleted_at по умолчанию равна NULL, то новые чаты всегда будут видны пользователю.
INSERT INTO chats (name) VALUES ('second chat');
SELECT * FROM chats WHERE deleted_at IS NULL; id | name | deleted_at
----+-------------+------------
2 | second chat |
(1 row)Один из главных недостатков мягкого удаления состоит в том, что необходимо везде использовать условие deleted_at IS NULL. Если в каком-то запросе будет пропущено это условие, то пользователь увидит удаленные данные, что неприемлемо, особенно, если удаленные строки содержат чувствительные данные (напр, номер банковской карты).
Создание представления запроса
Чтобы не использовать во всех запросах одно и тоже условие можно создать представление запроса (VIEW):
CREATE VIEW available_chats AS SELECT * FROM chats WHERE deleted_at IS NULL;Теперь вместо использования таблицы chats будем обращаться к «таблице» available_chats. Следующие 2 запроса идентичны:
SELECT * FROM available_chats;
SELECT * FROM chats WHERE deleted_at IS NULL;«Таблица» available_chats нигде не хранится. Каждый раз, когда идет к ней обращение, выполняется запрос к оригинальной таблице chats с условием deleted_at IS NULL.
Помимо SELECT, можно выполнять и другие операции:
INSERT INTO available_chats (name) VALUES ('third chat'); -- INSERT 0 1
UPDATE available_chats SET name = 'another chat' WHERE name = 'third chat'; -- UPDATE 1
DELETE FROM available_chats WHERE name = 'another chat'; -- DELETE 1Однако, обращаясь к представлению запроса available_chats, нельзя обновить/удалить чат, который не соответствует условию deleted_at IS NULL (если чат удален).
UPDATE available_chats SET name = 'another chat' WHERE id = 1; -- UPDATE 0
DELETE FROM available_chats WHERE id = 1; -- DELETE 0До сих пор мы использовали представление available_chats для активных чатов, однако, для него лучше использовать название chats, а саму таблицу переименовать, например, в chats_all. В этом случае не нужно менять в уже существующих запросах название таблицы chats на available_chats.
ALTER TABLE chats RENAME TO chats_all;
ALTER VIEW available_chats RENAME TO chats;Давайте еще добавим представление запроса для удаленных чатов:
CREATE VIEW chats_deleted AS SELECT * FROM chats_all WHERE deleted_at IS NOT NULL;В итоге у нас есть:
- таблица chats_all со всеми чатами.
- представление запроса chats с активными чатами.
- представление запроса chats_deleted с удаленными чатами.
Создание триггера
При удалении чата из «таблицы» chats (активные чаты) необходимо вместо физического удаления (DELETE) выполнить мягкое удаление (UPDATE), установив в поле deleted_at текущую метку времени. При удалении чата из «таблицы» chats_deleted должна быть выполнена операция DELETE.
Логику мягкого удаления реализуем через триггер:
CREATE FUNCTION soft_delete() RETURNS trigger AS $$
DECLARE
fullTableName text := quote_ident(TG_TABLE_SCHEMA) || '.' || quote_ident(TG_TABLE_NAME);
BEGIN
EXECUTE 'UPDATE ' || fullTableName || ' SET deleted_at = now() WHERE id = $1' USING OLD.id;
RETURN OLD;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER soft_delete
INSTEAD OF DELETE ON chats
FOR EACH ROW
EXECUTE FUNCTION soft_delete();Здесь логика мягкого удаления вынесена в отдельную триггерную функцию soft_delete, которую можно использовать для разных таблиц. В ней мы определяем переменную fullTableName с названием схемы и таблицы, для которой сработал триггер (в нашем случае это схема public и таблица chats). Эти названия мы берем из переменных TG_TABLE_SCHEMA и TG_TABLE_NAME. Функция quote_ident оборачивает строку в двойные кавычки, если это необходимо (напр, когда название таблицы имеет вид camelCaseTableName).
SELECT quote_ident('table_name'); -- table_name
SELECT quote_ident('tableName'); -- "tableName"При помощи оператора || производится конкатенация строк.
В теле триггерной функции soft_delete мы выполняем запрос на обновление колонки deleted_at, устанавливая текущую метку времени для той строки, для которой сработал триггер.
Триггерная функция возвращает OLD, тем самым сообщая, что он успешно выполнил операцию. Затронутая строка будет считаться обработанной (DELETE 1). После этого будут вызываться последующие триггеры (при их наличии). Если бы триггерная функция возвращала NULL (можно встретить в некоторых реализациях), то затронутая строка НЕ считалась бы обработанной (DELETE 0), не смотря на то, что операция UPDATE также была бы выполнена. После этого последующие триггеры не вызывались бы (при их наличии).
-- Используется триггерная функция, которая возвращает OLD
DELETE FROM chats WHERE id = 1; -- DELETE 1
-- Используется триггерная функция, которая возвращает NULL
DELETE FROM chats WHERE id = 1; -- DELETE 0Поэтому важно, чтобы наш триггерная функция в конце возвращала OLD.
Триггер soft_delete (не обязательно, чтобы название функции и триггера совпадало) срабатывает, когда в таблице chats выполняется операция DELETE. В этом случае вместо DELETE происходит вызов функции soft_delete для каждой строки.
Если из таблицы chats удалить 1 млн строк, то для каждой строки будет вызвана функция soft_delete, которая обновит колонку deleted_at. В результате будет вызвано 1 млн запросов UPDATE. Чтобы этого избежать, при удалении можно поставить ограничение на максимальное количество удаляемых строк (напр, 1000).
DELETE FROM chats WHERE ctid = ANY(ARRAY(SELECT ctid FROM chats LIMIT 1000)); -- DELETE 1000В итоге при удалении строк из «таблицы» chats мы делаем мягкое удаление, устанавливая в колонку deleted_at текущую метку времени.
DELETE FROM chats WHERE name = 'second chat'; -- DELETE 1
SELECT * FROM chats WHERE name = 'second chat'; -- 0 rows
SELECT * FROM chats_deleted WHERE name = 'second chat'; -- 1 rowПри удалении строк из «таблицы» chats_deleted мы делаем физическое удаление:
DELETE FROM chats_deleted WHERE name = 'second chat'; -- DELETE 1
SELECT * FROM chats_deleted WHERE name = 'second chat'; -- 0 rows
SELECT * FROM chats_all WHERE name = 'second chat'; -- 0 rowsУдаление чувствительных данных
В некоторых случаях, когда пользователь удаляет строку, необходимо удалять некоторые чувствительные данные в ней (например, номер банковской карты или id, по которому можно повторно списывать рекуррентные платежи).
Предположим, что у нас есть таблица users с полем credit_card_number. Когда пользователь удаляет свой аккаунт, необходимо удалять номер его кредитной карты (при восстановлении аккаунта можно снова запросить номер карты).
Для этого необходимо в триггере в колонках с чувствительными данными (в нашем случае в колонке credit_card_number) установить NULL:
CREATE FUNCTION soft_delete_user() RETURNS trigger AS $$
DECLARE
fullTableName text := quote_ident(TG_TABLE_SCHEMA) || '.' || quote_ident(TG_TABLE_NAME);
BEGIN
EXECUTE 'UPDATE ' || fullTableName || ' SET credit_card_number = NULL, deleted_at = now() WHERE id = $1' USING OLD.id;
RETURN OLD;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER soft_delete
INSTEAD OF DELETE ON users
FOR EACH ROW
EXECUTE FUNCTION soft_delete_user();Изменение ограничений уникальности
Давайте для примера создадим новую таблицу companies с ограничением уникальности для колонки name. Чтобы не усложнять пример, не будем создавать представления запросов и триггер.
CREATE TABLE companies (
id int,
name text,
deleted_at timestamp,
CONSTRAINT companies_name_key UNIQUE (name)
);Что будет, если мы добавим новую компанию, «мягко» удалим ее и попытаемся создать новую компанию с тем же названием?
INSERT INTO companies (id, name) VALUES (1, 'my company');
UPDATE companies SET deleted_at = now() WHERE id = 1;
INSERT INTO companies (id, name) VALUES (2, 'my company');
-- ERROR: duplicate key value violates unique constraint "companies_name_key"
-- DETAIL: Key (name)=(my company) already exists.Ограничение уникальности выбрасывает ошибку, т.к. компания все еще существует в таблице. Это можно исправить, сделав уникальными только названия активных компаний. К сожалению, мы не можем реализовать это, используя ограничение уникальности, но существуют другие варианты.
Вариант 1. Создать частичный индекс, который будет проверять на уникальность.
CREATE UNIQUE INDEX companies_name_key ON companies (name) WHERE deleted_at IS NULL;Вариант 2. Создать ограничение-исключение, которое будет проверять колонку name на равенство (равносильно ограничению UNIQUE, однако обычное ограничение уникальности будет работать быстрее).
ALTER TABLE companies ADD CONSTRAINT companies_name_key EXCLUDE (name WITH =) WHERE deleted_at IS NULL;Воспользуемся первым вариантом:
ALTER TABLE companies DROP CONSTRAINT companies_name_key;
CREATE UNIQUE INDEX companies_name_key ON companies (name) WHERE deleted_at IS NULL;Теперь попробуем дважды добавить компанию с тем же названием:
INSERT INTO companies (id, name) VALUES (2, 'my company'); -- INSERT 0 1
INSERT INTO companies (id, name) VALUES (3, 'my company');
-- ERROR: duplicate key value violates unique constraint "companies_name_key"
-- DETAIL: Key (name)=(my company) already exists.В первый раз компания добавилась успешно, т.к. не существует ни одной активной компании с таким же названием. Во второй раз произошла ошибка, т.к. такая компания уже существует.
Что будет, если мы попытаемся восстановить удаленную компанию с тем же названием?
UPDATE companies SET deleted_at = NULL WHERE id = 1;
-- ERROR: duplicate key value violates unique constraint "companies_name_key"
-- DETAIL: Key (name)=(my company) already exists.Произошла ошибка, т.к. мы уже создали другую компанию с тем же названием. В качестве решения, если возникает ошибка, то можно у пользователя запросить новое название компании.
UPDATE companies SET name = 'another company', deleted_at = NULL WHERE id = 1;Запрос выполнен успешно, т.к. компаний с новым названием не существует.
Важно отметить, что если бы мы реализовали ограничение уникальности по 2 колонкам (напр, deleted и name), то в этом случае при попытке мягкого удаления компании ограничение уникальности выбросило бы ошибку в случае, если компания с таким названием уже существует среди удаленных. Для простоты можно привести аналогию. Представьте, что в вашем компьютере чистая корзина. Вы удаляете файл 1.jpg, затем, спустя время, удаляете еще один файл с тем же названием. В этот момент корзина выбрасывает ошибку о том, что нельзя удалить такой файл, т.к. среди удаленных файл с таким названием уже существует. Это было бы странно. Поэтому мы воспользовались частичным индексом, который проверяет на уникальность только активные компании.
Изменение индексов
Напомню, что для обращения к таблице с активными чатами мы используем представление запроса chats, а для обращения к удаленным чатам – chats_deleted. Оба этих представления обращаются к физической таблице chats_all.
-- Выполняя запросы
SELECT * FROM chats;
SELECT * FROM chats_deleted;
-- Фактически выполняются
SELECT * FROM chats_all WHERE deleted_at IS NULL;
SELECT * FROM chats_all WHERE deleted_at IS NOT NULL;Индексы, которые используются для ускорения поиска по активным и удаленным чатам должны использовать это условие. Иначе, вместо того, чтобы сразу вернуть результаты, используя индекс, отдельным этапом будет произведена фильтрация результатов в соотвествии с условием deleted_at IS NULL или deleted_at IS NOT NULL.
CREATE TABLE chats (id int, name text, deleted_at timestamp);
INSERT INTO chats (id, name, deleted_at) SELECT i, md5(random()::text), (CASE WHEN random() < 0.1 THEN now() ELSE NULL END) FROM generate_series(1, 1000000) AS i;
CREATE INDEX chats_name_idx ON chats USING gin (name gin_trgm_ops);
EXPLAIN ANALYZE SELECT * FROM chats WHERE name ILIKE '%abc%' AND deleted_at IS NULL; Bitmap Heap Scan on chats (cost=24.77..393.40 rows=90 width=45) (actual time=2.850..2237.357 rows=6593 loops=1)
Recheck Cond: (name ~~* '%abc%'::text)
Filter: (deleted_at IS NULL)
Rows Removed by Filter: 738
Heap Blocks: exact=4910
-> Bitmap Index Scan on chats_name_idx (cost=0.00..24.75 rows=100 width=0) (actual time=1.940..1.947 rows=7331 loops=1)
Index Cond: (name ~~* '%abc%'::text)
Planning Time: 0.367 ms
Execution Time: 2286.334 msКак вы видите, в начале были найдены 7331 чатов (активные и удаленные), которые содержат поисковый запрос «abc», а затем отфильтровано 738 удаленных чатов, которые не соответствуют условию deleted_at IS NULL. Поиск занял 2.2 сек.
Теперь давайте сделаем частичный индекс, добавив к нему условие deleted_at IS NULL.
DROP INDEX chats_name_idx;
CREATE INDEX chats_name_idx ON chats USING gin (name gin_trgm_ops) WHERE deleted_at IS NULL;
EXPLAIN ANALYZE SELECT * FROM chats WHERE name ILIKE '%abc%' AND deleted_at IS NULL; Bitmap Heap Scan on chats (cost=24.70..357.97 rows=90 width=45) (actual time=6.397..528.855 rows=6593 loops=1)
Recheck Cond: ((name ~~* '%abc%'::text) AND (deleted_at IS NULL))
Heap Blocks: exact=4574
-> Bitmap Index Scan on chats_name_idx (cost=0.00..24.67 rows=90 width=0) (actual time=5.627..5.634 rows=6593 loops=1)
Index Cond: (name ~~* '%abc%'::text)
Planning Time: 4.438 ms
Execution Time: 579.336 msНа этот раз мы сразу выбрали 6593 активных чата, которые содержат поисковый запрос «abc», поэтому поиск выполнился в 4 раза быстрее (за 579 мс).
Если нужно сортировать по колонке deleted_at, то она должна быть включена в индекс. Например, давайте получим первые 100 удаленных чатов с id > 1000, отсортированный по deleted_at по убыванию, чтобы недавно удаленные чаты отображались сверху.
DROP INDEX chats_name_idx;
CREATE INDEX chats_deleted_at_id_idx ON chats (deleted_at, id) WHERE deleted_at IS NOT NULL;
EXPLAIN ANALYZE SELECT * FROM chats WHERE id > 1000 AND deleted_at IS NOT NULL ORDER BY deleted_at DESC LIMIT 100; Limit (cost=0.42..18.64 rows=100 width=45) (actual time=0.110..3.289 rows=100 loops=1)
-> Index Scan Backward using chats_deleted_at_id_idx on chats (cost=0.42..18616.35 rows=102138 width=45) (actual time=0.087..1.170 rows=100 loops=1)
Index Cond: (id > 1000)
Planning Time: 0.092 ms
Execution Time: 4.330 msПервые 100 удаленных чатов, которые удовлетворяют нашему условию, были выбраны сразу. Поиск выполнился за 4 мс.
Автоматическое удаление
Строки, помеченные как удаленные, лучше всего со временем удалять из таблицы, чтобы они не занимали много места на диске. Например, можно дать пользователю 30 дней на восстановление удаленных строк. По истечению этого периода строки будут удалены из таблицы навсегда.
Скорее всего, процент удаляемых строк из таблицы будет небольшим, поэтому можно воспользоваться операцией DELETE. Если необходимо удалить бОльшую часть строк, то можно воспользоваться способами, описанными тут и тут.
Процесс удаления лучше всего запускать в нерабочее время, например, по ночам раз в сутки (или в выходные раз в неделю). Удаление большого количества строк можно разбивать на куски (напр, по 10 тыс).
DELETE FROM chats WHERE ctid = ANY(ARRAY(SELECT ctid FROM chats LIMIT 10000));Поле ctid уникально для каждой записи в таблице и обозначает местоположение строки (tuple) в PostgreSQL. Использование ctid в условии, вместо id позволяет существенно ускорить удаление.
-- В обоих тестах используется следующая таблица с 1 млн строк
CREATE TABLE messages (id serial primary key, body text);
INSERT INTO messages (body) SELECT md5(random()::text) FROM generate_series(1, 1000000);
-- При использовании id удаление занимает 6.4 сек.
DELETE FROM messages WHERE id = ANY(ARRAY(SELECT id FROM messages));
-- При использовании ctid удаление занимает 2.9 сек (быстрее в 2 раза).
DELETE FROM messages WHERE ctid = ANY(ARRAY(SELECT ctid FROM messages));Количество удаляемых строк за 1 итерацию сильно зависит от многих параметров (напр, какие индексы используются в таблице, сколько строк ссылается на удаляемую и т.д.). Как вариант, в начале можно определиться за какое время должна выполняться 1 итерация, а затем эмпирическим путем подобрать то количество удаляемых строк за 1 итерацию, которое занимает это время. Можно начать с 10 тыс, постепенно увеличивая это число (50, 100 тыс, 1 млн).
Если требуется удалить строку, на которую ссылаются миллионы других строк, то можно в начале удалить дочерние строки в несколько итераций, а затем удалить родительскую строку.
Шпаргалка
-- Создание таблицы и представлений запросов для активных и удаленных чатов
CREATE TABLE chats_all (id serial primary key, name text, deleted_at timestamp);
CREATE VIEW chats AS SELECT * FROM chats_all WHERE deleted_at IS NULL;
CREATE VIEW chats_deleted AS SELECT * FROM chats_all WHERE deleted_at IS NOT NULL;
-- Создание триггера
CREATE FUNCTION soft_delete() RETURNS trigger AS $$
DECLARE
fullTableName text := quote_ident(TG_TABLE_SCHEMA) || '.' || quote_ident(TG_TABLE_NAME);
BEGIN
EXECUTE 'UPDATE ' || fullTableName || ' SET deleted_at = now() WHERE id = $1' USING OLD.id;
RETURN OLD;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER soft_delete
INSTEAD OF DELETE ON chats
FOR EACH ROW
EXECUTE FUNCTION soft_delete();
-- Мягкое удаление
DELETE FROM chats WHERE id = 1;
-- Физическое удаление
DELETE FROM chats_deleted WHERE id = 1;
-- Ограничение уникальности
CREATE UNIQUE INDEX chats_all_name_key ON chats_all (name) WHERE deleted_at IS NULL;
-- Запрашивать у пользователя новое название, если возникает ошибка при восстановлении
UPDATE chats SET name = 'another chat', deleted_at = NULL WHERE id = 1;
-- Использовать частичный индекс (включить deleted_at, если требуется сортировка по этой колонке)
CREATE INDEX chats_name_idx ON chats USING gin (name gin_trgm_ops) WHERE deleted_at IS NULL;
CREATE INDEX chats_deleted_at_id_idx ON chats (deleted_at, id) WHERE deleted_at IS NOT NULL;
-- Автоматическое удаление строк (в нерабочее время: по ночам, в выходные)
DELETE FROM chats WHERE ctid = ANY(ARRAY(SELECT ctid FROM chats LIMIT 10000));