Why is DELETE so much slower than TRUNCATE in PostgreSQL?

Quick answer: DELETE does per-row work and leaves the rows on disk for vacuum. TRUNCATE empties the whole table in one operation and gives the space back at commit. On a 5,000,000 row table, PostgreSQL 18.6 in container dbg-pgdel on 2026-09-20, that was 7,457.332 ms against 196.794 ms, and 812 MB of write-ahead log against 4.26 MB. They are also not interchangeable: TRUNCATE needs an exclusive lock, skips row triggers, needs its own privilege, and is not safe for transactions whose snapshot predates it.

What was measured, and where

Every number on this page was produced on 2026-09-20 on PostgreSQL 18.6 in Docker container dbg-pgdel, on an 8 vCPU Linux 6.17.0 host under Docker 29.1.3, with default settings. The table is ord: 5,000,000 rows, a bigserial primary key and three secondary indexes, 982 MB in total, of which 521 MB is the heap and 461 MB the four indexes. Two byte-identical copies were made so the delete and the truncate each got a fresh one.

What is the problem?

The two statements look like they do the same thing and they do not.

on an identical 982 MB, 5,000,000 row copy, PostgreSQL 18.6, container dbg-pgdel, 2026-09-20	time	WAL records	WAL bytes	size after
DELETE FROM ord_a;	7,457.332 ms	5,000,000	812,523,922	982 MB
plain VACUUM ord_a; afterwards	9,552.861 ms	376,350	326,751,921	461 MB
TRUNCATE ord_b;	196.794 ms	not reported per statement	4,256,952	40 kB

PostgreSQL 18.6, container dbg-pgdel, 2026-09-20. The delete figures come from EXPLAIN (ANALYZE, BUFFERS, WAL); the truncate’s WAL is the difference in pg_current_wal_lsn() across the statement, because TRUNCATE cannot be explained.

The delete also leaves work behind, and two columns show it in different ways, all of it PostgreSQL 18.6 in container dbg-pgdel on 2026-09-20. In the time column the vacuum afterwards takes longer than the delete itself did, 9,552.861 ms against 7,457.332 ms. In the WAL bytes column the delete is the larger of the two, 812,523,922 against the vacuum’s 326,751,921, and the two together are the cost of the path, because the cleanup writes a second time after the delete. The truncate is one statement that writes 4,256,952 bytes, and nothing follows it.

What the delete actually did per row

The WAL line, PostgreSQL 18.6 in container dbg-pgdel on 2026-09-20, shows 5,000,000 records for 5,000,000 rows, one each. Nothing was written for the four indexes. Most descriptions of DELETE get this part wrong. A delete marks the row version dead and stops. The index entries still point at it.

The index work arrives later, in vacuum, and the vacuum log names it. These lines are excerpts, with trailing fields trimmed:

INFO:  table "ord_a": truncated 66667 to 0 pages
pages: 66667 removed, 0 remain, 66667 scanned (100.00% of total)
tuples: 5000000 removed, 0 remain, 0 are dead but not yet removable
index scan needed: 66667 pages from table (100.00% of total) had 5000000 dead item identifiers removed
index "ord_a_pkey": pages: 24288 in total, 24284 newly deleted, 24284 currently deleted, 0 reusable
WAL usage: 376350 records, 128410 full page images, 326751921 bytes

PostgreSQL 18.6, container dbg-pgdel, 2026-09-20.

Does a plain DELETE give the space back?

Partly, and the answer depends entirely on where the deleted rows were.

The PostgreSQL 18 routine vacuuming documentation, read 2026-09-20, states the rule: “The standard form of VACUUM removes dead row versions in tables and indexes and marks the space available for future reuse. However, it will not return the space to the operating system, except in the special case where one or more pages at the end of a table become entirely free and an exclusive table lock can be easily obtained.”

Both halves of that were reproduced.

case, PostgreSQL 18.6, container dbg-pgdel, 2026-09-20	heap pages after plain VACUUM	total size after
every row deleted, so every page is free	66,667 down to 0	982 MB down to 461 MB
50,000 of 5,000,000 deleted from the old end	66,667, unchanged	982 MB, unchanged

Delete everything and plain vacuum hands the entire heap back. Delete a slice and it hands back nothing, because the free pages are not at the end. On PostgreSQL 18.6 in container dbg-pgdel on 2026-09-20 the free space inside the relation went from 0.83% to 1.78% and the file did not move.

In the first row, 461 MB did not come back. On PostgreSQL 18.6 in container dbg-pgdel on 2026-09-20 the heap went to 0 bytes and the four indexes stayed exactly where they were, at 190 MB, 192 MB, 48 MB and 31 MB, all of them empty. Emptying an index does not shrink it, and getting that space back takes a rebuild.

How do I confirm it on my own table?

SELECT relname,
       n_live_tup,
       n_dead_tup,
       last_vacuum,
       last_autovacuum
FROM pg_stat_user_tables
WHERE relname = 'orders';

Treat those two counts as a trend, not a measurement. The PostgreSQL 18 routine vacuuming documentation, read 2026-09-20, says of them: “The number of obsolete tuples and the number of inserted tuples are obtained from the cumulative statistics system; it is an eventually-consistent count updated by each UPDATE, DELETE and INSERT operation.”

The eventual consistency shows up in practice. On PostgreSQL 18.6 in container dbg-pgdel on 2026-09-20, after 50,000 rows of 5,000,000 were deleted and plain VACUUM had run, n_live_tup read 5,000,025 for a table holding 4,950,000 rows. The vacuum had scanned 1.00% of the pages and the estimate came out higher than the count before the delete.

For bytes rather than a trend, ask the page level directly:

CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT table_len, dead_tuple_count, dead_tuple_len, dead_tuple_percent, free_percent
FROM pgstattuple('orders');

Immediately after the 5,000,000 row delete, and before any vacuum, that returned a table_len of 546,136,064 bytes, 5,000,000 dead tuples occupying 485,000,000 bytes, 88.81% dead and 0.83% free: PostgreSQL 18.6, container dbg-pgdel, 2026-09-20. It is a full scan of the table, so it is a diagnostic rather than something to poll.

And the WAL cost of a delete before you run it:

EXPLAIN (ANALYZE, BUFFERS, WAL)
DELETE FROM orders WHERE status = 'pending';

The WAL: line is the write-ahead log attributable to that plan. It is a major input to replication and archiving pressure and it is not a prediction of replication lag, which also depends on everything else generating WAL, on the standby’s apply rate, and on the network between them.

TRUNCATE is not a faster DELETE

It is a different statement with different semantics, and six of them decide whether you can use it.

It is not MVCC-safe

This is the one that surprises people, and the PostgreSQL 18 TRUNCATE documentation, read 2026-09-20, says it plainly: “TRUNCATE is not MVCC-safe. After truncation, the table will appear empty to concurrent transactions, if they are using a snapshot taken before the truncation occurred.”

Reproduced both ways on PostgreSQL 18.6 in container dbg-pgdel on 2026-09-20, with a 100 row table and a REPEATABLE READ transaction that took its snapshot first:

what the other session committed	what the older snapshot then saw
DELETE FROM mv_del;, all 100 rows	100
TRUNCATE mv_t2;	0

The delete is invisible to the old snapshot, which is what MVCC promises. The truncate is not. A long-running report that started before your truncate does not see the data it was reading.

It takes an ACCESS EXCLUSIVE lock, and waits for one

The same TRUNCATE documentation, read 2026-09-20, says: “TRUNCATE acquires an ACCESS EXCLUSIVE lock on each table it operates on, which blocks all other concurrent operations on the table.”

Both directions of that were measured on PostgreSQL 18.6 in container dbg-pgdel on 2026-09-20:

situation	result
TRUNCATE while an idle transaction held only AccessShareLock from a SELECT	waited, wait_event_type Lock, wait_event relation
TRUNCATE with lock_timeout = '2s' behind an open DELETE	ERROR: canceling statement due to lock timeout at 2,000.790 ms
plain SELECT count(*) with lock_timeout = '3s' behind a held TRUNCATE	ERROR: canceling statement due to lock timeout at 3,000.501 ms
plain SELECT count(*) while a DELETE of 3,281,333 rows sat uncommitted	4,950,000 rows in 485.283 ms

That last row is the one to keep. A large DELETE does not block readers. A TRUNCATE blocks everything, and it can be blocked in turn by a session that is doing nothing but holding a transaction open. Set lock_timeout before a planned truncate so it fails instead of forming a queue:

SET lock_timeout = '3s';

It fires different triggers

The TRUNCATE documentation, read 2026-09-20: “TRUNCATE will not fire any ON DELETE triggers that might exist for the tables. But it will fire ON TRUNCATE triggers.”

Measured on PostgreSQL 18.6 in container dbg-pgdel on 2026-09-20, with a table carrying an AFTER DELETE ... FOR EACH ROW trigger and both BEFORE TRUNCATE and AFTER TRUNCATE statement triggers: the DELETE of four rows fired the row trigger four times and neither truncate trigger. The TRUNCATE fired each statement trigger once and the row trigger not at all.

If a per-row AFTER DELETE trigger is what keeps an audit table or a summary correct, swapping in a truncate silently stops maintaining it.

It needs its own privilege

“You must have the TRUNCATE privilege on a table to truncate it”, per the TRUNCATE documentation read 2026-09-20. Measured on PostgreSQL 18.6 in container dbg-pgdel on 2026-09-20: a role granted SELECT, DELETE was allowed to run the delete and got ERROR: permission denied for table parent on the truncate.

It does not touch sequences unless you ask

CONTINUE IDENTITY is the default. Measured on PostgreSQL 18.6 in container dbg-pgdel on 2026-09-20, on a table whose bigserial had reached 5: after a plain TRUNCATE the sequence still read 5 and the next insert got id 6. After TRUNCATE ... RESTART IDENTITY the sequence read 1 with is_called false and the next insert got id 1.

TRUNCATE orders RESTART IDENTITY;

A staging reset usually wants that. A production cleanup usually must not have it, because ids that come back around collide with anything that recorded them.

Foreign keys decide which tables you must name

“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”, per the TRUNCATE documentation read 2026-09-20. The rule is recursive, which the error messages show. On PostgreSQL 18.6 in container dbg-pgdel on 2026-09-20, with gc referencing child referencing parent:

statement	result
TRUNCATE parent;	ERROR: cannot truncate a table referenced in a foreign key constraint, DETAIL: Table "child" references "parent".
TRUNCATE parent, child;	the same error, naming gc and child
TRUNCATE parent, child, gc;	succeeded, all three emptied
TRUNCATE parent CASCADE;	succeeded, with NOTICE: truncate cascades to table "child" and NOTICE: truncate cascades to table "gc"

CASCADE walks the reference graph and empties what it finds, and the two notices above are the only warning you get.

What it does keep is rollback

Measured on PostgreSQL 18.6 in container dbg-pgdel on 2026-09-20: a TRUNCATE inside a transaction showed that transaction 0 rows, and after ROLLBACK the 5 original rows were back. This is a real difference from MySQL, where TRUNCATE commits implicitly, and it is the assumption most often carried across wrongly.

Deleting some rows

When you need a subset there is no alternative to DELETE. The question is how to bound it.

Batch on the column your predicate uses

The batching query below looks reasonable and is a trap, as its own plan shows. Measured on PostgreSQL 18.6 in container dbg-pgdel on 2026-09-20, on a 5,000,000 row copy where 632,933 rows matched the retention cutoff:

DELETE FROM orders
WHERE id IN (
    SELECT id FROM orders
    WHERE created_at < '2026-08-01'
    ORDER BY id
    LIMIT 10000
);

batch	execution time	rows discarded by the filter
1	1,170.967 ms	4,347,067
2	1,108.789 ms	4,347,067
3	1,112.993 ms	4,347,067
4	1,078.558 ms	4,347,067
5	1,072.830 ms	4,347,067

ORDER BY id makes the planner walk the primary key and apply created_at as a filter, so on PostgreSQL 18.6 in container dbg-pgdel on 2026-09-20 every batch re-read and discarded the same 4,347,067 rows. The cost does not grow, but every batch pays it again in full.

Order by the retention key instead and the same batch of 10,000 rows took 35.146 ms with an index scan on created_at and nothing discarded: PostgreSQL 18.6, container dbg-pgdel, 2026-09-20.

DELETE FROM orders
WHERE id IN (
    SELECT id FROM orders
    WHERE created_at < '2026-08-01'
    ORDER BY created_at
    LIMIT 10000
);

Checking that the predicate uses an index is not enough. Run EXPLAIN (ANALYZE, BUFFERS) on one batch and look at Rows Removed by Filter. A large number there means every batch is paying for the whole table.

Batching bounds how long row locks are held, how large a WAL burst gets, and how much a failure rolls back. It does not guarantee that autovacuum runs between batches or that it can remove anything when it does, because vacuum is driven by thresholds and by the oldest snapshot in the system rather than by your loop.

Index the columns that reference the table

This is the single largest number on this page. Deleting 1,000 parent rows that had no children at all, on PostgreSQL 18.6 in container dbg-pgdel on 2026-09-20, against a 2,000,000 row referencing table:

index on the referencing column	statement time	foreign key trigger time
none	109,346.157 ms	109,344.212 ms, 1,000 calls
CREATE INDEX kid_mum_idx ON kid (mum_id);	13.044 ms	12.024 ms, 1,000 calls

Read where the time is. In both runs the Delete node itself took about a millisecond and the plan looked identical. The cost is in a line underneath the plan that is easy to scroll past:

 Trigger for constraint kid_mum_id_fkey: time=109344.212 calls=1000

A plan that looks fast and a statement that takes two minutes is what a missing index on a referencing column looks like. Find them:

SELECT conrelid::regclass AS referencing_table,
       conname,
       pg_get_constraintdef(oid) AS definition
FROM pg_constraint
WHERE confrelid = 'orders'::regclass
  AND contype = 'f';

For each one, confirm an index exists on the referencing column.

Partitions are the cheap answer to retention

If rows age out on a schedule, dropping partitions is cheaper than deleting rows. The PostgreSQL 18 partitioning documentation, read 2026-09-20, says: “Dropping an individual partition using DROP TABLE, or doing ALTER TABLE DETACH PARTITION, is far faster than a bulk operation. These commands also entirely avoid the VACUUM overhead caused by a bulk DELETE.”

The lock is the part to plan. Measured on PostgreSQL 18.6 in container dbg-pgdel on 2026-09-20, on a three-partition table:

statement	locks taken
DROP TABLE ev_2026_07;	AccessExclusiveLock on the parent ev
ALTER TABLE ev DETACH PARTITION ev_2026_07;	AccessExclusiveLock on the parent and on the partition
ALTER TABLE ev DETACH PARTITION ev_2026_07 CONCURRENTLY;	no relation lock at all while it waited

The concurrent form behaves differently enough to be worth watching once. The same partitioning documentation, read 2026-09-20, says: “Adding the CONCURRENTLY qualifier as in the second form allows the detach operation to require only SHARE UPDATE EXCLUSIVE lock on the parent table, but see ALTER TABLE ... DETACH PARTITION for details on the restrictions.”

What it did on PostgreSQL 18.6 in container dbg-pgdel on 2026-09-20 was wait on a virtualxid lock, held by an ordinary open transaction, while holding no lock on either table. During that wait a SELECT count(*) on the parent returned in 11.238 ms and an INSERT into it in 5.318 ms. The command waits for older transactions to end without blocking the table in the meantime.

One consequence to know before you run it: during that wait the parent’s own count already excluded the detaching partition, 196,647 of 300,000 rows on PostgreSQL 18.6 in container dbg-pgdel on 2026-09-20.

Why does AI-generated code cause this?

Two habits cause it.

The first is reaching for TRUNCATE as a faster DELETE. It is faster, but the six sections above are the other differences that come with it. In a cleanup script that once deleted a subset and now truncates, the data loss is silent and total.

The second is the unbatched archival delete. DELETE FROM events WHERE created_at < now() - interval '1 year' is exactly what was asked for. On a large table it writes enormous WAL, leaves every deleted row on disk until vacuum, and gives back no space at all unless the rows happened to be at the physical end.

How do I stop it coming back?

Partition anything with a retention rule, and expire by dropping or detaching partitions rather than deleting rows.

For a table you empty on a schedule, use TRUNCATE. The routine vacuuming documentation, read 2026-09-20, recommends it directly: “If you have a table whose entire contents are deleted on a periodic basis, consider doing it with TRUNCATE rather than using DELETE followed by VACUUM.”

Vacuum after a large delete rather than waiting for the autovacuum threshold, which scales with table size:

VACUUM (ANALYZE) orders;

Expect it to make space reusable and, only when the free pages are at the end of the table, to give some back. For internal bloat you need VACUUM FULL, which takes an ACCESS EXCLUSIVE lock and rewrites the table, or pg_repack, which does the same job without the long lock. Neither is free, and empty indexes need rebuilding whichever you choose.

Set lock_timeout before any truncate or partition DDL, so a blocked statement fails rather than queueing everything behind itself.

Index every column that references a table you delete from.

How DBGorilla helps

DBGorilla can help

DBGorilla connects read-only and gives your AI coding agent the live and dead row counts for the table as a time series, the last autovacuum time, the table and index sizes, and the per-table insert, update and delete counts, so it can show that a delete left dead rows behind and whether vacuum has caught up yet.

Start free

FAQ

• Why is DELETE so much slower than TRUNCATE in PostgreSQL?

  Because DELETE does work for every row and TRUNCATE does not. Measured on PostgreSQL 18.6 in container dbg-pgdel on 2026-09-20, deleting all 5,000,000 rows of a 982 MB table took 7,457.332 ms and wrote 5,000,000 WAL records and 812,523,922 bytes. TRUNCATE on an identical copy took 196.794 ms and 4,256,952 bytes of WAL. The delete also left every row on disk for vacuum to deal with later.

• Does TRUNCATE free disk space immediately?

  Yes. Measured on PostgreSQL 18.6 in container dbg-pgdel on 2026-09-20, an identical 982 MB copy of the table was 40 kB after TRUNCATE, indexes included. A DELETE of every row left the table at 982 MB until vacuum ran. The PostgreSQL 18 TRUNCATE documentation, read 2026-09-20, puts it as reclaiming disk space immediately rather than requiring a subsequent VACUUM.

• Does a plain DELETE ever give space back?

  Sometimes, and only from the end of the table. Measured on PostgreSQL 18.6 in container dbg-pgdel on 2026-09-20: after deleting every row, plain VACUUM truncated 66,667 heap pages to 0 and the heap went to 0 bytes, but the four indexes stayed at 461 MB. After deleting 50,000 rows of 5,000,000 the same plain VACUUM left the table at 982 MB and 66,667 pages.

• Can I roll back a TRUNCATE in PostgreSQL?

  Yes. Measured on PostgreSQL 18.6 in container dbg-pgdel on 2026-09-20, a TRUNCATE inside a transaction showed 0 rows to that transaction and the 5 rows were back after ROLLBACK. What rollback does not give you is MVCC safety for other sessions: a REPEATABLE READ transaction whose snapshot predated the truncate saw 0 rows, where the same test with DELETE showed it the original 100.

• How do I delete millions of rows without locking the table?

  A DELETE does not block readers, so the goal is bounded transactions rather than avoiding a lock. Measured on PostgreSQL 18.6 in container dbg-pgdel on 2026-09-20, an uncommitted DELETE of 3,281,333 rows let a concurrent count finish in 485.283 ms. Batch on the column your retention predicate uses: the same 10,000 row batch took 1,170.967 ms ordered by id and 35.146 ms ordered by created_at.

Related

• What is table bloat in PostgreSQL and how to fix it
• Why is autovacuum not keeping up?