Сравнение TRUNCATE и DELETE

DELETE и TRUNCATE

Оба оператора удаляют данные, но имеют разную семантику. Поведение блокировок, журналирования, identity, триггеров и rollback зависит от СУБД.

DELETE

DELETE FROM orders
WHERE state = 'CANCELLED'
  AND created_at < TIMESTAMP '2024-01-01 00:00:00';

• удаляет выбранные строки;
• поддерживает WHERE;
• обычно вызывает row-level delete triggers;
• проверяет ссылочную целостность;
• большие удаления создают нагрузку на журнал и могут долго удерживать locks.

TRUNCATE

TRUNCATE TABLE staging_orders;

• очищает всю таблицу;
• обычно работает на уровне страниц/сегментов и быстрее полного DELETE;
• часто берет более сильную блокировку;
• имеет особые правила для внешних ключей;
• может сбрасывать счетчик identity в зависимости от команды и СУБД;
• не является буквально «DROP + CREATE»: объект, права и определение таблицы обычно сохраняются.

Транзакционность

Нельзя запоминать общее правило «TRUNCATE нельзя откатить». В одних СУБД он транзакционен, в других DDL вызывает неявный commit или имеет ограничения. Это проверяется для конкретного движка и версии.

Большая очистка

Для production возможны:

• удаление небольшими пакетами;
• partition drop/detach по сроку хранения;
• архивирование;
• окно обслуживания;
• контроль replication lag, журнала и vacuum/cleanup.

Ловушки

• Огромный DELETE одной транзакцией может заполнить журнал и заблокировать соседние операции.
• TRUNCATE ... CASCADE способен затронуть связанные таблицы.
• Удаление строк не всегда немедленно уменьшает файл на диске.
• После массового изменения могут потребоваться обслуживание и обновление статистики.

Связано: Операции SQL по группам, Транзакции принцип ACID, Ограничения, constraints.

Вопросы

Можно ли добавить WHERE к TRUNCATE? Нет.

TRUNCATE всегда неоткатываемый? Нет, это зависит от СУБД.

TRUNCATE равен DROP + CREATE? Нет, обычно таблица как объект сохраняется.

Как удалять исторические данные регулярно? Часто удобнее partitioning и удаление целых старых partition.