SQL: TRUNCATE is not a faster DELETE

TRUNCATE drops and recreates the table's storage. It is far faster, but it cannot be filtered, usually does not fire row triggers, and in some engines cannot be rolled back.

Code

DELETE FROM staging;              -- row by row, logged, triggers fire, WHERE allowed
TRUNCATE TABLE staging;           -- deallocates pages, no WHERE, no row triggers

TRUNCATE staging RESTART IDENTITY CASCADE;   -- also reset sequences, follow FKs

Output

8,000,000 rows:
  DELETE   -> 96 s, 2.1 GB of WAL
  TRUNCATE -> 0.08 s

-- PostgreSQL: transactional, can be rolled back.
-- MySQL/InnoDB and Oracle: implicit commit, cannot be rolled back.
-- TRUNCATE also needs an exclusive lock, so it waits for open readers.

Run this yourself in the Online Java Compiler, spin up a live REST API in the API Sandbox, or practise with Java interview questions.

Published 2026-08-25