# 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](https://www.postgresql.org/docs/18/routine-vacuuming.html), 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? ```sql 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](https://www.postgresql.org/docs/18/routine-vacuuming.html), 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: ```sql 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: ```sql 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](https://www.postgresql.org/docs/18/sql-truncate.html), 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](https://www.postgresql.org/docs/18/sql-truncate.html), 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: ```sql SET lock_timeout = '3s'; ``` ### It fires different triggers The [TRUNCATE documentation](https://www.postgresql.org/docs/18/sql-truncate.html), 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](https://www.postgresql.org/docs/18/sql-truncate.html) 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. ```sql 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](https://www.postgresql.org/docs/18/sql-truncate.html) 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: ```sql 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. ```sql 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: ```sql 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](https://www.postgresql.org/docs/18/ddl-partitioning.html), 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](https://www.postgresql.org/docs/18/routine-vacuuming.html), 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: ```sql 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 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. [Get started free →](https://app.dbgorilla.com/signup) ## Related - [What is table bloat in PostgreSQL and how to fix it](https://www.dbgorilla.com/learn/postgres/what-is-table-bloat-in-postgresql-and-how-to-fix-it/) - [Why is autovacuum not keeping up?](https://www.dbgorilla.com/learn/postgres/why-is-autovacuum-not-keeping-up/)