TRUNCATE is a table reset (fast, whole-table); DELETE is row removal (WHERE-able) — and whether TRUNCATE respects transactions is an engine property.

'Delete everything' has two tools with different contracts. DELETE without WHERE is slow on big tables but boringly predictable: row-wise, transactional everywhere, fires triggers. TRUNCATE is a storage operation wearing DML clothes: near-instant, but its side contracts vary by engine — and the dangerous variance is transactionality, because delete-and-reload jobs safe on Postgres become unrecoverable on MySQL the day someone 'optimizes' DELETE into TRUNCATE inside the transaction.

BEGIN;

TRUNCATE t;

ROLLBACK;

SELECT
  COUNT(*) AS n
FROM
  t;

BEGIN;

DELETE FROM t;

ROLLBACK;

SELECT
  COUNT(*) AS n
FROM
  t;

In DuckDB the TRUNCATE inside BEGIN…ROLLBACK restored the rows, but whether TRUNCATE respects transactions is an engine property — on MySQL/Oracle it auto-commits and the rollback can't bring the rows back; DELETE is transactional everywhere.

TRUNCATE for standalone resets where its contract is known; DELETE/swap patterns inside transactional batches.

DuckDB/Postgres/SQL Server: TRUNCATE transactional; MySQL/Oracle: implicit commit, not rollbackable; TRUNCATE may reset identity and skip row triggers.