re:re:actable
Урок 053 минуты чтения

Чем DELETE отличается от TRUNCATE

На этой странице
Кратко

DELETE удаляет строки по одной (можно с WHERE), для каждой создаёт запись в WAL, вызывает построчные триггеры и проверяет внешние ключи; место освобождается позже через VACUUM. TRUNCATE мгновенно очищает всю таблицу, заменяя её файлы новыми пустыми: без WHERE, без построчных триггеров (есть только ON TRUNCATE), место возвращается сразу. В PostgreSQL TRUNCATE транзакционный — его можно откатить ROLLBACK, но он берёт ACCESS EXCLUSIVE блокировку (никто не может даже читать таблицу до конца транзакции). Если на таблицу ссылаются FK, нужен TRUNCATE … CASCADE (очистит и ссылающиеся таблицы) — опасная операция. RESTART IDENTITY сбрасывает счётчики identity/sequence.

Вопрос

Чем отличаются DELETE и TRUNCATE?

Сравнение

DELETETRUNCATE
WHEREДаНет — только вся таблица
Скорость на больших таблицахМедленно, пропорционально числу строкМгновенно
Построчные триггерыСрабатываютНет (только ON TRUNCATE)
Освобождение местаПосле VACUUMСразу
Откат в транзакции (PostgreSQL)ДаДа
БлокировкаROW EXCLUSIVE — чтение и запись других строк не блокируютсяACCESS EXCLUSIVE — блокирует всё
Таблица, на которую ссылаются FKПроверяет FK построчноОшибка без CASCADE
Сброс identityНетRESTART IDENTITY
RETURNINGДаНет

Примеры

DELETE FROM sessions WHERE expires_at < now();   -- выборочно
 
TRUNCATE TABLE import_staging;                   -- очистить промежуточную таблицу
TRUNCATE TABLE import_staging RESTART IDENTITY;  -- и сбросить счётчик id
TRUNCATE orders, order_items;                    -- несколько таблиц сразу

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

BEGIN;
TRUNCATE import_staging;
-- ... загрузили новые данные, что-то пошло не так
ROLLBACK;     -- старые данные на месте

В MySQL (InnoDB) TRUNCATE вызывает неявный COMMIT и не откатывается — частая путаница на собеседованиях.

TRUNCATE и FK

TRUNCATE users;
-- ERROR: cannot truncate a table referenced in a foreign key constraint
 
TRUNCATE users CASCADE;   -- очистит ВСЕ таблицы, ссылающиеся на users (orders, payments...)

Когда что

ЗадачаКоманда
Удалить часть строкDELETE
Очистить staging/temp таблицу перед загрузкойTRUNCATE
Сбросить тестовую БД между тестамиTRUNCATE ... RESTART IDENTITY CASCADE
Удалить старые данные из большой таблицы журналовПартиции + DROP/DETACH
Нужен аудит удалений через триггерыDELETE

Частые ошибки

  • TRUNCATE в проде на таблице, которую активно читают, — все запросы встанут в очередь за блокировкой.
  • CASCADE без понимания, какие таблицы будут очищены.
  • Ожидать срабатывания AFTER DELETE триггеров при TRUNCATE.
  • Думать, что TRUNCATE нельзя откатить в PostgreSQL.

Что важно помнить

  • DELETE — построчно, выборочно, с триггерами.
  • TRUNCATE — вся таблица, мгновенно, с жёсткой блокировкой.
  • В PostgreSQL обе операции транзакционны.

Мини-шпаргалка

  • DELETE FROM t WHERE ....
  • TRUNCATE t [RESTART IDENTITY] [CASCADE].
  • TRUNCATE = ACCESS EXCLUSIVE.

Документация

PostgreSQL: TRUNCATE, PostgreSQL: DELETE.

Дочитали до конца?
Отметьте урок — прогресс сохранится в браузере.

Поиск

Поиск по урокам