TRUNCATE — The Fast Bulk Delete and the High Water Mark

The code in this article is meant for production RDBMSs

The TRUNCATE TABLE syntax in this article is the form used by production RDBMSs like MySQL / PostgreSQL / Oracle / SQL Server. The browser console for this course (which uses SQLite) does not accept the TRUNCATE keyword directly. In SQLite, DELETE FROM table; has the same effect, and an internal truncate optimization makes it run at the same speed as TRUNCATE.

TRUNCATE — wiping every row, fast

There are two ways to delete every row in a table at once. One is DELETE FROM table; (without WHERE), which we covered in the previous article. The other is `TRUNCATE TABLE table;`, the topic of this article. Both leave the table empty, but how they get there and how fast they run are completely different.

DELETE is a DML (Data Manipulation Language) command that removes rows one at a time, leaving a log entry for each deletion. TRUNCATE, on the other hand, leans toward DDL (Data Definition Language) — it resets the entire table without per-row bookkeeping, almost as if the table definition itself is recreated from scratch.

-- 1) DELETE everything (per-row delete log, can be rolled back)
DELETE FROM customer;

-- 2) TRUNCATE everything (one-shot reset, no per-row log, usually commits immediately)
TRUNCATE TABLE customer;

-- Note: this course's console (SQLite) doesn't accept TRUNCATE.
--   DELETE FROM customer; gets the same truncate optimization internally and runs fast.

Differences across MySQL / PostgreSQL / Oracle

The TRUNCATE TABLE table; syntax itself is shared across MySQL / PostgreSQL / Oracle / SQL Server, but the fine-grained behavior differs by DB.

- MySQL: Treated as DDL, cannot be rolled back, AUTO_INCREMENT is reset

- PostgreSQL: Can be rolled back, RESTART IDENTITY resets the sequence too

- Oracle: Treated as DDL, cannot be rolled back, REUSE STORAGE / DROP STORAGE lets you control how storage is returned

- SQL Server: Can be rolled back

The High Water Mark — what's behind the speed gap

The key to understanding the speed gap between DELETE and TRUNCATE is the concept of the High Water Mark (HWM). The HWM is a marker pointing to the highest storage block the table has ever used. The term comes from Oracle, but PostgreSQL / SQL Server / MySQL (InnoDB) all have equivalent optimizations working internally.

When the DB does a full scan (a row-by-row sweep like SELECT * FROM table), it reads every block from the start of the table up to the HWM in order. The higher the HWM, the more blocks it has to read — and since it doesn't skip empty blocks, a scan can be slow even on an empty table if the HWM is high.

The crucial behavior here is that DELETE does not lower the HWM. When you run DELETE FROM table; to remove every row, the table's contents go empty, but the blocks themselves stay in the segment. The HWM stays at block 5. Run SELECT COUNT(*) FROM table; next, and even though the row count is 0, the DB reads every block at or below the HWM to the end — taking about the same time as before the delete.

TRUNCATE TABLE table; is the command that resets the HWM back to the start of the table, instantly shrinking the full-scan target to 0 blocks. That's the real reason TRUNCATE is described as "instant even on huge tables."

DELETE didn't shrink the data?

I've run into a situation in production where I issued DELETE to reduce data size, but storage usage barely went down.

The cause was exactly this: the HWM doesn't drop. DELETE only removes rows logically, while the blocks (pages) the table had reserved stay put. The file on disk still holds that area, so from the OS's perspective the data size hasn't changed.

When you actually want to shrink the storage, run a per-DB reorganization command (VACUUM FULL on PostgreSQL, OPTIMIZE TABLE on MySQL InnoDB, ALTER TABLE ... SHRINK SPACE on Oracle), or use TRUNCATE for a full wipe.

Choosing between DELETE and TRUNCATE

In practice, the choice isn't simply "TRUNCATE for everything, DELETE when you need a condition." The real factors are whether you need to roll back, whether triggers must fire, and how much processing speed matters. The comparison table below is the standard checklist.

TRUNCATE pitfalls

TRUNCATE is fast and convenient, but it tends to be an operation you can't take back. Watch for:

- Some DBs commit on execution (Oracle / MySQL). In production, run SELECT COUNT(*) first to confirm the row count before firing it

- `AUTO_INCREMENT` gets reset (depends on DB). If the IDs are exposed to external systems or URLs, watch for collisions after the reset

- Foreign key constraints can block it (most DBs). If subscription references customer, you'll need to empty the child table first to TRUNCATE customer

- Triggers generally don't fire. A trigger is the DB mechanism that automatically runs SQL in response to row inserts / updates / deletes — for example, "write an audit log entry to another table whenever a row is deleted." DELETE fires this trigger row by row, but TRUNCATE doesn't operate at row level, so audit logs and similar records won't be created

Reach for TRUNCATE when you need maximum speed on a bulk wipe of huge data; otherwise DELETE WHERE or a rollback-able DELETE is the safer choice.