TRUNCATE CASCADE in Postgres: Preview the Blast Radius

TRUNCATE orders CASCADE does not mean “empty orders even if foreign keys exist.” It means “empty orders, then empty every referencing table required to make that possible, recursively.” PostgreSQL takes ACCESS EXCLUSIVE on every affected table, so the statement blocks reads, writes, and other DDL across the full cascade set until the transaction ends.

The dangerous part is the gap between the SQL you can see and the tables PostgreSQL will touch. A one-table statement can empty a large section of the database. Preview the foreign-key graph before it runs, and prefer explicit, reviewable cleanup when the real intent is narrower than that graph.

Quick reference: operations that look similar

Statement	Rows removed	Lock mode	Fires ON DELETE triggers	Disk reclaimed immediately
DELETE FROM orders WHERE ...	Matching rows	ROW EXCLUSIVE	Yes	No
DELETE FROM orders	Every row in one table	ROW EXCLUSIVE	Yes	No
TRUNCATE orders	Every row in named table and descendants	ACCESS EXCLUSIVE	No	Yes
TRUNCATE orders CASCADE	Every row in named and referencing tables	ACCESS EXCLUSIVE on each affected table	No	Yes

The official TRUNCATE documentation states that CASCADE automatically adds tables with foreign-key references. It also states that every truncated table receives ACCESS EXCLUSIVE. PostgreSQL’s lock conflict table shows that this mode conflicts with every other table-level mode.

By contrast, DELETE removes rows matching its predicate. With no WHERE clause it removes every row, but it still behaves as row deletion: it takes the normal write lock, creates dead tuples for later vacuuming, and fires ON DELETE triggers.

The migration whose real target is larger than it looks

Suppose a database has this dependency chain:

customers
  -> orders
      -> order_items
      -> payments
          -> payment_attempts

Each arrow means the child table has a foreign key to the parent. This migration names only orders:

SET lock_timeout = '2s';
SET statement_timeout = '5min';

TRUNCATE orders CASCADE;

PostgreSQL can empty orders, order_items, payments, and payment_attempts. It takes ACCESS EXCLUSIVE on all four tables. Plain SELECT queries against any of them wait, as do inserts, updates, deletes, and schema changes.

The lock acquisition itself is also a deployment risk. If one affected table has a long-running reader, the statement can wait while already holding locks acquired earlier in the operation. A short lock_timeout bounds that wait, but it cannot make the requested blast radius safe.

Preview the recursive foreign-key set first

Run this read-only query with the exact starting table before approving a cascade:

WITH RECURSIVE cascade_tables AS (
  SELECT
    'public.orders'::regclass::oid AS relid,
    0 AS depth,
    ARRAY['public.orders'::regclass::oid] AS path

  UNION ALL

  SELECT
    fk.conrelid,
    parent.depth + 1,
    parent.path || fk.conrelid
  FROM cascade_tables AS parent
  JOIN pg_constraint AS fk
    ON fk.confrelid = parent.relid
   AND fk.contype = 'f'
  WHERE NOT fk.conrelid = ANY(parent.path)
)
SELECT DISTINCT ON (relid)
  relid::regclass AS affected_table,
  depth
FROM cascade_tables
ORDER BY relid, depth;

The starting table appears at depth 0. Direct referencing tables appear at depth 1, and tables reached through those children appear at larger depths. The path check prevents a foreign-key cycle from making the recursive query loop forever.

Review the result as a data-deletion manifest. For each table, answer three questions:

1. Is emptying this table actually intended?
2. Does any application still read or write it?
3. Is there a backup or retained copy that has been tested for recovery?

If any answer is unclear, the migration is not ready.

TRUNCATE is transactional, but commit is the point of no return

PostgreSQL can roll back TRUNCATE before the surrounding transaction commits:

BEGIN;

SET LOCAL lock_timeout = '2s';
SET LOCAL statement_timeout = '5min';

TRUNCATE orders CASCADE;

-- Inspect counts in this transaction, then choose one:
ROLLBACK;
-- COMMIT;

That rollback property is useful, but it is not a recovery plan. While the transaction stays open, the ACCESS EXCLUSIVE locks stay open too. A human inspection pause after TRUNCATE can therefore block the affected application tables for as long as the pause lasts.

Once committed, ordinary rollback is gone. Recovery then depends on a backup, point-in-time recovery, a delayed replica, or another retained copy. Test that path before a destructive production change, not after it.

TRUNCATE also does not fire ON DELETE triggers. If those triggers perform audit, cleanup, counters, or external synchronization, replacing DELETE with TRUNCATE changes behavior in addition to changing speed.

Safer option: explicit batched deletion

If the actual requirement is to remove old orders while keeping current ones, express that requirement in the predicate and delete in committed batches:

WITH batch AS (
  SELECT ctid
  FROM orders
  WHERE created_at < DATE '2024-01-01'
  ORDER BY id
  LIMIT 1000
  FOR UPDATE SKIP LOCKED
)
DELETE FROM orders AS o
USING batch
WHERE o.ctid = batch.ctid;

Run one batch at a time from an out-of-band job, commit, observe replica lag and application latency, then repeat. This keeps each transaction bounded and makes progress resumable.

Foreign-key actions still matter. ON DELETE CASCADE can remove child rows for every parent row in a batch, while restrictive foreign keys can reject the delete. When the cleanup intentionally spans several tables, delete in an explicit order and make each table visible in the plan. The migration review should show the same blast radius that production will see.

If the requirement truly is to empty a staging or disposable table immediately, plain TRUNCATE without CASCADE may be correct. Name every table explicitly and use the default RESTRICT behavior so an unexpected dependency fails the command instead of expanding it.

SET lock_timeout = '2s';
SET statement_timeout = '5min';

TRUNCATE order_items, payment_attempts, payments, orders;

This is still destructive and still takes ACCESS EXCLUSIVE, but the reviewable SQL now matches the intended target set.

What pgfence reports

pgfence classifies the cascade as critical and names both the data and lock blast radius:

npx @flvmnt/pgfence explain "TRUNCATE orders CASCADE"

Statement:
  TRUNCATE orders CASCADE;

[CRITICAL] truncate-cascade
  TRUNCATE "orders" CASCADE: deletes all rows in this table AND all referencing tables via foreign keys, acquires ACCESS EXCLUSIVE on all affected tables
  Lock: ACCESS EXCLUSIVE
  Blocks: reads, writes, other DDL

  Safe rewrite:
  Remove CASCADE and explicitly truncate each table, or use batched DELETE
    -- Delete in batches out-of-band, table by table:
    -- DELETE FROM orders WHERE ctid IN (SELECT ctid FROM orders LIMIT 1000);

The same rule family flags plain TRUNCATE, DELETE without a WHERE clause, and provably tautological predicates such as WHERE true or WHERE 1 = 1. Static analysis cannot confirm that a broad but nontrivial predicate matches the rows you intended, so destructive migrations still require a data review.

Review checklist

1. Generate the recursive foreign-key target set before approving CASCADE.
2. Prefer RESTRICT and explicit table names when the complete target set is known.
3. Confirm trigger differences before replacing DELETE with TRUNCATE.
4. Keep inspection and approval outside the transaction that holds ACCESS EXCLUSIVE.
5. Test recovery from the retained copy before committing destructive production work.

Related

• TRUNCATE CASCADE lock and rewrite reference
• The lock_timeout death spiral
• Postgres lock mode cheat sheet
• Migration policy checks