Совет дня №26

Продолжение прошлой заметки про удаление.

Решение – перенос! Данные нужно не помечать на удаление, а переносить между таблицами. В этом случае главная таблица не захламляется, а удаленные строки не висят грузом. Для начала решим задачу в лоб. Представим, есть таблица товаров:

create table goods (
 id serial primary key,
 title text not null,
 created_at timestamptz not null default current_timestamp
);

Вставим в нее много записей:

insert into goods(title)
select
 format('good_%s', x)
from
 generate_series(1, 10000) as seq(x);

Подготовим таблицу удаленных записей. Ее структура такая же, поэтому используем выражение like <table>. Индексы и ограничения скопированы не будут.

create table goods_deleted (like goods);

Далее мы решили, что товары с кодами с 555 по 666 почему-то плохие, и нужно их удалить. Разработчики делают так: сперва читают данные в приложение:

select * from goods
where id between 555 and 666;

Удаляют их:

delete from goods
where id between 555 and 666;

Вставляют в таблицу goods_deleted строки, которые висят в приложении:

insert into goods_deleted ...;

В обратную сторону — то же самое: прочитали, удалили, вставили. Решение нормальное и встречается часто — и в нем все плохо.

Во-первых, по какой-то причине мы гоняем строки в приложение и обратно, хотя нужды в этом никакой. Postgres прекрасно перенесет их между таблицами, не отправляя клиенту. Для этого выполним запрос:

with deleted as (
 delete from goods
 where id between 555 and 666
 returning *
)
insert into goods_deleted
select * from deleted;

Если DELETE имеет на конце returning, он не отличается от SELECT (разве что с побочным эффектом). В выборке deleted окажутся удаленные строки. Далее мы вставляем их в goods_deleted. Оба запроса протекают в неявной транзакции, снимок данных один и тот же.

Теперь в goods не окажется товаров с айдишками 555…666, зато они будут в goods_deleted.

Теперь в обратную сторону: удалить из goods_deleted и вставить в goods:

with restored as (
 delete from goods_deleted
 where id between 555 and 666
 returning *
)
insert into goods
select * from restored;

Один запрос, атомарность, нулевая передача клиенту.

Теперь таблицы. Выше мы клонировали таблицу goods и назвали ее goods_deleted. И что, делать это для каждой таблицы? users_deleted, orders_deleted, photos_deleted, profile_deleleted…?

А во-вторых, миграции. Если таблица goods изменится, перенос отвалится, потому что состав колонок будет другим. Придётся явно прописывать, что на что маппить.

Поэтому вместо goods_deleted, users_deleted и так далее заведем таблицу any_deleted:

create table any_deleted (
 entity text not null,
 pk integer not null,
 data jsonb not null,
 created_at timestamptz not null default current_timestamp
);

Поле entity означает сущность (user, profile и другие), pk — ее первичный ключ. В идеале это UUID, чтобы быть уникальным в разрезе всех сущностей. В поле data хранится строка в формате JSON. Вот как выполнить перенос:

with deleted as (
 delete from goods
 where id between 555 and 666
 returning *
)
insert into any_deleted
select
 'goods',
 deleted.id,
 to_jsonb(deleted)
from deleted;

Посмотрим, что оказалось в удаленной таблице:

┌─[ RECORD 1 ]─────────────────────────────────
│ entity  │ goods
│ pk      │ 555
│ data    │ {"id": 555, "title": "good_555",
│ created_at │ 2026-01-24 14:57:32.595317+03
├─[ RECORD 2 ]─────────────────────────────────
│ entity  │ goods
│ pk      │ 556
│ data    │ {"id": 556, "title": "good_556",
│ created_at │ 2026-01-24 14:57:32.595317+03
├─[ RECORD 3 ]─────────────────────────────────
│ entity  │ goods
│ pk      │ 557
│ data    │ {"id": 557, "title": "good_557",
│ created_at │ 2026-01-24 14:57:32.595317+03

Видим, что все однородно: можно удалять разные сущности, конфликтов не будет.

Чтобы перенести обратно, выполним такой запрос:

with deleted as (
 delete from any_deleted
 where
         entity = 'goods'
     and pk between 555 and 666
 returning *
)
insert into goods
select
 rec.id,
 rec.title,
 rec.created_at
from
 deleted,
 jsonb_to_record(deleted.data) as rec(
     id integer,
     title text,
     created_at timestamptz
 );

Функция jsonb_to_record приводит JSON-объект к записи. Даже если в исходной таблице добавились колонки, после распаковки они будут null.

В итоге у нас одна таблица удаленных сущностей. Она бесконечно растет вниз, устойчива к миграциям. Активные таблицы не захламляются. Чудо!

Что бы еще добавить? Пожалуй, у таблицы any_deleted могут быть поля done_by (кто удалил), reason (причина: ban, conflict, legacy), комментарий (бывает нужен), срок хранения (для регулятора).

Минус переноса в том, что нужно ослабить внешние ключи. Обычно против этого возражают, но с ростом базы это неминуемо. Если сущность удаляется (подлежит переносу), отключите foreign key constraint в тех таблицах, которые на нее ссылаются. Для джоинов используйте inner join, чтобы отсекать пустые ссылки. Записи, которые ссылаются вникуда, можно найти анти-соединением и либо удалить физически, либо тоже перенести. Часть этой работы можно поставить на крон (см. pg_cron в прошлых советах).