Delete Rows and TRUNCATE Tables

You've built tables, inserted rows, maybe even updated a few. Now the day comes when you need to remove data — and suddenly you're facing a fork in the road: DELETE or TRUNCATE? Both get rid of rows, but they behave so differently that choosing the wrong one can grind your database to a halt or let deleted data slip through a foreign key. This lesson is the mental-model upgrade that turns that decision from guesswork into a deliberate, well-informed choice — the same choice you'll make daily in production, reporting, and data cleanup.

The problem this lesson solves

When you need to remove data from a PostgreSQL table, the naive answer is always DELETE. But DELETE is not a magic wand — it's a fully logged, row-by-row operation that fires triggers, checks constraints, and can chain into foreign key cascade deletes. Use it on a 50-million-row table and you can lock the table for minutes, bloat the storage, and stall your entire application. Meanwhile, TRUNCATE — the brute-force alternative — is nearly instant but wipes the slate completely clean, and it refuses to run at all if the table is referenced by a foreign key from another table.

That's the pain: you can't just "clear a table" without knowing what's underneath. The problem is not whether you can remove rows — it's that the wrong command, applied at the wrong time, can lock your database, invalidate your foreign keys, or silently delete data you meant to keep. This lesson teaches you to see the difference clearly and pick the right tool for the job.

Core concept / mental model

Think of DELETE as surgical removal and TRUNCATE as bulldozing the lot.

• DELETE identifies which rows to remove using a WHERE clause — it's precise, can target a single row or a million, and removes them one at a time. It respects foreign keys, fires triggers, and can be rolled back if you're inside a transaction.
• TRUNCATE removes all rows from a table — and does it by deallocating entire data pages rather than deleting individual rows. It's fast, minimal, and skips most overhead, but it's a blunt instrument: no WHERE, no row-level triggers, and it cannot be used on a table that has inbound foreign key references without using the CASCADE option.

Here's a picture in words:

DELETE FROM orders WHERE order_date < '2023-01-01';
   → Scans the table, finds matches, removes each row (logs, triggers, FK checks)

TRUNCATE TABLE orders;
   → Deallocates all pages for the table in one shot → empty table, instantly

Transaction perspective: both are transactional, so you can roll them back if you wrap them in a transaction. But TRUNCATE executes a fast path that doesn't individually log each row — it logs the table-level operation. That's why it's so fast, and why it can be riskier if you're not careful.

How it works step by step

Understanding the DELETE flow

1. DELETE FROM table_name WHERE condition; is parsed and planned.
2. PostgreSQL scans the table (using an index or a sequential scan) to find rows matching the WHERE clause.
3. Each matching row is marked as deleted in the table's heap, and the deletion is written to the write-ahead log (WAL).
4. Any ON DELETE triggers on the table fire for each affected row.
5. Foreign key constraints referencing the table are checked (unless the action is NO ACTION and deferred). If a referencing row exists, the delete fails with a foreign key violation.
6. The deleted rows remain in the table file until VACUUM reclaims the space — this is normal and not a leak.

Understanding the TRUNCATE flow

1. TRUNCATE TABLE table_name; acquires an ACCESS EXCLUSIVE lock on the table (and optionally on referencing tables if CASCADE is used).
2. PostgreSQL deallocates all data pages of the table — in practical terms, the table becomes empty instantly.
3. The operation is logged as a single transaction-level event.
4. Trigger behavior: TRUNCATE does not fire row-level DELETE triggers, but it does fire BEFORE TRUNCATE and AFTER TRUNCATE statement-level triggers if they exist.
5. The table's storage is not returned to the OS; it's marked as reusable for future inserts. The disk space is freed for reuse by the same table, not necessarily returned to the filesystem.

Pro tip: Because TRUNCATE doesn't fire per-row triggers, if you have audit triggers that log each deleted row, TRUNCATE will silently skip them. Always check your triggers when choosing between the two.

The syntax you'll actually use

-- Delete specific rows
DELETE FROM users WHERE email = 'old@example.com';

-- Delete all rows (but keep the table structure)
DELETE FROM users;  -- Scans every row, fires triggers, logs each deletion

-- Empty a table instantly
TRUNCATE TABLE users;

The DELETE FROM users; without a WHERE is the slow way to clear a table; TRUNCATE is the fast way. But they're not interchangeable when foreign keys are in play.

Hands-on walkthrough

Let's make this concrete. We'll build a small demo database, insert some rows, and then experiment with both DELETE and TRUNCATE. Open a psql session and follow along.

Step 1: Create a demo schema

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_name TEXT NOT NULL,
    created_at DATE NOT NULL DEFAULT CURRENT_DATE
);

CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INT REFERENCES orders(id) ON DELETE CASCADE,
    product TEXT NOT NULL
);

Insert a few rows:

INSERT INTO orders (customer_name) VALUES
  ('Alice'),
  ('Bob'),
  ('Charlie');

INSERT INTO order_items (order_id, product) VALUES
  (1, 'Laptop'),
  (1, 'Mouse'),
  (2, 'Keyboard');

Step 2: Try TRUNCATE on a table with a dependency

-- This will fail!
TRUNCATE TABLE orders;

You'll get an error like:

ERROR:  cannot truncate a table referenced in a foreign key constraint
DETAIL:  Table "order_items" references "orders".
HINT:  Truncate table "order_items" at the same time, or use TRUNCATE ... CASCADE.

This is the key insight: you cannot truncate a table that is referenced by a foreign key from another table, unless you either truncate both tables in one command or use CASCADE.

To make it work, truncate both:

TRUNCATE TABLE orders, order_items;

Or use CASCADE to automatically truncate all dependent tables:

TRUNCATE TABLE orders CASCADE;

After that, both tables are empty. Check with:

SELECT count(*) FROM orders;       -- 0
SELECT count(*) FROM order_items;  -- 0

Step 3: Try DELETE with a WHERE clause

Re-insert the rows, then delete one specific order:

INSERT INTO orders (customer_name) VALUES ('Alice'), ('Bob');
INSERT INTO order_items (order_id, product) VALUES (1, 'Laptop');

DELETE FROM orders WHERE id = 1;

Because we defined ON DELETE CASCADE, the related row in order_items is deleted automatically. Verify:

SELECT * FROM orders;      -- only Bob's row remains
SELECT * FROM order_items; -- empty

Step 4: Compare performance (conceptually)

In a real database, try this on a table with a million rows:

-- Slow and heavy
DELETE FROM big_table;

-- Fast and light
TRUNCATE big_table;

The first might take minutes and generate gigabytes of WAL; the second takes milliseconds. But the first is reversible (inside a transaction) and respects triggers; the second is all-or-nothing.

Compare options / when to choose what

Here's a quick reference table to guide your decision:

When to choose DELETE:

• You need to remove only a subset of rows.
• You have foreign key constraints from other tables that must be respected.
• You rely on row-level triggers for auditing or cascading deletes.
• You want to keep the table's SERIAL sequence value (unless you explicitly want to reset it).

When to choose TRUNCATE:

• You need to remove all rows quickly — for example, clearing a staging table before a nightly batch.
• You don't care about row-level triggers.
• You can handle foreign key dependencies by either truncating all dependent tables together or using CASCADE.
• Performance matters more than row-level granularity.

Pro tip: To reset the identity column back to 1, add RESTART IDENTITY: TRUNCATE TABLE orders RESTART IDENTITY CASCADE;. This is fantastic for test data or seeding fresh environments.

Variation: TRUNCATE ... CONTINUE IDENTITY — the default, which leaves the identity sequence untouched. Use RESTART IDENTITY when you want a fresh start.

Another variation: DELETE with a subquery or using USING for joins — but that's beyond the scope of this beginner lesson.

Troubleshooting & edge cases

"Cannot truncate a table referenced in a foreign key constraint"

You'll see this error if you try to TRUNCATE a table that is referenced by another table. Fix: include all referencing tables in the TRUNCATE statement or use CASCADE.

-- Error
TRUNCATE TABLE parent;

-- Fix 1: truncate both
TRUNCATE TABLE parent, child;

-- Fix 2: cascade
TRUNCATE TABLE parent CASCADE;

"DELETE waits forever / lock timeout"

If your DELETE seems to hang, another session likely holds a lock on the table. Use pg_stat_activity to find blocking queries:

SELECT pid, state, wait_event_type, query
FROM pg_stat_activity
WHERE state = 'active';

Also, a long DELETE on a huge table can take forever; consider batching or using TRUNCATE if it's a full purge.

"TRUNCATE didn't fire my DELETE trigger"

That's expected — TRUNCATE only fires BEFORE TRUNCATE and AFTER TRUNCATE triggers, not row-level triggers. If you need row-level logging, you must use DELETE.

"Identity reset unexpectedly"

If you used TRUNCATE ... RESTART IDENTITY, the next insert will start at 1. If you didn't, the sequence continues as if no rows existed — which can surprise you if you expected a reset.

"Table is still empty after DELETE"

Check if you forgot the WHERE clause — an accidental DELETE FROM table; removes all rows. This is the most common destructive mistake. Always wrap destructive operations in a transaction first, verify the WHERE clause with a SELECT, then commit.

BEGIN;
DELETE FROM orders WHERE customer_name = 'Alice';
-- Inspect, then COMMIT or ROLLBACK;
COMMIT;

What you learned & what's next

Now you can confidently delete rows and truncate tables in PostgreSQL. You understand the core difference: DELETE is surgical, respects foreign keys and triggers, and can target specific rows; TRUNCATE is a lightning-fast, all-or-nothing reset that skips row-level overhead. You know how to use CASCADE and RESTART IDENTITY, you can troubleshoot the classic foreign-key error, and you know when to reach for each tool.

You've also internalized the key mental model: choosing between DELETE and TRUNCATE is a trade-off between control and speed. Keep that in mind as you move forward — the next step in your PostgreSQL journey is upserts, where you'll combine INSERT and UPDATE logic to handle data that may or may not already exist. That lesson builds on the transactional thinking you've started here, so you're ready for it.

Go ahead and practice what you've learned with a quick exercise: create a small logs table, add a few rows, then try both removing specific entries with DELETE and clearing the whole table with TRUNCATE. Experiment with what happens if you add a foreign key reference — you'll see the difference in real time.