DELETE vs TRUNCATE vs DROP — interview question

Compare DELETE, TRUNCATE and DROP TABLE in PostgreSQL.

Show the explanation

	DELETE	TRUNCATE	DROP TABLE
Removes	chosen rows (`WHERE`) or all	all rows	the table itself (structure, indexes, data)
Speed on big tables	slow — row by row	very fast	fast
Row triggers	fire	do not fire (statement triggers do)	—
Space	freed later by VACUUM	freed immediately	freed immediately
Identity reset	no	optional: `RESTART IDENTITY`	—
Rollback	yes	yes in PostgreSQL (inside a transaction)	yes in PostgreSQL

SQL

delete from sessions where last_seen < now() - interval '30 days';
truncate table staging_import restart identity;
drop table if exists old_reports;

Two PostgreSQL-specific points impress interviewers: DDL is transactional, so TRUNCATE and DROP can be rolled back inside BEGIN … ROLLBACK (unlike in some other databases); and TRUNCATE refuses to empty a table referenced by foreign keys unless you add CASCADE.

Original UniCircle question. Last reviewed 22 Oct 2026.