DELETE vs TRUNCATE vs DROP

DELETE removes rows one by one and supports WHERE; TRUNCATE instantly empties the whole table and typically resets identity counters; DROP removes the table itself, structure and all. Speed and rollback behavior differ sharply between them.

What Does Each Command Actually Remove?

DELETE removes data (some or all rows), TRUNCATE removes all data but keeps the table definition, and DROP removes the table, its indexes, and its permissions entirely.

DELETE FROM logs WHERE created_at < '2025-01-01';  -- selective
TRUNCATE TABLE logs;   -- table stays, empty
DROP TABLE logs;       -- table is gone; SELECT now errors

Can You Roll Back DELETE and TRUNCATE?

DELETE is always transactional — roll it back and the rows return. TRUNCATE is rollback-able inside a transaction in Postgres and SQL Server, but in MySQL and Oracle it commits implicitly and cannot be undone.

BEGIN;
DELETE FROM logs;    -- fully logged, rollback works everywhere
ROLLBACK;            -- rows are back

BEGIN;
TRUNCATE TABLE logs; -- Postgres/SQL Server: can roll back
ROLLBACK;            -- MySQL/Oracle: too late, already committed

How rollback works under the hood is covered in SQL transactions and ACID.

Does TRUNCATE Reset the Identity Column?

Usually yes: TRUNCATE resets auto-increment/identity counters back to their seed in SQL Server and MySQL, while Postgres makes it explicit with RESTART IDENTITY. DELETE never resets the counter.

-- Postgres:
TRUNCATE TABLE logs RESTART IDENTITY;  -- next id = 1
TRUNCATE TABLE logs CONTINUE IDENTITY; -- default: keep the sequence

-- After DELETE FROM logs; the next insert continues from the old max id.

Why Is TRUNCATE So Much Faster?

DELETE logs every removed row and fires row-level triggers, so on a big table it can run for minutes and bloat the transaction log. TRUNCATE deallocates the table's data pages in one metadata operation, but it takes an exclusive table lock and is blocked by foreign key references in most engines.

-- 50M-row table, typical timings:
DELETE FROM events;          -- minutes, huge WAL/log growth
TRUNCATE TABLE events;       -- milliseconds, minimal logging
-- TRUNCATE fails if another table has a FK pointing here
-- (Postgres offers TRUNCATE ... CASCADE; use with care)

Common Pitfalls

• TRUNCATE on MySQL/Oracle is final: it implicitly commits — no rollback, so snapshot or verify first.
• DELETE without WHERE: valid SQL that empties the table slowly. Write the WHERE first, or run inside a transaction.
• Foreign keys block TRUNCATE: referencing tables must be truncated together or the FK dropped; DELETE respects FKs row by row instead.
• Triggers don't fire on TRUNCATE: row-level audit triggers are skipped, so audit trails silently miss the wipe.
• DROP loses structure: indexes, grants, and constraints go with it — recreating the table doesn't restore them automatically.