TRUNCATE vs DELETE vs DROP: Remove Rows Fast in PostgreSQL

calendar Jun 16, 2026 / · 7 min read · postgresql database administration sql queries truncate delete maintenance postgres dba  ·
Share on: linkedin copy

TRUNCATE vs DELETE vs DROP: Remove Rows Fast in PostgreSQL

Where DELETE removes rows one logical version at a time and leaves the cleanup to vacuum, TRUNCATE discards an entire table's contents almost instantly and reclaims the space immediately, and DROP removes the table itself. Picking the wrong one for "empty this table" is a common cause of surprise WAL floods and runaway bloat.

Purpose and Overview

"Just clear the table" sounds like one operation, but PostgreSQL offers three commands with very different costs. The reason the choice matters comes down to how PostgreSQL stores data. Under multiversion concurrency control (MVCC), a DELETE does not physically remove a row — it marks each row version as deleted by stamping its xmax, leaving a dead tuple behind. Those dead tuples occupy space until vacuum reclaims them, and on a full-table delete that means writing one WAL record per row and generating dead tuples across the entire table.

TRUNCATE takes a structural shortcut. Instead of touching rows individually, it creates new, empty file storage for the table and discards the old, so it does not scan the table and does not produce per-row dead tuples. It reclaims disk space immediately rather than deferring to vacuum, which makes it dramatically faster and far lighter on WAL for the "remove everything" case. The trade-off is concurrency: TRUNCATE takes an ACCESS EXCLUSIVE lock, blocking all other access to the table for its duration.

DROP TABLE goes one step further and removes the table definition along with its data, indexes, constraints, and triggers. It is the right tool when the table itself is no longer needed — not merely its current contents. Understanding which of the three matches your actual intent is what keeps a routine cleanup from becoming an incident.

Sample Code

 1-- Remove some rows: DELETE is the only choice that filters
 2DELETE FROM events WHERE created_at < now() - interval '90 days';
 3
 4-- Empty an entire table fast, and reset its identity sequence
 5TRUNCATE TABLE staging_load RESTART IDENTITY;
 6
 7-- Empty a table and cascade to tables with FK references to it
 8TRUNCATE TABLE parent_data CASCADE;
 9
10-- Remove the table itself, contents and definition together
11DROP TABLE obsolete_report;

Notes: All four statements are transaction-safe in PostgreSQL — including TRUNCATE and DROP, which can be rolled back inside a transaction block, unlike in some other databases. RESTART IDENTITY resets owned sequences; the default CONTINUE IDENTITY leaves them untouched. CASCADE is required if other tables hold foreign-key references, and it will empty those referencing tables too — use it deliberately.

Code Breakdown

Each statement expresses a different intent, and the modifiers change behavior in ways worth knowing before running them in production.

DELETE With a Predicate

1DELETE FROM events WHERE created_at < now() - interval '90 days';

DELETE is the only one of the three that removes a subset of rows. The WHERE clause is what makes it indispensable for retention jobs and selective cleanup. The cost is proportional to the rows removed: each becomes a dead tuple, each generates WAL, and vacuum must later reclaim the space. For a large purge this is real overhead, which is why batching deletes and ensuring autovacuum keeps up both matter.

TRUNCATE With RESTART IDENTITY

1TRUNCATE TABLE staging_load RESTART IDENTITY;

TRUNCATE empties the whole table at near-constant cost regardless of row count. RESTART IDENTITY additionally resets any sequences owned by the table's identity columns back to their start values — the natural choice for a staging table that is reloaded from scratch. Without it, the default CONTINUE IDENTITY preserves the sequence position even though the rows are gone.

TRUNCATE CASCADE

1TRUNCATE TABLE parent_data CASCADE;

If foreign keys reference the target table, a plain TRUNCATE fails rather than silently break referential integrity. CASCADE tells PostgreSQL to also truncate every table that references it. This is powerful and dangerous in equal measure: it can empty far more than the named table, so confirm the dependency graph first.

DROP TABLE

1DROP TABLE obsolete_report;

DROP removes the table and everything attached to it. Add IF EXISTS to make scripts idempotent, and CASCADE to drop dependent objects such as views or foreign-key constraints. Reach for it only when the table has no future, not as a way to empty one you intend to keep.

Key Decision Factors

WAL Volume

A full-table DELETE writes WAL proportional to the number of rows, which on a large table can be a substantial, replication-straining burst. TRUNCATE writes only the small amount of WAL needed to record the file swap. When the goal is "remove everything" and WAL pressure is a concern, TRUNCATE wins decisively.

Dead Tuples and Bloat

DELETE leaves dead tuples that vacuum must reclaim; until it does, the table retains its on-disk size and queries still scan the dead space. TRUNCATE produces no dead tuples and returns space to the filesystem immediately. A repeated delete-and-reload cycle on a staging table is a classic bloat generator that TRUNCATE eliminates.

Locking and Concurrency

DELETE takes a row-level lock and allows concurrent reads and writes to the rest of the table. TRUNCATE takes an ACCESS EXCLUSIVE lock, blocking every other session — including readers — until it completes. On a table that must stay continuously available, DELETE is the only option even when it is slower.

Transaction Safety and Triggers

All three are transactional in PostgreSQL and roll back cleanly. The behavioral catch is triggers: TRUNCATE does not fire ON DELETE triggers, only ON TRUNCATE triggers. Logic that depends on per-row delete triggers — audit logging, cascade emulation — will be silently skipped by TRUNCATE, which is a correctness trap if you switch commands without checking.

Practical Applications

Reloading a Staging Table

For an ETL staging table truncated and refilled on every run, TRUNCATE ... RESTART IDENTITY is the obvious fit: instant, no bloat, sequences reset. A DELETE here would generate needless WAL and bloat on every cycle.

Time-Based Retention

Purging rows older than a retention window requires filtering, so DELETE with a WHERE clause is the only choice. Run it in batches on very large tables to bound WAL bursts and lock duration, and make sure autovacuum reclaims the freed space afterward.

Tearing Down Temporary Structures

When a feature is retired or a report table is no longer produced, DROP TABLE IF EXISTS removes it cleanly along with its indexes and constraints. Keeping an unused table around only invites confusion and wasted backups.

To reset a small cluster of FK-linked tables, TRUNCATE ... CASCADE clears the parent and its referencing tables in one consistent operation — provided you have confirmed exactly which tables the cascade will reach.

Version Compatibility

The semantics described here are long-standing. TRUNCATE has reclaimed space immediately and taken an ACCESS EXCLUSIVE lock for many major versions; RESTART IDENTITY and CASCADE options, transactional rollback of DDL, and the ON TRUNCATE trigger event are all well established and behave consistently across PostgreSQL 12 through 18. DELETE with USING and RETURNING are PostgreSQL extensions to the standard and are equally stable.

The practical differences between releases are at the margins — incremental planner and vacuum improvements that affect how quickly a large DELETE's dead tuples get reclaimed, for instance — rather than changes to which command does what. Any decision made on the WAL, bloat, locking, and trigger trade-offs above will hold across current supported versions.

Best Practices

  • Match the command to the intent — filtered removal → DELETE; empty a table you keep → TRUNCATE; remove the table entirely → DROP.
  • Batch large DELETEs — break a big purge into chunks to cap WAL bursts and lock hold time, and let autovacuum reclaim space between batches.
  • Mind the ACCESS EXCLUSIVE lock — never TRUNCATE a table that must stay readable under load; its lock blocks everyone.
  • Check for ON DELETE triggers before switching — TRUNCATE skips them; do not swap a DELETE for a TRUNCATE where per-row trigger logic matters.
  • Use CASCADE deliberately — confirm the foreign-key graph before TRUNCATE ... CASCADE or DROP ... CASCADE, because both can reach further than expected.

References

Posts in this series