SQL / INSERTING, UPDATING, AND DELETING ROWS
TRUNCATE versus DELETE and what each leaves behind
Pick DELETE or TRUNCATE on purpose, and predict what survives each one: identity counters, triggers, foreign keys, disk space and rollback.
What you will learn
- Use DELETE when you need a WHERE clause, row triggers, FK cascades, or RETURNING.
- Reach for TRUNCATE only for a full wipe, and add RESTART IDENTITY to rewind ids.
- Expect TRUNCATE to be refused while another table's foreign key points at yours.
- Know that DELETE leaves dead rows until VACUUM, while TRUNCATE frees the file.
Understanding TRUNCATE versus DELETE and what each leaves behind
DELETE FROM t with no WHERE is still ordinary row-at-a-time work: the engine visits every row, marks it dead, writes a WAL record for it, fires AFTER DELETE FOR EACH ROW triggers, and applies any ON DELETE CASCADE or SET NULL action on referencing tables. That is why it reports a row count you can trust, why RETURNING works, and why the cost grows with the number of rows. It is also why the file on disk does not shrink afterwards: the dead row versions stay there until VACUUM makes that space reusable.
TRUNCATE is not a filtered delete at all. It takes an ACCESS EXCLUSIVE lock on the table, creates a new empty file for it, and discards the old one, so the work is roughly the same whether the table held ten rows or ten million. Because it never reads a row, there is nothing for a row-level trigger or a referential action to hook into and no row to return; PostgreSQL compensates by refusing outright when another table has a foreign key pointing at yours, instead of quietly leaving dangling references behind.
The useful question is therefore not which one is faster but what each leaves behind. DELETE leaves the identity or serial counter exactly where it was, leaves dead tuples on disk, and leaves a complete audit trail from your triggers; TRUNCATE also leaves the counter untouched unless you write RESTART IDENTITY, leaves a freshly allocated file, and leaves your audit table with no idea anything happened. Both are transactional in PostgreSQL and can be rolled back, which is not portable knowledge: in MySQL InnoDB and Oracle, TRUNCATE is DDL that commits implicitly and always resets the auto-increment value.
Neither statement is a substitute for the other by default, so the choice is a checklist, not a preference.
CREATE TABLE audit_note (
id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
note text NOT NULL
);
INSERT INTO audit_note (note) VALUES ('first'), ('second'), ('third');
-- DELETE removes the rows but the identity counter keeps its place
DELETE FROM audit_note;
INSERT INTO audit_note (note) VALUES ('after delete');
SELECT id, note FROM audit_note;
-- TRUNCATE discards the storage; RESTART IDENTITY also rewinds the counter
TRUNCATE audit_note RESTART IDENTITY;
INSERT INTO audit_note (note) VALUES ('after truncate');
SELECT id, note FROM audit_note;DELETE removes rows one at a time and so runs everything attached to a row, while TRUNCATE throws away the table's storage and skips all of it, which is what makes their leftovers differ.
Worked examples
TRUNCATE will not step over a foreign key
A referential action runs for DELETE, while TRUNCATE refuses to touch a table that another table references.
CREATE TABLE team (
id integer PRIMARY KEY,
name text NOT NULL
);
CREATE TABLE player (
id integer PRIMARY KEY,
team_id integer REFERENCES team(id) ON DELETE CASCADE
);
INSERT INTO team VALUES (1, 'red'), (2, 'blue');
INSERT INTO player VALUES (10, 1), (11, 2);
DELETE FROM team WHERE id = 1;
SELECT count(*) FROM player;
TRUNCATE team;Example explained
Line 1DELETE FROM team WHERE id = 1 reads that row, so the ON DELETE CASCADE action fires and player 10 goes with it.
Line 2count(*) returns 1 because only player 11 survives, proving the cascade ran.
Line 3TRUNCATE team fails before removing anything: the check is on the existence of the foreign key, not on whether player has rows.
Line 4The deliberate fixes are TRUNCATE team, player; or TRUNCATE team CASCADE, both of which also empty player.
Rolling back a TRUNCATE in PostgreSQL
Shows that TRUNCATE is a transactional statement here, unlike in MySQL or Oracle.
CREATE TABLE reading (id integer, val numeric);
INSERT INTO reading VALUES (1, 4.5), (2, 9.1);
BEGIN;
TRUNCATE reading;
SELECT count(*) AS rows_inside_txn FROM reading;
ROLLBACK;
SELECT count(*) AS rows_after_rollback FROM reading;Example explained
Line 1TRUNCATE swaps in a brand new empty file, and that swap only becomes visible to other sessions at COMMIT.
Line 2Inside the transaction your own session already sees the empty table, so the count is 0.
Line 3ROLLBACK discards the new file and keeps the original one, so the two rows are back untouched.
Line 4Do not carry this habit to MySQL InnoDB or Oracle, where TRUNCATE commits implicitly and cannot be undone.
Row triggers never see a TRUNCATE
An AFTER DELETE row trigger logs every DELETE but records nothing when the same rows vanish via TRUNCATE.
CREATE TABLE item (id integer);
CREATE TABLE removal_log (what text);
CREATE FUNCTION log_removal() RETURNS trigger AS $$
BEGIN
INSERT INTO removal_log VALUES ('deleted ' || OLD.id);
RETURN OLD;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER item_removed AFTER DELETE ON item
FOR EACH ROW EXECUTE FUNCTION log_removal();
INSERT INTO item VALUES (1), (2);
DELETE FROM item;
SELECT what FROM removal_log ORDER BY what;
INSERT INTO item VALUES (3), (4);
TRUNCATE item;
SELECT count(*) AS log_rows FROM removal_log;Example explained
Line 1DELETE FROM item visits both rows, so log_removal() runs twice and writes one line per row.
Line 2TRUNCATE item removes rows 3 and 4 without reading them, so the FOR EACH ROW trigger is never invoked.
Line 3log_rows is still 2, meaning the audit table has no trace of the second wipe at all.
Line 4If you need a hook for this case, PostgreSQL supports AFTER TRUNCATE ... FOR EACH STATEMENT triggers.
Important notes
TRUNCATE takes an ACCESS EXCLUSIVE lock, so even a plain SELECT on that table waits for it; it is fast but not gentle on a live table.
Neither statement changes the table definition. Columns, indexes, constraints, grants and triggers all survive both, so use DROP TABLE when you want the table itself gone.
Common mistakes
Swapping DELETE for TRUNCATE in a cleanup job that has an AFTER DELETE audit trigger: the wipe succeeds, the audit table stays empty, and nothing warns you that history is now missing.
Assuming plain TRUNCATE rewinds ids because MySQL resets AUTO_INCREMENT: in PostgreSQL the identity sequence keeps counting, so the reload starts at 4001 instead of 1 unless you write RESTART IDENTITY.
Adding CASCADE just to silence the foreign key error: TRUNCATE ... CASCADE empties every referencing table too, which destroys far more than the DELETE it replaced.
Try it yourself
Change, predict, then run
Create a table with an identity column and four rows, empty it with DELETE, insert one row and note the id, then TRUNCATE ... RESTART IDENTITY, insert one row and confirm the id is 1. Repeat that TRUNCATE inside BEGIN ... ROLLBACK and check that count(*) climbs back to its old value.
Open the SQL workspaceCheck your understanding
A nightly job clears staging_order (2 million rows) and reloads it. staging_order has an AFTER DELETE FOR EACH ROW trigger that writes to order_audit, and order_line has a foreign key referencing staging_order with ON DELETE CASCADE. In PostgreSQL, replacing DELETE FROM staging_order with TRUNCATE staging_order will:
- succeed and still fill order_audit, because TRUNCATE fires the trigger once for the whole statement
- succeed, empty order_line as well, and rewind the identity counter to 1
- fail, because order_line holds a foreign key that references staging_order
- succeed much faster, leaving order_audit with no record of the 2 million removals
Show answer
PostgreSQL refuses to truncate a table that another table references with a foreign key, so the statement is rejected before any rows are removed; you would have to name order_line in the same TRUNCATE or write CASCADE. The fourth option is tempting because it correctly describes how TRUNCATE treats row triggers, but that only matters once the foreign key obstacle is cleared, and ON DELETE CASCADE does not make TRUNCATE cascade on its own.