TRUNCATE vs DELETE in SQL: Differences Across PostgreSQL, MySQL, and SQL Server

TRUNCATE and DELETE both remove rows from a table, but they do not work the same way underneath. TRUNCATE drops all rows at once by deallocating the data pages. DELETE removes rows one at a time and logs each removal individually. That difference is why TRUNCATE is faster on large tables, and it is also why the two behave differently around transactions, triggers, and foreign keys.

There is a second thing worth knowing before you reach for TRUNCATE: it is not even the same operation across databases. PostgreSQL treats it as a transactional command you can roll back. MySQL treats it as a DDL statement that commits immediately and cannot be undone. If you carry a habit from one database to another without checking, you can lose data.

TRUNCATE vs DELETE at a glance#

The speed gap is not marginal. DELETE on a 47-million-row table scans every row and writes a WAL entry for each one, which can take hours. TRUNCATE on the same table finishes in under a second because it discards the data files instead of touching individual rows.

TRUNCATE in PostgreSQL#

PostgreSQL is the most flexible of the three. It wraps TRUNCATE inside transactions, just like any other command, so you can truncate a table and roll it back if something goes wrong.

BEGIN;
TRUNCATE employees;
-- Changed your mind?
ROLLBACK;
-- All rows are still there.

You also get control over auto-increment sequences:

-- Reset the sequence back to 1
TRUNCATE orders RESTART IDENTITY;

-- Keep the current sequence value
TRUNCATE orders CONTINUE IDENTITY;

Both the row removal and the sequence reset are transactional. Roll back, and the sequence returns to its previous value too. That is unusual: even PostgreSQL's own ALTER SEQUENCE RESTART is not transactional outside of a TRUNCATE context.

For tables with foreign key references, PostgreSQL blocks TRUNCATE unless you add CASCADE:

-- This fails if other tables reference "orders"
TRUNCATE orders;

-- This truncates "orders" and any tables that reference it
TRUNCATE orders CASCADE;

CASCADE is worth a second look before you run it. It empties every table linked by a foreign key chain, and on a schema with 30+ tables, that chain can reach tables you forgot were connected.

TRUNCATE in MySQL#

Different story here. MySQL's TRUNCATE is a DDL statement that causes an implicit commit, so it cannot be rolled back.

START TRANSACTION;
TRUNCATE employees;
-- The TRUNCATE already committed. ROLLBACK does nothing here.
ROLLBACK;
-- The table is empty.

This is the one that catches people moving from PostgreSQL to MySQL. They write TRUNCATE inside a transaction block expecting the same safety net, and the data is gone the moment the statement runs. There is no warning and no error: the ROLLBACK succeeds silently and does nothing.

MySQL always resets AUTO_INCREMENT to 1 on TRUNCATE. There is no option to keep the current value, unlike PostgreSQL's CONTINUE IDENTITY.

Foreign keys are handled differently too. MySQL blocks TRUNCATE if the table is referenced by a foreign key from another table, even if the referencing table has zero rows. The usual workaround:

SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE orders;
SET FOREIGN_KEY_CHECKS = 1;

Disabling foreign key checks is a blunt instrument. If another session inserts rows referencing the truncated table while checks are off, you end up with orphaned records and no constraint violation to flag them. MySQL will not tell you about them.

TRUNCATE in SQL Server#

SQL Server lands in the middle. It allows TRUNCATE inside explicit transactions like PostgreSQL does, but it lacks the sequence control options.

BEGIN TRANSACTION;
TRUNCATE TABLE employees;
-- Safe to roll back
ROLLBACK;

The IDENTITY seed always resets. There is no equivalent to PostgreSQL's CONTINUE IDENTITY.

SQL Server is the strictest of the three about foreign keys. It blocks TRUNCATE on any table referenced by a FOREIGN KEY constraint, even when ON DELETE CASCADE is defined. There is no CASCADE option for TRUNCATE and no FOREIGN_KEY_CHECKS toggle, so your only choices are DELETE or dropping the constraint entirely.

One more edge case worth knowing: TRUNCATE also fails on tables that participate in indexed views or are published through transactional replication.

When to use TRUNCATE vs DELETE#

Reach for TRUNCATE when you are doing bulk cleanup: emptying staging tables between ETL loads, wiping test data, resetting a table after a failed migration. If the table has 10 million rows and you need all of them gone, TRUNCATE is the only practical option.

Use DELETE for everything else. When you need a WHERE clause, when you need triggers to fire, when you need to know exactly how many rows were removed, DELETE is the one that plays by the rules. It is slower, but that is the trade you are making for control.

A rule of thumb that has saved me grief: if you are writing application code that removes rows, always use DELETE. TRUNCATE belongs in migration scripts, maintenance jobs, and CI pipelines. Putting TRUNCATE in application logic is the kind of thing that comes back to bite you when someone adds a foreign key to that table six months later.

TRUNCATE and foreign keys#

All three databases block TRUNCATE when another table holds a foreign key reference. The workarounds differ, and none of them are safe to run blindly.

Each workaround carries its own catch. CASCADE can wipe tables you did not intend. Disabling foreign key checks opens a window for orphaned rows. Dropping and recreating constraints on a large schema is slow and error-prone.

When the table has foreign key dependencies, the simplest answer is usually to just use DELETE. It is slower, but it respects constraints without workarounds and without the risk of silently destroying related data.

How Bytebase prevents accidental TRUNCATE#

Running TRUNCATE on a production table by accident takes seconds. Recovering from it takes hours if you have backups, and is impossible if you don't.

Bytebase provides SQL review rules that flag or block TRUNCATE statements before they reach production. You can set policies like prohibiting TRUNCATE on production environments, requiring approval for DDL statements, or flagging CASCADE operations that touch multiple tables.

These rules run automatically when a developer submits a SQL change through Bytebase, so dangerous statements get caught during review rather than after execution. See The SQL Review Tool for Developers for how this fits into a team workflow.

FAQ#

Is TRUNCATE faster than DELETE?

Yes. TRUNCATE deallocates data pages without scanning or logging individual rows. On tables with millions of rows, TRUNCATE completes in under a second while DELETE can take minutes or hours.

Does TRUNCATE reset AUTO_INCREMENT?

Yes in all three databases. PostgreSQL gives you a choice with RESTART IDENTITY (reset) or CONTINUE IDENTITY (keep current value). MySQL and SQL Server always reset to the starting value.

Can I TRUNCATE a table with foreign keys?

Not directly. All three databases block TRUNCATE when another table references the target with a foreign key. PostgreSQL supports CASCADE to truncate referencing tables. MySQL requires disabling FOREIGN_KEY_CHECKS. SQL Server requires dropping the constraint first.

Is TRUNCATE a DDL or DML statement?

TRUNCATE is classified as DDL (Data Definition Language). DELETE is DML. This classification is what explains MySQL's implicit commit: MySQL auto-commits all DDL statements. PostgreSQL and SQL Server take a different route and allow DDL inside transactions. For more on how PostgreSQL and MySQL handle DDL transactions differently, see our detailed comparison.