DELETE vs TRUNCATE in SQL

Why this question shows up in interviews

Imagine the scene. You are on a final-round loop for a senior data analyst seat at Stripe. The hiring manager flips to a whiteboard prompt: "You have a 200 GB events_staging table that needs to be wiped every night before the next ETL load. You typed DELETE FROM events_staging; and the job runs for forty minutes. What is the right command and why?" That is the moment a question that sounds like trivia becomes a real production decision, and the interviewer is watching whether you pause to ask about transactions, triggers, and foreign keys first.

DELETE and TRUNCATE look like two ways to do the same thing but sit in different layers of the engine. DELETE is a row-level DML statement that walks the table, logs every change, fires row triggers, and respects every constraint. TRUNCATE is a near-DDL operation that wipes data pages wholesale, bypasses row triggers, and behaves differently across engines. The differences only matter at scale, but at scale they turn a sub-second job into a forty-minute outage.

How DELETE actually works

DELETE is the row-level DML statement defined in the ANSI/ISO SQL standard. It accepts an optional WHERE clause, walks every row that matches, and removes them one at a time. Each removed row is written into the write-ahead log so the operation can be rolled back, replayed during crash recovery, or shipped to a replica. Because the work is row-by-row, every row trigger fires, every foreign-key constraint is checked, and every index entry is updated in lock-step with the table page.

-- Remove inactive users with a predicate
DELETE FROM users
WHERE last_login < '2025-01-01';

-- Capture what you deleted (PostgreSQL, Oracle)
DELETE FROM users
WHERE last_login < '2025-01-01'
RETURNING user_id, email;

A property worth knowing for interviews: DELETE does not reclaim disk space on its own. PostgreSQL marks rows as dead tuples and reuses the space via VACUUM. MySQL with InnoDB reuses freed space for new rows but does not shrink the file without OPTIMIZE TABLE. SQL Server leaves pages allocated until a clustered index rebuild. A DELETE of half a billion rows can show "success" yet leave the table at its original disk footprint.

DELETE is what you reach for when the operation needs predicates, row triggers, or RETURNING. The cost is throughput — for a hundred million rows the row-by-row machinery dominates runtime, and WAL writes can saturate disk if you do not chunk.

How TRUNCATE actually works

TRUNCATE removes every row in a single operation. It does not walk rows — it marks all data pages as free and resets the table to its empty state. In PostgreSQL it runs at near-DDL speed regardless of size, in MySQL it is implemented as DROP plus CREATE, and in SQL Server it is a minimally logged operation that releases all extents to the file group.

-- Wipe the table in milliseconds
TRUNCATE TABLE users;

-- Multiple tables in one statement (PostgreSQL)
TRUNCATE TABLE orders, order_items;

-- Reset auto-incrementing identity columns
TRUNCATE TABLE users RESTART IDENTITY;

-- Wipe child tables that reference this one
TRUNCATE TABLE customers CASCADE;

Two clauses come up in interviews. RESTART IDENTITY resets sequence values so the next inserted row gets ID 1 — useful for nightly staging, dangerous when IDs are referenced downstream. CASCADE tells the engine to also truncate any table holding a foreign key back to the target, which prevents the constraint error but can wipe more than you intended if you forget the dependency graph.

TRUNCATE is fast because it does not generate per-row log records. PostgreSQL logs page allocation changes, MySQL replaces the file, SQL Server records deallocations. For a 200-million-row table the difference between forty minutes of DELETE and four hundred milliseconds of TRUNCATE is precisely the difference between row-level and page-level bookkeeping.

The key differences side by side

Reciting "TRUNCATE is page-level and skips row triggers, DELETE is row-level and respects them" earns a follow-up question in most loops. Memorize the rows for "WHERE clause", "Triggers", "Transactional rollback", and "Auto-increment reset" first.

Transactionality across engines

This question separates senior candidates from juniors because the answer depends on the engine. In PostgreSQL, TRUNCATE is fully transactional. You can run BEGIN; TRUNCATE staging; ROLLBACK; and the data comes back. The engine takes a table-level exclusive lock for the duration, which blocks other readers and writers, but the operation respects the transaction boundary.

-- PostgreSQL: TRUNCATE rolls back cleanly
BEGIN;
TRUNCATE TABLE staging_data;
-- something went wrong upstream
ROLLBACK; -- staging_data IS intact

In MySQL with InnoDB, TRUNCATE performs an implicit commit. Any open transaction is closed the moment the statement runs, and the operation cannot be rolled back. This is one of the most painful surprises for analysts migrating ETL code from PostgreSQL to MySQL — the same pattern that was safe silently loses the rollback guarantee on the new engine. The safe alternative is DELETE inside a transaction. SQL Server treats TRUNCATE as minimally logged but still rolls it back inside a transaction. Snowflake supports TRUNCATE and rolls it back, and its time-travel feature can UNDROP a wiped table within the retention window.

Common pitfalls

The first pitfall is reaching for TRUNCATE on a table referenced by a foreign key. PostgreSQL refuses with an error until you add CASCADE, and the moment you do, the operation also wipes every child table that references the parent. New analysts add the keyword to silence the error and only discover during the next stand-up that they wiped six dependent tables. Map out the foreign-key graph first, and consider whether DELETE with explicit ordering is a safer match.

The second pitfall is assuming TRUNCATE fires the same triggers as DELETE. Row-level triggers do not run during a TRUNCATE, so any audit log built around a BEFORE DELETE or AFTER DELETE row trigger silently misses the wipe. PostgreSQL exposes statement-level BEFORE TRUNCATE and AFTER TRUNCATE triggers, but those are a different surface and most teams have not wired them up. If compliance requires every removal to be logged, prefer DELETE or add explicit truncate triggers and test them.

The third pitfall is the MySQL implicit-commit behaviour. The code reads BEGIN; TRUNCATE; INSERT; ROLLBACK; and the analyst expects the rollback to undo the truncate. Under InnoDB it does not — the truncate already committed, the insert ran in a new auto-commit transaction, and the rollback only un-does whatever was buffered after. This pattern has caused enough nightly incidents that some teams ban TRUNCATE on MySQL outside stand-alone scripts.

The fourth pitfall is forgetting that TRUNCATE requires a stronger lock than DELETE. On a warehouse table with continuous reads, TRUNCATE blocks until every reader finishes because it needs an exclusive lock. DELETE proceeds under shared locks and waits on row contention. If you can schedule a short maintenance window, TRUNCATE wins on speed. If reads cannot stop, chunked DELETE wins on availability.

The fifth pitfall is treating identity reset as the same across engines. PostgreSQL accepts TRUNCATE TABLE t RESTART IDENTITY; in one statement. SQL Server requires a separate DBCC CHECKIDENT call. MySQL auto-resets because the implementation is DROP plus CREATE, but only for InnoDB and only when no other connection holds a metadata lock. Saying "TRUNCATE resets the auto-increment" without naming the engine is the half-answer that loses follow-ups.

Optimization tips

When you need to remove rows on a hot table without taking a long lock, partition drops beat both DELETE and TRUNCATE. If events is partitioned by event_date and you need to drop everything older than a year, ALTER TABLE events DETACH PARTITION events_2024_01; finishes in milliseconds, releases disk space immediately, and does not block readers on other partitions. Warehouse teams at Snowflake, Databricks, and Vercel build partitioning into their schema from day one for precisely this reason.

For unpartitioned tables, the cheapest "wipe and reload" pattern is a swap: CREATE TABLE new AS SELECT ... WHERE keep;, then DROP TABLE old; and ALTER TABLE new RENAME TO old;. The intermediate table holds only the rows you want to keep, the swap happens in metadata, and the old table is dropped without scanning rows. The downside is that any view, foreign key, or trigger attached to the old table needs to be re-attached, which makes this a pattern for ETL scripts rather than online schemas.

For chunked deletes, batch in groups of ten to fifty thousand rows and commit between batches. This keeps locks small, lets concurrent reads proceed, and avoids WAL spikes that saturate replication. Follow a large DELETE with VACUUM FULL or pg_repack in PostgreSQL, OPTIMIZE TABLE in MySQL, or a clustered index rebuild in SQL Server — none reclaim space automatically, and a DELETE-heavy workload without maintenance leaves the table with high bloat and degraded query plans.

The last optimization matters most for staging tables: design them so TRUNCATE is always the right answer. Keep staging tables free of foreign keys, row triggers, and audit dependencies. Use partition-per-load when volume demands it. When TRUNCATE is the right answer by construction, you never debate whether to chunk a DELETE or wait out a VACUUM.

Related reading

• NULL in SQL guide
• CASE WHEN SQL guide
• COALESCE vs ISNULL in SQL
• SQL window functions interview questions

If you want to drill SQL questions like this every day, NAILDD is launching with 500+ SQL problems across exactly this pattern.

FAQ

Can I roll back a TRUNCATE?

In PostgreSQL and SQL Server, yes. TRUNCATE runs inside a transaction and a ROLLBACK returns the data to its previous state, though both engines hold an exclusive table-level lock for the duration. In MySQL with InnoDB, no — TRUNCATE issues an implicit commit before and after the statement, so any surrounding transaction is closed and the wipe is permanent. The MySQL workaround is DELETE inside a transaction, accepting lower throughput and higher WAL volume in exchange for the safety net.

Does TRUNCATE remove the table structure?

No. The table definition, including columns, constraints, indexes, and grants, stays exactly as it was. Only the data and the auto-increment counter (if you pass RESTART IDENTITY in PostgreSQL or rely on MySQL's implicit reset) are affected. To remove the table itself, use DROP TABLE, which deletes the data, the structure, and dependent objects unless guarded by IF EXISTS or CASCADE.

When should I use DELETE instead of TRUNCATE?

Use DELETE when you need a predicate, row-level triggers to fire, a RETURNING clause to capture what you removed, or when the table is referenced by foreign keys you do not want to wipe through CASCADE. Use TRUNCATE for staging tables, full table wipes where speed matters and rollback semantics are well understood for your engine, and cases where resetting auto-increment is part of the intent. Mental model: DELETE for surgical work, TRUNCATE for a clean slate.

What does TRUNCATE CASCADE actually do?

TRUNCATE TABLE customers CASCADE; truncates customers and, in PostgreSQL, every table holding a foreign key back. PostgreSQL recursively truncates the entire foreign-key graph, which is silent for tables you may not have remembered are connected. Map the dependencies before typing CASCADE, or inspect them with pg_dump --schema-only. If only one table needs wiping and the foreign keys would block, the safer alternative is to disable constraints, DELETE from the children explicitly, and re-enable them.

Is DELETE without WHERE the same as TRUNCATE?

The end state is the same — the table is empty — but the path is very different. DELETE without WHERE walks every row, writes per-row WAL entries, fires row triggers, validates foreign keys, and updates every index entry. TRUNCATE marks the data pages as free in a single metadata operation. The runtime difference on a 200 GB table is between minutes and milliseconds. Valid reasons to prefer DELETE FROM table; exist — audit triggers, replication tools that handle row events but not truncate events, MySQL transactions that need rollback safety — but performance is never one of them.

Why does my DELETE run so slowly on a large table?

The most common cause is per-row work: triggers firing, indexes updating, foreign keys validating, and WAL records being written. The second cause is lock contention with concurrent readers and writers blocking the delete from progressing. The third is index maintenance: every index on the table has to be updated for every removed row, so a table with eight indexes is roughly eight times slower than the same table with one. The fix is usually to chunk the delete into batches of ten to fifty thousand rows, commit between batches, and consider whether TRUNCATE or a partition drop matches the actual intent better.