Cascade Delete Nightmare — When Deleting One Row Deletes Ten Thousand

Introduction

Why This Matters

A cascade delete is a foreign key constraint that automatically deletes related rows in child tables when a parent row is deleted. In small databases this feels convenient. In production with millions of rows, a single DELETE FROM users WHERE id = 42 can silently cascade through orders, sessions, audit logs, and analytics events — deleting tens of thousands of rows across ten tables in a single transaction that holds table locks for seconds.

When the cascade depth is deep and the data volume is large, this can cause lock timeouts, replication lag, and data loss that is impossible to undo without a point-in-time restore.

How Cascade Deletes Propagate

PostgreSQL processes ON DELETE CASCADE depth-first. A delete on the root table triggers deletes on all child tables, which trigger their own child deletes recursively:

-- Schema that creates a cascade chain
CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,
  email TEXT UNIQUE NOT NULL
);
 
CREATE TABLE orders (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT REFERENCES users(id) ON DELETE CASCADE
);
 
CREATE TABLE order_items (
  id BIGSERIAL PRIMARY KEY,
  order_id BIGINT REFERENCES orders(id) ON DELETE CASCADE
);
 
CREATE TABLE order_events (
  id BIGSERIAL PRIMARY KEY,
  order_item_id BIGINT REFERENCES order_items(id) ON DELETE CASCADE
);
 
-- This one DELETE triggers:
-- DELETE FROM order_events WHERE order_item_id IN (...)
-- DELETE FROM order_items WHERE order_id IN (...)
-- DELETE FROM orders WHERE user_id = 42
-- All inside ONE transaction holding ALL locks
DELETE FROM users WHERE id = 42;

Auditing Cascade Delete Chains in Your Schema

Query your existing cascade relationships before any bulk delete operation:

-- Find all ON DELETE CASCADE foreign keys
SELECT
  tc.table_schema,
  tc.table_name AS child_table,
  kcu.column_name AS child_column,
  ccu.table_name AS parent_table,
  ccu.column_name AS parent_column,
  rc.delete_rule
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
  ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.referential_constraints rc
  ON tc.constraint_name = rc.constraint_name
JOIN information_schema.constraint_column_usage ccu
  ON rc.unique_constraint_name = ccu.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
  AND rc.delete_rule = 'CASCADE'
ORDER BY parent_table, child_table;

Use this before running any large delete operation to understand the blast radius.

Estimating Delete Blast Radius Before Executing

Always estimate how many rows will be deleted before running:

-- Estimate rows affected by deleting user_id = 42
WITH target_user AS (
  SELECT 42::BIGINT AS user_id
),
target_orders AS (
  SELECT id FROM orders WHERE user_id = (SELECT user_id FROM target_user)
),
target_items AS (
  SELECT id FROM order_items WHERE order_id IN (SELECT id FROM target_orders)
)
SELECT
  (SELECT COUNT(*) FROM target_orders) AS orders_to_delete,
  (SELECT COUNT(*) FROM target_items) AS items_to_delete,
  (SELECT COUNT(*) FROM order_events
   WHERE order_item_id IN (SELECT id FROM target_items)) AS events_to_delete;

If the numbers are large, do not proceed with a single-transaction cascade delete.

Safe Alternative 1: Soft Deletes

Instead of physically deleting rows, mark them as deleted with a timestamp:

-- Add deleted_at column to all tables
ALTER TABLE users ADD COLUMN deleted_at TIMESTAMPTZ;
ALTER TABLE orders ADD COLUMN deleted_at TIMESTAMPTZ;
 
-- Remove the ON DELETE CASCADE constraint
ALTER TABLE orders DROP CONSTRAINT orders_user_id_fkey;
ALTER TABLE orders ADD CONSTRAINT orders_user_id_fkey
  FOREIGN KEY (user_id) REFERENCES users(id)
  ON DELETE RESTRICT; -- Prevent accidental cascade
 
-- Soft-delete function
CREATE OR REPLACE FUNCTION soft_delete_user(p_user_id BIGINT)
RETURNS VOID AS $$
BEGIN
  UPDATE users SET deleted_at = NOW() WHERE id = p_user_id;
  UPDATE orders SET deleted_at = NOW() WHERE user_id = p_user_id AND deleted_at IS NULL;
END;
$$ LANGUAGE plpgsql;

Filter soft-deleted records in your application layer:

// Query active records only
const user = await db.query(
  'SELECT * FROM users WHERE id = $1 AND deleted_at IS NULL',
  [userId]
);
 
// Or use a view that hides deleted rows
// CREATE VIEW active_users AS SELECT * FROM users WHERE deleted_at IS NULL;

Safe Alternative 2: Deferred Cleanup Jobs

For data that must eventually be physically removed, use a background job that batches deletes in small chunks:

import { CronJob } from 'cron';
 
async function cleanupDeletedUsers(batchSize = 100): Promise<void> {
  const cutoff = new Date(Date.now() - 30 * 24 * 60 * 60 * 1000); // 30 days ago
 
  // Process in small batches to avoid long-held locks
  let deleted: number;
  do {
    const result = await db.query(`
      WITH batch AS (
        SELECT id FROM users
        WHERE deleted_at IS NOT NULL
          AND deleted_at < $1
        LIMIT $2
      )
      DELETE FROM users WHERE id IN (SELECT id FROM batch)
      RETURNING id
    `, [cutoff, batchSize]);
 
    deleted = result.rowCount ?? 0;
 
    if (deleted > 0) {
      console.log(`Cleaned up ${deleted} soft-deleted users`);
      // Brief pause between batches to avoid lock pressure
      await new Promise(r => setTimeout(r, 100));
    }
  } while (deleted === batchSize);
}
 
// Run nightly at 2 AM
const job = new CronJob('0 2 * * *', cleanupDeletedUsers);
job.start();

Safe Alternative 3: Explicit Application-Level Deletes

Delete rows bottom-up through the cascade chain explicitly, in batches:

async function deleteUserAndRelatedData(userId: number): Promise<void> {
  // Bottom-up: delete leaf tables first, then work toward root
  // This avoids holding a cascade lock on all tables simultaneously
 
  // Step 1: Find affected IDs first (read-only, no locks)
  const orders = await db.query(
    'SELECT id FROM orders WHERE user_id = $1', [userId]
  );
  const orderIds = orders.rows.map((r: { id: number }) => r.id);
 
  if (orderIds.length === 0) {
    await db.query('DELETE FROM users WHERE id = $1', [userId]);
    return;
  }
 
  const items = await db.query(
    'SELECT id FROM order_items WHERE order_id = ANY($1)', [orderIds]
  );
  const itemIds = items.rows.map((r: { id: number }) => r.id);
 
  // Step 2: Delete leaf rows in chunks
  for (let i = 0; i < itemIds.length; i += 500) {
    const chunk = itemIds.slice(i, i + 500);
    await db.query(
      'DELETE FROM order_events WHERE order_item_id = ANY($1)', [chunk]
    );
    await db.query(
      'DELETE FROM order_items WHERE id = ANY($1)', [chunk]
    );
    await new Promise(r => setTimeout(r, 50)); // brief pause
  }
 
  // Step 3: Delete parent rows
  await db.query('DELETE FROM orders WHERE user_id = $1', [userId]);
  await db.query('DELETE FROM users WHERE id = $1', [userId]);
}

Common Mistakes

Not auditing cascade depth before a bulk delete. Running DELETE FROM users WHERE created_at < '2020-01-01' without knowing the cascade chain is how you delete millions of rows unintentionally.

Using ON DELETE CASCADE on audit/compliance tables. Audit logs should never be deleted as a side effect of deleting a business entity. Use ON DELETE SET NULL or ON DELETE RESTRICT instead.

No row count estimate before bulk deletes. Always estimate affected rows with a SELECT COUNT(*) equivalent before executing a large delete in production.

Cascades during high traffic. Long-running cascade deletes hold locks on multiple tables simultaneously. Always schedule large deletes during off-peak hours.

Relying on rollback as a safety net. After a cascade delete commits, the only recovery path is a point-in-time database restore. There is no undo.

Best Practices

Audit all ON DELETE CASCADE foreign keys in your schema before any bulk delete operation
Prefer soft deletes (deleted_at timestamp) for business entities that have legal or audit requirements
Use deferred background cleanup jobs for physical row removal, processing in batches of 100–500 rows
Always estimate blast radius with a dry-run SELECT COUNT(*) before executing large deletes
Use ON DELETE RESTRICT as the default for most foreign keys, requiring explicit application-level cleanup
Never cascade delete into audit log, billing, or compliance tables

Key Takeaways

A single DELETE statement on a parent table with ON DELETE CASCADE can delete tens of thousands of rows across multiple tables within one long-held transaction.
PostgreSQL processes cascade deletes depth-first, holding locks on all affected tables simultaneously for the entire transaction duration.
Auditing ON DELETE CASCADE constraints via information_schema.referential_constraints reveals the full blast radius before any bulk delete.
Soft deletes (adding a deleted_at column) eliminate the risk of unintentional cascade while preserving data for compliance and recovery.
Deferred cleanup jobs that delete in small batches (100–500 rows) are safer than single-transaction cascade deletes for large data volumes.
Audit logs, billing records, and compliance tables should use ON DELETE RESTRICT or ON DELETE SET NULL, never ON DELETE CASCADE.
After a cascade delete commits, the only recovery path is a point-in-time database restore — there is no application-level undo.
Always estimate rows-to-be-deleted with a dry-run query before executing any large delete operation in production.

Introduction#

Why This Matters#

How Cascade Deletes Propagate#

Auditing Cascade Delete Chains in Your Schema#

Estimating Delete Blast Radius Before Executing#

Safe Alternative 1: Soft Deletes#

Safe Alternative 2: Deferred Cleanup Jobs#

Safe Alternative 3: Explicit Application-Level Deletes#

Common Mistakes#

Best Practices#

Key Takeaways#

Related reading