Postgresql Delete All Rows From Table

7 min read

PostgreSQL: How to Delete All Rows from a Table

Deleting every record from a PostgreSQL table is a common operation when you need to reset data, start a fresh import, or reclaim space after a massive purge. While the concept sounds simple—DELETE FROM table_name;—the reality involves understanding transaction behavior, performance considerations, and the difference between deleting rows versus truncating a table. This article walks you through the safest and most efficient methods, explains the underlying mechanics, and answers the most frequently asked questions Worth keeping that in mind..

Introduction

When you need to delete all rows from a table in PostgreSQL, you have two primary options:

  1. DELETE – a row‑by‑row operation that respects triggers, constraints, and transaction logging.
  2. TRUNCATE – a bulk operation that de‑allocates pages and is much faster, but bypasses many of the safety checks that DELETE performs.

Choosing the right tool depends on whether you need to preserve transactional integrity, rely on row‑level triggers, or simply want to reclaim space quickly. Below we explore both approaches, step by step, and provide guidance on when each is appropriate.

Step‑by‑Step Methods

1. Using DELETE

The basic syntax is:

DELETE FROM public.my_table;

Key points

  • Row‑by‑row – PostgreSQL scans the table and removes each row individually, writing an entry to the WAL (Write‑Ahead Log).
  • Triggers fire – any BEFORE or AFTER triggers defined on the table are executed for every row.
  • Foreign key constraints – if the table is referenced by other tables with ON DELETE actions, those actions are performed.
  • Transaction safety – you can roll back the operation if needed, because it runs inside a transaction.

When to use

  • You need to keep audit logs or other side‑effects that rely on triggers.
  • You want the ability to undo the deletion within the same transaction.
  • The table is relatively small, so the performance impact is acceptable.

Example with a WHERE clause

If you only want to delete rows that meet a condition, add a WHERE clause:

DELETE FROM public.orders
WHERE created_at < '2023-01-01';

Note: Omitting the WHERE clause will delete all rows, which is the focus of this article Easy to understand, harder to ignore..

2. Using TRUNCATE

The TRUNCATE command removes all rows in a single command, de‑allocating the data pages and resetting any associated sequences:

TRUNCATE TABLE public.my_table;

Key points

  • Page‑level deallocation – PostgreSQL simply marks the pages as free, which is far faster than row‑by‑row deletion.
  • No row‑level triggers – TRUNCATE bypasses BEFORE and AFTER triggers.
  • Foreign key handling – you can use CASCADE to automatically truncate dependent tables, or RESTRICT (the default) to abort if other tables reference the target.
  • Immediate and irreversible – the operation is not transactional in the same way; it commits immediately unless wrapped in a transaction block (which is allowed but still commits at the end of the block).
  • Resets sequences – if the table has a serial or identity column, the associated sequence is reset to the start value.

When to use

  • You need maximum speed and can tolerate the lack of trigger execution.
  • The table is large, and you want to reclaim space quickly.
  • You do not need to fire any business logic that lives in triggers.

3. Combining DELETE and TRUNCATE

In practice, you might start with DELETE to ensure triggers fire, then switch to TRUNCATE if you discover performance bottlenecks. On the flip side, using both in the same transaction is unnecessary and can lead to confusion Simple as that..

Scientific Explanation

How PostgreSQL Stores Data

PostgreSQL organizes data into 8 KB pages on disk. Day to day, when you insert rows, they are placed into pages. A DELETE marks each row’s tuple header with a deletion flag (the “dead tuple”) and later performs a vacuum to reclaim space Still holds up..

  • Generates WAL entries for every row change.
  • Requires row‑level locks (shared locks while scanning, exclusive locks while deleting).
  • Can cause bloat if many dead tuples accumulate before vacuum.

In contrast, TRUNCATE does not touch individual tuples. It simply adjusts the table’s pointer to the first unused page and releases the pages to the file system. This results in:

  • Minimal WAL activity (only a single entry for the whole operation).
  • Exclusive page locks during the operation, but no row‑level locks.
  • Immediate space reclamation, which can be crucial after massive deletions.

Transactional Behavior

  • DELETE runs inside a transaction. If you issue DELETE followed by ROLLBACK, the table returns to its pre‑delete state.
  • TRUNCATE can be wrapped in a transaction, but the changes are committed when the transaction ends. If you ROLLBACK after TRUNCATE, the table will be left empty because the de‑allocation is permanent once the command finishes.

Performance Considerations

Operation Speed Logging Trigger Execution Locking Space Reclaimed
DELETE Slow (scales with row count) Full WAL per row Yes Row‑level locks After vacuum
TRUNCATE Very fast (constant time) Minimal WAL No Page‑level exclusive lock Immediate

For tables with millions of rows, DELETE can take minutes or hours, while TRUNCATE finishes in seconds Turns out it matters..

Scientific Explanation (continued)

Transaction Log and Crash Recovery

PostgreSQL’s durability guarantees rely on WAL. DELETE writes a WAL record for each row removed, ensuring that, in the event of a crash, the system can replay or undo those changes. TRUNCATE writes a single WAL record describing the de‑allocation of pages; recovery simply re‑applies that record, which is safe because the pages are marked as free.

Impact on Identity/Serial Columns

When you DELETE rows, the identity or serial value continues to increment; the deleted values are not reused unless you explicitly reseed the sequence. With TRUNCATE, the sequence is automatically reset to its start value (or you can specify RESTART IDENTITY), making it ideal for resetting counters.

Locking and Concurrency

  • DELETE acquires row‑level exclusive locks on each row it modifies. This can cause contention if many sessions are deleting rows simultaneously.
  • TRUNCATE needs an exclusive lock on the table, preventing any concurrent reads or writes until the operation completes. That said, because it does not touch individual rows, the lock is held for a shorter period relative to the total number of rows.

FAQ

Q1: Can I delete all rows without affecting dependent tables?

Yes. Use DELETE (which respects foreign key `

Q1: Can I delete all rows without affecting dependent tables?

Yes. Use DELETE (which respects foreign key ON DELETE actions such as CASCADE, SET NULL, or RESTRICT). TRUNCATE will fail if any table references the target via a foreign key unless you explicitly add CASCADE, which then truncates the dependent tables as well—potentially removing far more data than intended.

Q2: Does TRUNCATE fire ON DELETE triggers?

No. Because TRUNCATE does not scan individual rows, it bypasses row‑level triggers entirely. If your application relies on trigger logic for audit trails, cascading business rules, or synchronous replication hooks, you must use DELETE (or manually invoke the logic after truncating) Worth knowing..

Q3: How do I reset an identity/serial column after a bulk delete?

After a DELETE, the sequence remains at its current high‑water mark. To reuse values, run:

ALTER SEQUENCE my_table_id_seq RESTART WITH 1;

With TRUNCATE, simply add RESTART IDENTITY:

TRUNCATE TABLE my_table RESTART IDENTITY;

Q4: Which command is safer for replication and logical decoding?

DELETE is fully captured by logical replication and pg_logical because every row change appears in the WAL stream. TRUNCATE is also replicated, but only as a single command; downstream subscribers will execute the same TRUNCATE, which is usually desirable but requires that the subscriber table has no extra rows that would be lost.

Q5: Can I use TRUNCATE inside a function or procedure?

Yes, but only if the function is declared VOLATILE (the default) and executes with sufficient privileges. Note that TRUNCATE cannot be run inside a READ ONLY transaction or a SELECT‑only function.


Conclusion

Choosing between DELETE and TRUNCATE is ultimately a trade‑off between granular control and raw efficiency.

  • Use DELETE when you need row‑level visibility, trigger execution, foreign‑key cascade behavior, or the ability to roll back a partial removal within a larger transaction.
  • Use TRUNCATE when you must wipe an entire table instantly, reclaim disk space without waiting for autovacuum, reset identity columns, and can tolerate an exclusive table lock for the brief duration of the command.

In production systems handling millions of rows, TRUNCATE is the clear winner for bulk reset operations—such as clearing staging tables, resetting test datasets, or implementing data‑retention windows—while DELETE remains the precise instrument for selective, auditable data removal. Understanding the internal mechanics (WAL volume, locking granularity, trigger semantics, and sequence handling) lets you make the right call without guessing, ensuring both performance and data integrity.

Hot New Reads

Hot off the Keyboard

People Also Read

Readers Went Here Next

Thank you for reading about Postgresql Delete All Rows From Table. We hope the information has been useful. Feel free to contact us if you have any questions. See you next time — don't forget to bookmark!
⌂ Back to Home