Applies toPostgres, MySQL, most SQL databasesRisk levelHigh — production incidents documentedCommon mistakeUsing TRUNCATE when DELETE semantics are required
TRUNCATE is described as a fast DELETE in most introductory SQL material. This description is accurate in one narrow sense and misleading in every other. TRUNCATE and DELETE are not the same operation performed at different speeds. They are different operations with different semantics, different lock behavior, different replication behavior, and different trigger behavior.
The speed difference is a side effect of the semantic difference. Understanding why TRUNCATE is fast tells you exactly when it will break things.
How DELETE works
DELETE removes rows one at a time. For each row, Postgres acquires a row-level lock, checks foreign key constraints on referencing tables, fires any BEFORE DELETE row-level triggers and AFTER DELETE row-level triggers, writes a WAL record marking the row as dead, and allows MVCC snapshots to continue seeing the row until their transaction ends.
This is expensive per row. On a table with ten million rows, DELETE touches each row individually. The dead rows remain physically in the table until VACUUM reclaims them. WAL volume is proportional to the number of rows deleted.
What DELETE gives you in exchange: it fires triggers, it respects row-level locks held by other transactions, it integrates with MVCC so ongoing reads see a consistent view, and it can be rolled back at any point during execution.
How TRUNCATE works
TRUNCATE removes all rows by deallocating the pages that store them. No row-level processing occurs. There are no row-level locks, no per-row WAL records, no MVCC dead tuples. The table pages are simply freed. On a table with ten million rows, TRUNCATE takes the same time as on a table with ten rows.
The cost: TRUNCATE acquires an ACCESS EXCLUSIVE lock on the table — the most restrictive lock level in Postgres. Nothing can read from, write to, or even query the lock status of a table while TRUNCATE holds this lock. DELETE acquires a much weaker ROW EXCLUSIVE lock and allows concurrent reads.
The trigger difference
Row-level BEFORE DELETE and AFTER DELETE triggers do not fire on TRUNCATE. This is not a quirk or an oversight — it is by design, because TRUNCATE does not process rows individually.
If your application logic depends on triggers to maintain derived state — audit tables, cache invalidation, denormalized columns in other tables, soft-delete flags — TRUNCATE silently skips all of it. The downstream state is not updated. The audit table records no event. The cache is not invalidated. This is the production incident pattern: developer uses TRUNCATE expecting DELETE semantics, triggers don't fire, and the application state is now inconsistent in ways that may not be discovered until much later.
Statement-level TRUNCATE triggers do exist and do fire. But you need to have explicitly created them. If your table has only row-level triggers, TRUNCATE does not fire them.
Foreign key cascade behavior
TRUNCATE with CASCADE propagates truncation to tables with foreign key references pointing at the truncated table. TRUNCATE parent_table CASCADE will also truncate every child table that has a foreign key pointing at parent_table.
This differs from DELETE, where cascade behavior is governed by the ON DELETE CASCADE constraint definition and applies at the row level. TRUNCATE CASCADE is table-level and not constrained by the same rules. A developer who knows that DELETE FROM parent_table would cascade to child rows via an ON DELETE CASCADE constraint may not realize that TRUNCATE parent_table CASCADE will truncate the entire child table, including rows that reference other parent rows not being deleted.
Sequence behavior
TRUNCATE ... RESTART IDENTITY resets sequences associated with the table. DELETE leaves sequences unchanged. If your application expects INSERT after DELETE to produce IDs continuing from the previous maximum, TRUNCATE RESTART IDENTITY will restart from 1 and potentially collide with IDs that exist in other tables as foreign keys.
Replication behavior
Before Postgres 11, logical replication did not replicate TRUNCATE at all. Subscribers would not see the truncation. Tables on the subscriber would retain their rows while the publisher had none. The databases would silently diverge.
Postgres 11 added TRUNCATE support to logical replication, but it requires the publication to be created with publish = 'truncate' enabled. This is not the default. If you run TRUNCATE on a logically replicated table without verifying that your publication replicates it, your subscriber may diverge silently.
MVCC behavior
DELETE leaves dead tuples for VACUUM to reclaim. Concurrent readers with open transactions see the rows as existing until their snapshot ends. TRUNCATE does not leave dead tuples — it deallocates the pages. On a table that is being actively read under a long-running transaction, TRUNCATE will succeed but concurrent readers may see table pages disappear from under them, causing errors depending on isolation level.
When TRUNCATE is correct
TRUNCATE is the right operation when you want to empty a table completely and you do not need trigger execution, foreign-key-safe per-row cascade, or MVCC-compatible concurrent reads. The canonical correct uses:
- Test teardown: empty a test table between test runs where no triggers need to fire and no concurrent readers exist.
- Staging refresh: replace staging data with a fresh copy where the system is taken offline during the operation.
- ETL full-table replacement: truncate then reload as a batch operation where you own the entire transaction.
The name suggests that TRUNCATE's primary advantage is speed. The actual difference is semantics. Speed is what you get when you skip row-level processing. Whether skipping row-level processing is safe depends entirely on what your system expects that processing to do.
Building in public at builds.anethoth.com — public build dossiers for software projects in progress.