Table swap вместо DELETE: как мы обработали 1,4 млрд строк за 2 часа

Есть класс задач, где нужно удалить большинство строк очень большой таблицы: свертка исторических данных, очистка разросшихся служебных журналов, вынос архива. И есть интуитивный способ это сделать - DELETE ... WHERE. На маленьких объёмах он работает. На сотнях миллионов строк он превращается в источник страданий, у которых есть точное имя: журнал транзакций.

Дисклеймер. Речь об операциях уровня СУБД. Мы применяем их в проектах свертки только после доказательства безопасности: полные репетиции на копии, побитовые сверки остатков “до/после”, точки отката и приёмка заказчиком. Лицензионная политика вендора платформы такие операции не приветствует - понимайте риски и не повторяйте на продуктиве без полного контура контроля. Для типовых объёмов используйте штатные механизмы - этого достаточно в большинстве случаев.

Почему DELETE так плох на больших объёмах

DELETE - построчная операция с полным журналированием. На каждую удаляемую строку СУБД пишет запись в журнал транзакций, обслуживает индексы и удерживает блокировки. Отсюда три беды:

1. Журнал транзакций раздувается до размеров, сопоставимых с самой таблицей. На 2,37 млрд строк это отдельная вечность и отдельные терабайты.
2. Эскалация блокировок: длинный DELETE довольно быстро эскалирует до блокировки всей таблицы - привет, пользователи.
3. Индексы после массового удаления остаются фрагментированным решетом - их всё равно перестраивать, это ещё часы.

Классический паллиатив - “удалять порциями по N тысяч строк” - уменьшает беды 1-2, но растягивает процесс на дни и оставляет беду 3 в полный рост.

Идея table swap: не удалять мёртвое, а спасать живое

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

-- 1. Новая таблица: переносим только выживших
SELECT s.*
INTO   dbo._DocTable_new
FROM   dbo._DocTable s
JOIN   dbo._ProtectMarks p ON p.Ref = s._IDRRef;

-- 2. Индексы и ограничения - с нуля, на уже итоговых данных
--    (быстрее, чем поддерживать их во время вставки, и без фрагментации)

-- 3. Подмена имён в одной короткой транзакции
BEGIN TRAN;
  EXEC sp_rename 'dbo._DocTable',     '_DocTable_old';
  EXEC sp_rename 'dbo._DocTable_new', '_DocTable';
COMMIT;
-- _DocTable_old живёт до конца окна как локальная точка отката

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

Замеры с боевого проекта

Цифры из свертки базы 2,3 ТБ (46 млн документов, таблицы по 300-430 млн строк):

Метрика	Значение
Ускорение против DELETE	в 5-9 раз (по замерам на репетициях)
Обработано за ночное окно	~1,4 млрд строк за ~2 часа
Параллельность	4 потока (раскладка таблиц - по замерам, чтобы финишировали одновременно)
Фрагментация индексов после	0 - они только что построены

Бонус, который часто недооценивают: после swap вы получаете свежие нефрагментированные индексы бесплатно - этап “перестроить индексы после чистки” исчезает из плана.

Грабли и правила безопасности

Table swap - мощный и опасный инструмент. Правила, выстраданные репетициями:

1. Точная копия структуры. Все индексы, ограничения и особенности таблиц платформы должны быть перенесены на новую таблицу в точности. Одно забытое ограничение - и живая база начинает вести себя странно через неделю.
2. Только на замороженном контуре. Регламентные задания, обмены, интеграции, сеансы - всё выключено и проверено, что выключено. Любой “писатель”, доживший до swap, допишет данные в таблицу, которая через минуту станет _old.
3. Критерий “живой строки” фиксируется заранее и материализуется как данные (у нас - таблица меток защиты, построенная транзитивным замыканием по ссылкам). Вычислять критерий “на лету” в момент переноса - способ получить невоспроизводимый результат.
4. Пред-чеки перед необратимым шагом. Автоматическая сверка: количество выживших соответствует ожиданию, все кандидаты на удаление вне защищённого множества. Расхождение - стоп всего процесса, а не предупреждение в лог.
5. _old-таблицы живут до конца окна как мгновенная точка отката, и удаляются последними.
6. Репетиции на копии прода - не одна. У нас их было 7+, и каждая делала пайплайн быстрее и жёстче: каскад ускорился с 10 часов на первой репетиции до 2 часов на финальной.

Когда table swap НЕ нужен

• Удаляется меньшинство строк - обычный порционный DELETE проще и безопаснее.
• Удаляется вся таблица целиком (служебный кэш, очередь, лог) - там достаточно TRUNCATE, он проще и быстрее swap: разбор с замерами - в статье “SQL-версия обрезки базы 1С”.
• Таблица маленькая (миллионы строк, не сотни миллионов) - штатные механизмы справятся.
• Нет возможности заморозить контур - swap в живой базе это авария по расписанию.
• Нет копии для репетиций - значит, вы планируете эксперимент на проде. Не надо.

Итог

DELETE оптимизирован для удаления меньшинства, и заставлять его удалять большинство - это платить построчным журналированием за каждую из миллиарда строк. Table swap меняет знак задачи: спасаем живое, а мёртвое отбрасываем целиком. Плата за скорость - дисциплина: заморозка, пред-чеки, репетиции, точки отката.

Полный контекст проекта, где этот паттерн отработал на 1,4 млрд строк - в методике на Инфостарте: “Свертка базы 2,3 ТБ за одно ночное окно”. А если ваша база доросла до объёмов, где типовая свертка уже не влезает в окно, - посмотрите, как мы делаем это как услугу: с калькулятором стоимости и бесплатным аудитом на входе.