Compare

DELETE vs TRUNCATE vs DROP

Q: Is TRUNCATE faster than DELETE?

Significantly, for large tables. DELETE removes rows one by one, fires triggers, and is fully transactional. TRUNCATE deallocates the underlying pages directly: milliseconds vs minutes on a billion-row table. The tradeoffs: TRUNCATE skips per-row triggers, may not be transactional on MySQL/Oracle, and resets sequences.

Q: What's the difference between DROP TABLE CASCADE and RESTRICT?

RESTRICT (default) refuses the drop if anything (foreign keys, views, materialized views) depends on the table. CASCADE drops the table and every dependent object. CASCADE is convenient and occasionally dangerous — a single command can take out views you didn't know existed.

Q: Does DELETE free up disk space?

Eventually, after VACUUM (Postgres) or equivalent maintenance runs. Immediately, no: deleted rows are marked but the pages remain. TRUNCATE releases the pages immediately. For a large delete, VACUUM FULL reclaims space but locks the table.

Short answer: DELETE removes specific rows and the table stays intact. TRUNCATE empties the table fast, page-level, no WHERE clause. DROP TABLE removes the table itself: definition, indexes, constraints, every row. Three escalating levels of "make this data go away," with three very different blast radii.

Quick comparison #

	`DELETE`	`TRUNCATE`	`DROP TABLE`
What's removed	matched rows	every row	the table itself
Table survives?	yes	yes	no
Indexes / constraints survive?	yes	yes	no, gone with the table
`WHERE` clause	yes	no	n/a
Speed on big tables	slow (row-by-row)	fast (page-level)	fast
Transactional / reversible?	yes, everywhere	yes in Postgres / SQL Server; no in MySQL / Oracle	yes in Postgres / SQL Server; no in MySQL / Oracle
Triggers fire?	per-row triggers fire	usually no	n/a
Foreign key cascades fire?	yes	refused if FKs reference the table (most engines)	refused (or `CASCADE` to drop dependents too)
Resets identity / sequence?	no	yes (often)	n/a
Use for	targeted cleanup	"empty this staging table"	"this table no longer exists"

See them in action #

Loading SQL editor...

When to use which #

DELETE — anything user-facing or production-critical. Targeted (WHERE id = 42), transactional (works inside BEGIN / ROLLBACK), audit-friendly (triggers fire, change-data-capture sees every row). The right tool for "remove the cancelled orders from last quarter" or "GDPR-erase this user."

TRUNCATE is for staging tables, scratch tables, ETL intermediates. Anything where the contract is "this table is rebuilt from scratch on every run." Fast, simple, and intent is clearer than DELETE FROM staging with no WHERE.

DROP TABLE is for when the table is genuinely no longer needed. For risky drops, the safer pattern is rename then drop later: ALTER TABLE orders RENAME TO orders__deprecated_2026_04, monitor for a week to find any straggler queries, then drop. The rename is reversible in seconds; the drop is not. Detail on DROP TABLE.

Common mistakes #

Using TRUNCATE to "clean up production" on MySQL or Oracle: implicit commit means no rollback. Same blast radius as DROP for the row data, just the table shape survives. Use DELETE inside a transaction instead.
DROP TABLE to "fix" something quickly: irreversible, takes the indexes and constraints with it, and any view, FK, or downstream query referencing it breaks.
Forgetting that DELETE cascades: DELETE FROM users WHERE id = 42 looks innocent until ON DELETE CASCADE foreign keys remove thousands of rows from orders, order_items, reviews, etc. Audit the FK graph before bulk deletes.
Forgetting that TRUNCATE resets sequences: if downstream systems rely on monotonically-increasing IDs, TRUNCATE plus INSERT can produce ID collisions with previously-emitted data.

FAQ #

Is TRUNCATE faster than DELETE?

Significantly, for large tables. DELETE removes rows one by one, fires triggers, and is fully transactional. TRUNCATE deallocates the underlying pages directly: milliseconds vs minutes on a billion-row table. The tradeoffs: TRUNCATE skips per-row triggers, may not be transactional on MySQL/Oracle, and resets sequences.

Can I undo TRUNCATE?

On Postgres / SQL Server / DuckDB inside a transaction: yes, ROLLBACK reverses it. On MySQL InnoDB and Oracle: no, because DDL implicitly commits, so TRUNCATE is permanent the moment it runs. Treat as irreversible on those engines.

When should I use DROP TABLE instead of DELETE or TRUNCATE?

When the table is genuinely no longer needed — schema cleanup, deprecated features. For risky drops, the rename-then-drop pattern is safer: ALTER TABLE orders RENAME TO orders__deprecated_2026_04, monitor for a week, then drop. The rename is reversible in seconds; the drop is not.

What's the difference between DROP TABLE CASCADE and RESTRICT?

RESTRICT (default) refuses the drop if anything (foreign keys, views, materialized views) depends on the table. CASCADE drops the table and every dependent object. CASCADE is convenient and occasionally dangerous — a single command can take out views you didn't know existed.

Does DELETE free up disk space?

Eventually, after VACUUM (Postgres) or equivalent maintenance runs. Immediately, no: deleted rows are marked but the pages remain. TRUNCATE releases the pages immediately. For a large delete, VACUUM FULL reclaims space but locks the table.

Why does DELETE FROM big_table take hours?

Row-by-row deletion + trigger firing + transaction log writes scale linearly with row count. For "delete most of this table," consider: (1) TRUNCATE if you can drop everything; (2) INSERT the keep-rows into a new table, swap; (3) batch-delete with LIMIT and WHERE to avoid one giant transaction.

Warning

The reversibility ladder. DELETE inside a transaction = recoverable in 1 second. DELETE after commit = recoverable from a backup (hours). TRUNCATE on MySQL/Oracle = recoverable from a backup. DROP TABLE = recoverable from a backup. Climb this ladder slowly: start with DELETE in a transaction, only escalate when you need the speed (TRUNCATE) or genuinely want the table gone (DROP).