DELETE vs TRUNCATE vs DROP: What Each Removes and Rolls Back

DELETE vs TRUNCATE vs DROP in one line: DELETE removes the rows you pick, TRUNCATE removes every row in one operation, and DROP removes the table itself, with its indexes, triggers and constraints. The harder question is what you can take back: DELETE is an ordinary transactional write everywhere. TRUNCATE rolls back in PostgreSQL but causes an implicit commit in MySQL. SQLite has no TRUNCATE at all. The PostgreSQL and MySQL behaviour below is quoted from their manuals, as of October 2026; the SQLite behaviour is run live in Python.

Statement	What it removes	Rolls back?
DELETE	rows matching WHERE, or all; the table stays	yes, in every engine here
TRUNCATE	every row at once; the table stays	PostgreSQL yes; MySQL no, implicit commit
DROP TABLE	the table, its indexes, triggers, constraints	SQLite yes; MySQL no, implicit commit

The problem it solves

"Clear out the staging table" can mean three things: remove last month's rows, empty the table but keep it and its indexes for tonight's load, or retire it for good. Each statement has its own cost, locks and answer to "can I undo this?", and confusing them is how a cleanup script commits something nobody meant to commit.

The intuition

Think about what each statement has to touch.

• DELETE works row by row. It finds each row, through WHERE if there is one, removes it, updates every index, checks foreign keys and fires ON DELETE row triggers. Every removed row is part of the transaction, so a ROLLBACK restores them all. It is the only one of the three that can be selective.
• TRUNCATE works on the table as a whole. No WHERE, no per-row visits. PostgreSQL describes it as having the same effect as an unqualified DELETE but faster, because it does not scan the table, and it reclaims disk space at once. MySQL describes it as similar to a DROP TABLE followed by CREATE TABLE.
• DROP removes the definition. The rows go, but so do the columns, indexes, triggers and constraints. Afterwards a SELECT is an error, not an empty result.

Then the details that interviews like:

• Rollback. PostgreSQL's TRUNCATE is transaction-safe: if the surrounding transaction does not commit, the truncation is rolled back. MySQL's TRUNCATE TABLE and DROP TABLE cause an implicit commit, so they cannot be rolled back, and they commit whatever the transaction had done before them.
• Triggers. TRUNCATE fires no ON DELETE triggers in either engine; PostgreSQL fires ON TRUNCATE triggers instead.
• Counters. MySQL resets AUTO_INCREMENT to its start value. PostgreSQL keeps sequences unless you write RESTART IDENTITY.
• Foreign keys. PostgreSQL refuses to truncate a table that other tables reference, unless they are truncated in the same command or you add CASCADE. MySQL's TRUNCATE TABLE fails on an InnoDB table that other tables reference.
• Locks. PostgreSQL's TRUNCATE takes an ACCESS EXCLUSIVE lock, which blocks all other access; the manual suggests DELETE when concurrent access is needed.

SQLite has no TRUNCATE. A DELETE with no WHERE on a table without triggers uses its "truncate optimization", which erases the table without visiting each row.

Watch it run

The animation is the lesson's museum loan. BEGIN, and the engine notes the state it would have to return to. Hallam goes from 3 to 9, applied but invisible to other connections. Then Verrey would go to -1, the CHECK fires, and the rollback restores both rows, because the unit of work is the transaction, not the statement. A DELETE is a write of exactly the same kind: inside a transaction it can be undone just like those updates. A TRUNCATE in MySQL, or any DROP there, would have ended that transaction with an implicit commit before the rollback ever ran.

The code

The three statements in PostgreSQL syntax, with the options the manual documents:

DELETE FROM loan WHERE objects = 0;             -- some rows, row by row
DELETE FROM loan;                               -- every row, still row by row

BEGIN;
TRUNCATE loan RESTART IDENTITY;                 -- every row at once, reset sequences
ROLLBACK;                                       -- PostgreSQL: the rows come back

DROP TABLE loan;                                -- the table itself

Now in SQLite, on the lesson's loan table with one more museum. The connection is in manual transaction mode so the BEGIN and ROLLBACK are visible. Delete some rows, then all of them, roll back; empty the table for real; drop it inside a transaction and roll that back too; then drop it for good:

import sqlite3

db = sqlite3.connect(":memory:", isolation_level=None)    # we write BEGIN / ROLLBACK ourselves
db.executescript("""
  CREATE TABLE loan (museum TEXT, objects INTEGER CHECK (objects >= 0));
  INSERT INTO loan VALUES ('Hallam', 3), ('Verrey', 0), ('Ashby', 5);
""")
rows = lambda: db.execute("SELECT * FROM loan").fetchall()
tables = lambda: [r[0] for r in db.execute("SELECT name FROM sqlite_master WHERE type = 'table'")]

db.execute("BEGIN")
print(db.execute("DELETE FROM loan WHERE objects = 0").rowcount)   # 1
print(db.execute("DELETE FROM loan").rowcount, rows())             # 2 []
db.execute("ROLLBACK")
print(rows())               # [('Hallam', 3), ('Verrey', 0), ('Ashby', 5)]  <- all three back

db.execute("DELETE FROM loan")                                     # no WHERE: every row
print(rows(), tables())     # [] ['loan']   <- empty, but the table is still there

db.execute("BEGIN")
db.execute("DROP TABLE loan")
print(tables())             # []
db.execute("ROLLBACK")      # in SQLite, even the DROP is part of the transaction
print(tables())             # ['loan']

db.execute("DROP TABLE loan")
try:
    db.execute("SELECT * FROM loan")
except sqlite3.OperationalError as e:
    print(e)                # no such table: loan

The ROLLBACK brought all three rows back. An emptied table is still listed in sqlite_master; a dropped one is not, and querying it raises an error. SQLite runs DROP TABLE inside the transaction, so it rolled back as well, which is exactly what MySQL's implicit commit would not allow.

What DELETE does that a truncation skips: row triggers fire, the AUTOINCREMENT counter survives, and foreign keys are checked. DROP then removes the trigger and the counter with the table:

db.executescript("""
  PRAGMA foreign_keys = ON;
  CREATE TABLE museum (name TEXT PRIMARY KEY);
  CREATE TABLE crate (id INTEGER PRIMARY KEY AUTOINCREMENT,
                      museum TEXT REFERENCES museum(name));
  CREATE TABLE audit (note TEXT);
  CREATE TRIGGER crate_gone AFTER DELETE ON crate
    BEGIN INSERT INTO audit VALUES ('crate ' || OLD.id || ' removed'); END;
  INSERT INTO museum VALUES ('Hallam'), ('Verrey');
  INSERT INTO crate (museum) VALUES ('Hallam'), ('Hallam'), ('Verrey');
""")
db.execute("DELETE FROM crate")                    # the row trigger fires once per row
print(db.execute("SELECT * FROM audit").fetchall())
# [('crate 1 removed',), ('crate 2 removed',), ('crate 3 removed',)]

db.execute("INSERT INTO crate (museum) VALUES ('Hallam')")
print(db.execute("SELECT id FROM crate").fetchall())   # [(4,)]  <- DELETE kept the counter
try:
    db.execute("DELETE FROM museum WHERE name = 'Hallam'")
except sqlite3.IntegrityError as e:
    print(e)                                           # FOREIGN KEY constraint failed

db.executescript("""
  DROP TABLE crate;                                    -- table, trigger and counter all go
  CREATE TABLE crate (id INTEGER PRIMARY KEY AUTOINCREMENT, museum TEXT);
  INSERT INTO crate (museum) VALUES ('Hallam');
""")
print(db.execute("SELECT id FROM crate").fetchall())   # [(1,)]  <- a new table starts again
print(db.execute("SELECT name FROM sqlite_master WHERE type = 'trigger'").fetchall())   # []

Three rows, three audit notes, because the row trigger ran once per deleted row; with a trigger present, SQLite also skips the truncate optimization. The next crate got id 4, not 1. The museum still referenced by a crate could not be deleted. After the DROP and CREATE, ids start again from 1, the same reset that MySQL's TRUNCATE performs.

Finally, the truncate optimization against a DELETE that has to visit every row, on 200,000 rows:

import time

def timed_delete(sql):
    big = sqlite3.connect(":memory:", isolation_level=None)
    big.execute("CREATE TABLE loan (museum TEXT, objects INTEGER)")
    big.executemany("INSERT INTO loan VALUES (?, ?)", ((f"M{i}", i % 7) for i in range(200000)))
    start = time.perf_counter()
    n = big.execute(sql).rowcount
    return n, round(time.perf_counter() - start, 3)

print(timed_delete("DELETE FROM loan"))                     # (200000, 0.001)  <- truncate optimization
print(timed_delete("DELETE FROM loan WHERE objects >= 0"))  # (200000, 0.022)  <- visits every row
# on one machine, roughly; the row counts are exact, the seconds are not

On one machine, roughly, the version with no WHERE was about twenty times faster; both removed exactly 200,000 rows.

The complexity

• DELETE: O(rows removed), each with index maintenance, trigger and foreign key work, plus undo or log records for the rollback.
• TRUNCATE: close to constant in the number of rows, because it releases the table's storage instead of visiting rows, but it needs an exclusive lock.
• DROP: like TRUNCATE for the data, plus removing the definition and everything attached to it.

Where it goes wrong

• Running TRUNCATE in a MySQL transaction and expecting ROLLBACK. The implicit commit has already happened, and it committed the earlier statements too.
• Expecting TRUNCATE to fire ON DELETE triggers. It does not, so audit tables fed by those triggers miss the rows.
• Forgetting the counter. MySQL's TRUNCATE resets AUTO_INCREMENT; a DELETE does not. Code that assumes either behaviour breaks on the other.
• A huge DELETE in one transaction. It holds locks and log space until the end; delete in batches, or truncate if every row should go.
• DROP when you meant empty. Indexes, triggers and permissions vanish with it.

When it shows up in interviews

The question is usually literal, and a strong answer covers rollback and triggers, not only speed. It is also a natural follow-up to the transaction questions in ACID transactions explained and to isolation levels. The undo machinery itself is in the Transactions and ACID lesson, and why every removed row also costs index work is covered in SQL indexes explained.

How to say it in an interview

"DELETE removes the rows I select, row by row, so it can use WHERE, fires row triggers, checks foreign keys and is fully transactional. TRUNCATE empties the whole table in one operation: much faster, no WHERE, no ON DELETE triggers. Whether it rolls back depends on the engine: PostgreSQL rolls it back with the transaction, MySQL commits implicitly. DROP removes the table and everything attached to it. For a selective or undoable cleanup I use DELETE; to reset a staging table I use TRUNCATE; I use DROP only when the table should no longer exist."

Frequently asked questions

What is the difference between DELETE, TRUNCATE and DROP?

DELETE removes chosen rows one by one and keeps the table. TRUNCATE removes every row at once, without WHERE or ON DELETE triggers, and keeps the table. DROP TABLE removes the table itself, including its indexes, triggers and constraints, so later queries on it fail.

Can TRUNCATE be rolled back?

In PostgreSQL, yes: the truncation is rolled back if the surrounding transaction does not commit. In MySQL, no: TRUNCATE TABLE causes an implicit commit. SQLite has no TRUNCATE; its DELETE without WHERE is an ordinary transactional delete and rolls back.

Is TRUNCATE faster than DELETE?

Usually, yes, for emptying a whole table. TRUNCATE releases the table's storage instead of visiting every row, so it skips per-row index, trigger and logging work. DELETE is the right tool when you need a WHERE, triggers or concurrent access while it runs.

Sources

• TRUNCATE — PostgreSQL Documentation
• DELETE — PostgreSQL Documentation
• DROP TABLE — PostgreSQL Documentation
• TRUNCATE TABLE Statement — MySQL 8.4 Reference Manual
• Statements That Cause an Implicit Commit — MySQL 8.4 Reference Manual
• DELETE, the truncate optimization — SQLite Documentation