Секция SQL. Теория.
Вопрос: TRUNCATE vs DELETE (разобрал для MSSQL, MYSQL, PostreSQL, ORACLE)
Тип БД:
Первое, что стоит учесть — TRUNCATE и DELETE это команды реляционной модели. В NoSQL и колоночных БД свои механизмы удаления, поэтому там это сравнение неприменимо =)
Тип модели хранилища:
В системах типа DWH, Data Lake, OLAP это сравнение тож не актуально. В DWH данные грузятся батчами, точечный DELETE — редкая и тяжелая хрень. Чаще нужна полная перезагрузка партиции или витрины через TRUNCATE/DROP PARTITION. Короче DELETE в OLAP — это лишнее.
А вот OLTP — это уже та среда, для которой этот вопрос предназначен, тк выбор между командами критичен и зависит от цели.
🔤🔤🔤🔤🔤🔤🔤🔤
🔤🔤
🩸🩸🩸🩸🩸🩸
в тексте есть сноски (*N). Пояснение комменте
1️⃣ Транзакционность
DELETE относится к DML и полностью транзакционен во всех СУБД: поддерживает ROLLBACK, участвует в MVCC (1️⃣), генерирует UNDO/WAL(2️⃣).
TRUNCATE - это DDL: В PostgreSQL и MSSQL он транзакционен и откатывается, а в MySQL и Oracle — выполняет авто-commit и отката нет.
2️⃣ Механизм удаления
DELETE работает построчно, логирует каждое изменение и обновляет индексы — сложность O(N) (3️⃣).
TRUNCATE деаллоцирует(4️⃣) страницы целиком и пишет в лог только факт операции — O(1) (5️⃣).
3️⃣ WHERE и партиционирование
DELETE поддерживает WHERE везде.
TRUNCATE работает без условий, только с таблицей целиком или партицией. TRUNCATE PARTITION есть в Oracle, MySQL и PostgreSQL. В MSSQL используют partition switching (6️⃣).
4️⃣ Триггеры
DELETE активирует row-level триггеры (7️⃣) во всех СУБД.
TRUNCATE триггеры не вызывает — кроме PostgreSQL, где TRUNCATE-триггеры поддерживаются явно.
5️⃣ Внешние ключи
DELETE проверяет ссылочную целостность построчно везде.
TRUNCATE не выполнится, если на таблицу ссылается FK. Исключение — Oracle начиная с 12c, где появился CASCADE.
6️⃣ Блокировки
DELETE накладывает row-level locks (8️⃣), что при большом объёме может порождать долгие blocking chains.
TRUNCATE берёт table-level exclusive lock (9️⃣) — в PostgreSQL это AccessExclusiveLock, блокирующий даже SELECT. Но выполняется мгновенно.
7️⃣ WAL(2️⃣) и репликация
DELETE генерирует полный объём WAL-записей, нагружает standby(🔟) и архивные логи.
TRUNCATE пишет в лог минимум. В MSSQL операция считается minimally logged даже в Full Recovery Model(1️⃣1️⃣).
8️⃣ Инкрементальные счётчики
DELETE счётчик не сбрасывает.
TRUNCATE: MySQL сбрасывает AUTO_INCREMENT в 1, MSSQL сбрасывает IDENTITY в seed-значение(1️⃣2️⃣), PostgreSQL сбрасывает sequence только при явном указании RESTART IDENTITY. В Oracle SEQUENCE — отдельный объект, TRUNCATE его не трогает (только ALTER SEQUENCE вручную).
9️⃣ High Water Mark (Oracle) (1️⃣3️⃣)
DELETE не снижает HWM — full scan продолжает читать весь ранее занятый сегмент даже после удаления данных.
TRUNCATE сбрасывает HWM. Есть два режима: DROP STORAGE возвращает экстенты(1️⃣4️⃣) обратно в tablespace, REUSE STORAGE оставляет структуру, но сбрасывает метку. В PostgreSQL используется аналог — VACUUM, в MSSQL — rebuild операции, но термин HWM там не используется.