Truncate table vs delete from table

Many database management systems, such as PostgreSQL, MySQL, Oracle and others have two ways of cleaning up a table: DELETE FROM tblname statement and TRUNCATE [TABLE] tblname (SQLite does not have a dedicated TRUNCATE statement, but in fact when doing DELETE FROM without WHERE clause it runs special optimization pretty much similar to TRUNCATE in other DBMS).

I this post I’m going to show how it works under the hood on PostgresSQL example. First, let’s check official documentation on TRUNCATE statement:

TRUNCATE quickly removes all rows from a set of tables. It has the same effect as an unqualified DELETE on each table, but since it does not actually scan the tables it is faster. Furthermore, it reclaims disk space immediately, rather than requiring a subsequent VACUUM operation. This is most useful on large tables.

and in the same file below:

TRUNCATE cannot be used on a table that has foreign-key references from other tables, unless all such tables are also truncated in the same command. Checking validity in such cases would require table scans, and the whole point is not to do one.

Time comparison

First I’m going to compare speeds on the demo stand.

I use docker to start postgres:

docker run \
	--name=postgres \
	--volume $(pwd)/data:/var/lib/postgresql/data \
	--env POSTGRES_HOST_AUTH_METHOD=trust \
	--detach \
	postgres:13.2

and then run psql shell:

docker exec \
	--interactive \
	--tty \
	postgres \
	psql --username postgres

Then I create a simple test schema containing one table

CREATE TABLE people (
	id serial PRIMARY KEY,
	email varchar(255) NOT NULL,
	age int
);

CREATE UNIQUE INDEX people_email ON people(email);

and fill it with some dummy data using a stored procedure. Here’s the code I use:

CREATE OR REPLACE FUNCTION random_between(low INT ,high INT)
   RETURNS INT AS
$$
BEGIN
   RETURN floor(random()* (high-low + 1) + low);
END;
$$ language 'plpgsql' STRICT;

INSERT INTO people(email, age)
SELECT
	md5(RANDOM()::TEXT) || '@example.com',
	random_between(10, 100)
FROM generate_series(1, 1000000);

First I delete using DELETE FROM statement (I use \timing on psql command to measure runtime):

postgres=# DELETE FROM people;
DELETE 1000000
Time: 17677.917 ms (00:17.678)

Now same with TRUNCATE (refilled the table with data previously of course):

postgres=# TRUNCATE TABLE people;
TRUNCATE TABLE
Time: 1308.537 ms (00:01.309)

The difference is massive: ~17s vs ~1.3s, TRUNCATE is more than 10x faster. Why is that? Let’s first dig a bit into PostgreSQL file layout.

PostgreSQL file layout

First i’m going to query pg_database system catalog to get the database’s object identifier.

postgres=# select oid, datname from pg_database;
  oid  |  datname
-------+-----------
 13395 | postgres
     1 | template1
 13394 | template0
(3 rows)

The default database postgres has the object identifier 13395.

Now for the sake of demonstration, I’m recreating the table because I already truncated it once when was measuring time:

DROP TABLE people;

CREATE TABLE people (
	id serial PRIMARY KEY,
	email varchar(255) NOT NULL,
	age int
);
CREATE UNIQUE INDEX people_email ON people(email);

Now I am querying another internal PostgreSQL catalogue with table metadata - pg_class to fetch the table’s oid and also I am interested in one more attribute - relfilenode.

postgres=# select oid, relname, relfilenode from pg_class where relname = 'people';
  oid  | relname | relfilenode
-------+---------+-------------
 16400 | people  |       16400
(1 row)

Here’s oid is equal relfilenode. Now we can try to find the data on filesystem using location ${PGDATA}/<db-oid>/<table-oid>:

$ ls -l data/base/13395/16400
-rw-------  1 mzh  staff     0B 13 Mar 13:24 data/base/13395/16400

Now I’m going to truncate the table and list the file again

postgres=# TRUNCATE people;
TRUNCATE TABLE

$ ls -l data/base/13395/16400
ls: data/base/13395/16400: No such file or directory

and there’s no such file. To understand what happened let’s get relfilenode again:

postgres=# select oid, relname, relfilenode from pg_class where relname = 'people';
  oid  | relname | relfilenode
-------+---------+-------------
 16400 | people  |       16407
(1 row)

We can see here that while the table’s oid remains the same (it always do) but relfilenode is different, and if I use as a table’s filename:

$ ls -l data/base/13395/16407
-rw-------  1 mzh  staff     0B 13 Mar 13:56 data/base/13395/16407

Voila - here’s our data file. This demonstrated that in PostgreSQL table is a logical unit backed by “physical” file on a real filesystem that can change during the table lifetime (apart from TRUNCATE it also happens when one use VACUUM FULL on a table).

The difference

So DELETE FROM operation acts like any DML modification statement: performs a full scan, adds tombstones on every record to support MVCC, maintains heap files and indexes, runs triggers, preserving exactly the same table.

In contrast TRUNCATE simply allocates a new file and deletes the old one - a pretty simple and quick operation. In fact, TRUNCATE is logically equivalent to DROP TABLE + CREATE TABLE combo, but the later will also reset the table’s metadata (such as autoincrement key sequences for example).

Should I use truncate?

If you can - why not. But remember that it has some limitations: in the case of PostgreSQL for example TRUNCATE is not MVCC-safe, which makes it barely useful in OPTL applications, but might be useful in some sort of integration tests to clean up the database after test suite run.

Other databases have similar limitations, check the docs for details.