DELETE vs TRUNCATE
Overview
When data needs to be removed, developers often confuse DELETE and TRUNCATE. DELETE is a surgical DML operation. It removes specific rows one-by-one, evaluates triggers, and writes every deletion to the transaction log so it can be rolled back if necessary. TRUNCATE is a nuclear DDL operation. It doesn't look at the rows; it simply deallocates the entire disk file storing the table, instantly wiping millions of rows in milliseconds.
Syntax
-- 1. DELETE (Surgical, logged, can be rolled back)
-- Safely removes exactly one user.
DELETE FROM users
WHERE user_id = 999;
-- 2. DELETE ALL (Very slow on large tables!)
-- Scans and logs every single row deletion.
DELETE FROM temp_logs;
-- 3. TRUNCATE (Instantaneous, DDL, often cannot be easily rolled back)
-- Instantly wipes the table and resets auto-incrementing IDs to 1.
TRUNCATE TABLE temp_logs;Common Pitfalls
- Running
DELETE FROM table;on a 500 million row table. BecauseDELETElogs every single row to the Write-Ahead Log (WAL), this query might take 4 hours to run, consume hundreds of gigabytes of disk space for the logs, and lock the table. If you want to empty a table, ALWAYS useTRUNCATE. - Forgetting about
ON DELETE CASCADE. If you delete a User, what happens to their Orders? If Foreign Keys are set up correctly, the database will block the deletion. IfCASCADEis enabled, deleting the User will silently and instantly delete all their Orders, Payments, and Comments too. Be extremely careful.
Interview Questions
TRUNCATE categorized as Data Definition Language (DDL) instead of Data Manipulation Language (DML) like DELETE?Because TRUNCATE operates physically, not logically. It does not scan or mutate rows. It literally alters the table's structural metadata, telling the operating system to drop the data file and create a brand new, empty one in its place.
Real-World Example
A scheduled CRON job that cleans up old session tokens without locking the table.
-- We use DELETE here because we only want to surgically remove a subset,
-- but we LIMIT it so we don't overwhelm the transaction log!
DELETE FROM active_sessions
WHERE last_activity < CURRENT_TIMESTAMP - INTERVAL '30 days';Check Your Knowledge
Test your understanding of DELETE vs TRUNCATE with these quick questions.