DELETE vs TRUNCATE vs DROP in SQL (PostgreSQL)

All three commands make data disappear, which is why interviewers like to ask them together. The one-line version: DELETE removes rows (some or all) and leaves the table in place, TRUNCATE removes all rows at once without looking at them, and DROP TABLE removes the table itself, structure, indexes, constraints and all.

The details depend on the database. A lot of interview prep material says “TRUNCATE can’t be rolled back”, which is true in MySQL and Oracle and false in PostgreSQL. This guide uses PostgreSQL as the reference and points out where other engines differ, because saying “it depends on the engine, and here’s how” is a stronger answer than reciting one engine’s rules as universal.

The comparison table

DELETE: row by row, with all the safety nets

DELETE is the ordinary data-manipulation statement. It finds rows matching its WHERE clause and removes them one at a time, which means every per-row mechanism runs: ON DELETE triggers fire, foreign keys with ON DELETE CASCADE delete child rows, and RETURNING hands back what was removed.

Here’s a table with an audit trigger, run in SQLite through Python’s sqlite3 module. SQLite has no TRUNCATE, but its DELETE, triggers and transactional DROP behave the way PostgreSQL’s do for this example:

CREATE TABLE events (id INTEGER PRIMARY KEY AUTOINCREMENT, kind TEXT NOT NULL);
CREATE TABLE audit (msg TEXT);
CREATE TRIGGER events_deleted AFTER DELETE ON events
BEGIN INSERT INTO audit VALUES ('deleted ' || OLD.id); END;
INSERT INTO events (kind) VALUES ('login'), ('click'), ('click');

BEGIN;
DELETE FROM events WHERE kind = 'click' RETURNING id;   -- returns 2, 3
SELECT * FROM audit;                                     -- 'deleted 2', 'deleted 3'
ROLLBACK;
SELECT COUNT(*) FROM events;                             -- 3: the rows are back
SELECT COUNT(*) FROM audit;                              -- 0: so is the trigger's work

Rolling back undoes both the delete and everything the trigger did. Now delete everything and insert a new row:

DELETE FROM events;              -- audit gets 'deleted 1', 'deleted 2', 'deleted 3'
INSERT INTO events (kind) VALUES ('login');
SELECT * FROM events;            -- (4, 'login'): the counter did not reset

The trigger fired three times, and the next id is 4. PostgreSQL behaves the same with an IDENTITY or serial column: deleting rows never rewinds the sequence.

In PostgreSQL, a deleted row isn’t physically removed. Its version is marked dead and stays on disk until VACUUM reclaims the space, so deleting 50 million rows from a 60-million-row table writes a lot of WAL, takes a long time, and leaves a bloated table behind. That’s the main reason TRUNCATE exists.

TRUNCATE: empty the table, skip the per-row work

TRUNCATE doesn’t look at rows. In PostgreSQL it gives the table fresh, empty storage and schedules the old files for removal at commit, so emptying a huge table takes about as long as emptying a tiny one. You give up everything that depended on visiting rows:

• No WHERE. It’s all rows or nothing.
• Row-level ON DELETE triggers don’t fire. PostgreSQL has separate BEFORE/AFTER TRUNCATE triggers at the statement level if you need an audit hook.
• No RETURNING.
• It takes an ACCESS EXCLUSIVE lock, so it waits for every running query on the table, and every new query (even a SELECT) waits for it.

Foreign keys get special treatment. You can’t truncate a table that another table references, even if the referencing table is empty, because PostgreSQL won’t scan it to check:

-- PostgreSQL (illustrative; not run here)
TRUNCATE customers;
-- ERROR:  cannot truncate a table referenced in a foreign key constraint
-- DETAIL:  Table "orders" references "customers".
-- HINT:  Truncate table "orders" at the same time, or use TRUNCATE ... CASCADE.

TRUNCATE orders, customers;                      -- both at once is allowed
TRUNCATE customers CASCADE;                      -- also truncates every referencing table
TRUNCATE orders, customers RESTART IDENTITY;     -- and resets their identity sequences

CASCADE is worth a pause in an interview: it doesn’t delete matching child rows like ON DELETE CASCADE, it truncates the entire referencing table, and then any table referencing that one. On a shared database that can be far more data than you meant.

Identity columns keep counting after a plain TRUNCATE (the default is CONTINUE IDENTITY). Add RESTART IDENTITY to reset them, which is handy for test fixtures where you want ids to start at 1 again.

Transactions are where PostgreSQL differs from the folklore. TRUNCATE is fully transactional there:

-- PostgreSQL (illustrative; not run here)
BEGIN;
TRUNCATE orders;
SELECT COUNT(*) FROM orders;   -- 0
ROLLBACK;
SELECT COUNT(*) FROM orders;   -- original count again

The PostgreSQL documentation also notes that TRUNCATE isn’t MVCC-safe: a concurrent transaction that took its snapshot before the truncate committed will see the table as empty if it reads it afterwards, where after a DELETE it would still see the old rows. In MySQL, TRUNCATE TABLE causes an implicit commit, and in Oracle it’s DDL that commits, so in both you can’t roll it back.

DROP TABLE: removing the table itself

DROP TABLE orders deletes the rows, the column definitions, indexes, constraints, triggers and privileges. Afterwards, queries fail with relation "orders" does not exist. It’s the command for a table you no longer want, not for clearing data.

PostgreSQL refuses to drop a table that other objects depend on, such as a view selecting from it or a foreign key pointing at it:

-- PostgreSQL (illustrative; not run here)
DROP TABLE customers;
-- ERROR:  cannot drop table customers because other objects depend on it
-- DETAIL:  constraint orders_customer_id_fkey on table orders depends on table customers
-- HINT:  Use DROP ... CASCADE to drop the dependent objects too.

Here DROP ... CASCADE drops the dependent objects (the foreign key constraint, or the whole view), not the rows in orders. Migrations usually write DROP TABLE IF EXISTS so they can be re-run without failing.

Like TRUNCATE, DROP is transactional in PostgreSQL, which makes schema migrations safer: a migration that drops one table and then fails halfway leaves nothing changed. The SQLite run shows the same idea:

BEGIN;
DROP TABLE events;
SELECT * FROM events;    -- Error: no such table: events
ROLLBACK;
SELECT * FROM events;    -- (4, 'login'): the table and its row are back

Choosing between them in real work

• Removing some rows, such as expired sessions or one tenant’s data: DELETE with a WHERE. For millions of rows, delete in batches (by id range, a few thousand at a time) so each transaction stays short and VACUUM can keep up.
• Clearing a staging or test table before a reload: TRUNCATE, often with RESTART IDENTITY. It’s fast and leaves no dead rows behind. Make sure nothing relies on delete triggers, and that a brief exclusive lock is acceptable.
• Retiring a table: DROP TABLE, in a migration, after checking what depends on it.
• Removing old data from a large time-series table: consider partitioning by month and dropping or detaching whole partitions, which gives you DROP speed with DELETE-like selectivity.

A frequent production mistake is using TRUNCATE on a table with an audit trigger and assuming the audit log captured it. Another is running a huge unbatched DELETE during business hours and wondering why the table is slow afterwards: it’s full of dead rows waiting for vacuum.

Interview practice: clearing a test database between runs

An integration test suite empties orders and customers before each test with DELETE FROM orders; DELETE FROM customers;. It works but is slow, and the tests that assert “the first customer has id 1” fail on every run after the first. A teammate switches to TRUNCATE customers; and gets an error. Explain both problems and propose the statement to use.

Answer

The ids keep climbing because DELETE never resets an identity sequence; after the first run the next customer gets whatever value comes after the last one used. It’s slow because each delete visits every row, fires any triggers and leaves dead tuples for vacuum.

TRUNCATE customers fails because orders has a foreign key referencing customers, and PostgreSQL won’t truncate a referenced table on its own. Truncate both in one statement and reset the sequences:

TRUNCATE orders, customers RESTART IDENTITY;

That’s a single fast operation, ids restart at 1, and it’s transactional, so it can sit inside a test transaction. It’s worth adding the caveats a reviewer would raise: it takes an ACCESS EXCLUSIVE lock (fine for a test database), it skips ON DELETE triggers, and TRUNCATE ... CASCADE would also work but could empty tables you didn’t list. A better long-term answer for tests is to wrap each test in a transaction and roll it back, which avoids clearing tables at all.

Keep reading

• SQL interview questions and the SQL multiple-choice quiz
• PostgreSQL MVCC, old versions and vacuum
• PostgreSQL row locks and concurrent updates
• PostgreSQL ‘relation does not exist’: cause and fix
• PostgreSQL documentation: TRUNCATE