DEV Community

Foreign Key Cascade Deletes: Un-Indexed FKs, Slow Cascades, and Lock Contention in PostgreSQL

Running a DELETE on a parent entity with ON DELETE CASCADE feels like clean relational cleanup. But if any child or grandchild table in the dependency chain lacks an index on its foreign key column, PostgreSQL falls back to full sequential scans on every cascade step, turning a quick delete into a long-running transaction that holds row locks and starves concurrent writes.

Here is an architectural deep dive into what actually happens during an un-indexed cascade, and how to build an asynchronous soft-delete purge pipeline.

In multi-tenant relational design, enforcing referential integrity with foreign keys (FOREIGN KEY ... REFERENCES ... ON DELETE CASCADE) is foundational. But when a high-throughput B2B SaaS platform purges a top-level tenant or bulk-deletes historical entities, cascading deletes can turn into a performance trap, not because PostgreSQL escalates to table locks, but because a missing index on a foreign key column forces a full sequential scan on every cascade step, stretching a quick delete into a long-running, lock-holding transaction.

1. The Mechanics of Un-Indexed Foreign Key Cascades

When an application deletes a parent row (say, a row in tenants), PostgreSQL must find and delete every referencing row in child and grandchild tables (users, invoices, audit_logs, api_tokens, and so on) to satisfy the foreign key constraints.

The Un-Indexed Foreign Key Problem

PostgreSQL automatically indexes primary keys and unique constraints, but it does not create an index on the referencing (child) side of a foreign key. You have to add that index yourself. When it is missing:

  • Sequential Child Scans: To find the child rows that point at the parent being deleted, PostgreSQL has no index to probe, so it runs a full sequential scan of the child table, once per cascade step, repeated down the hierarchy.
  • A Long-Running Transaction, Not a Table Lock: This is the point most write-ups get wrong. PostgreSQL does not escalate row locks to a table-level ShareLock on the child table, and it has no lock-escalation mechanism at all. The cascade holds the ordinary RowExclusiveLock on each table it writes, exactly as any DELETE does, and RowExclusiveLock does not conflict with other writers. What actually hurts is duration: the sequential scans make the deleting transaction run for seconds, and for that whole time it holds row locks on every row it is deleting and FOR KEY SHARE locks on the parent rows it touches.
  • Contention and Deadlock Risk: A transaction that stays open for seconds while touching millions of rows is the real hazard. Concurrent statements that try to update or delete any of those same rows block until it finishes; the long transaction holds back VACUUM and inflates bloat; and the more rows and tables one transaction locks, the higher the chance of a lock-ordering deadlock with other transactions. The symptom looks like a sitewide stall, but the cause is a slow transaction holding many row locks, not a single table-level lock.

2. Measuring the Cost

Consider a standard multi-tenant schema whose tenants table cascades down three tiers, with no indexes on the foreign key columns:

-- Failure case: foreign keys with no supporting index
CREATE TABLE tenants (
  id UUID PRIMARY KEY,
  name VARCHAR(255) NOT NULL
);
CREATE TABLE users (
  id UUID PRIMARY KEY,
  tenant_id UUID REFERENCES tenants(id) ON DELETE CASCADE, -- INDEX MISSING
  email VARCHAR(255) NOT NULL
);
CREATE TABLE user_activity_logs (
  id UUID PRIMARY KEY,
  user_id UUID REFERENCES users(id) ON DELETE CASCADE, -- INDEX MISSING
  action VARCHAR(255) NOT NULL
);

A single DELETE FROM tenants WHERE id = '...' forces PostgreSQL to:

  • Delete the matching tenants row.
  • Sequentially scan users to find rows with that tenant_id, and delete them.
  • For each deleted user, sequentially scan user_activity_logs to find and delete that user's rows.

On a PostgreSQL 16 test with 2,000 users and 200,000 activity-log rows, deleting one tenant with no FK indexes took about 2.4 seconds. After adding a B-tree index on each foreign key column, the identical delete took about 32 milliseconds, roughly 70x faster.

Scale the log table to tens of millions of rows and the un-indexed version runs long enough to become a visible production incident: the deleting transaction sits open, holding row locks and blocking any concurrent write that touches the same rows.

3. Production Diagnostics: Finding Un-Indexed Foreign Keys

Run this catalog query to list every foreign key whose referencing columns are not covered by the leading columns of some index. (A naive version that compares indkey::text to conkey::text does not work: an index prints its columns as 1 while a constraint prints {2}, so the string match silently flags every key. The version below compares them as arrays.)

-- Detect foreign keys with no supporting index (PostgreSQL)
SELECT c.conrelid::regclass AS child_table,
  c.conname AS foreign_key_name,
  pg_get_constraintdef(c.oid) AS constraint_definition
FROM pg_constraint c
WHERE c.contype = 'f'
  AND c.connamespace NOT IN ('pg_catalog'::regnamespace, 'information_schema'::regnamespace)
  AND NOT EXISTS (
    SELECT 1 FROM pg_index i
    WHERE i.indrelid = c.conrelid
      AND i.indpred IS NULL -- a partial index can't back the FK
      -- the FK columns must be the leading columns of the index, in order
      AND (i.indkey::smallint[])[0:cardinality(c.conkey)-1] = c.conkey
  )
ORDER BY child_table;

This query is read-only and safe to run on a production primary.

4. Fixes: Index Every Foreign Key, and Purge Asynchronously

Step 1: Add a B-Tree Index on Every Foreign Key, CONCURRENTLY

Give every foreign key column a supporting index so cascade lookups use an index scan instead of a sequential scan. Build them with CREATE INDEX CONCURRENTLY so the build does not take a blocking lock on a live table. (CONCURRENTLY cannot run inside a transaction block, and on failure leaves an INVALID index you drop and recreate.)

-- Index the referencing columns so CASCADE uses index scans
CREATE INDEX CONCURRENTLY idx_users_tenant_id ON users (tenant_id);
CREATE INDEX CONCURRENTLY idx_user_activity_logs_user_id ON user_activity_logs (user_id);

Step 2: Replace Synchronous Hard Deletes with Soft-Delete + Batched Purge

Even with indexes, deleting millions of rows in one statement inside a user-facing request is a bad idea: it is one long transaction holding many locks. Mark the tenant deleted instantly, then purge in small, committed batches from a background worker.

// PRODUCTION PATTERN: Soft-delete then batched purge (TypeScript)
import { Pool } from 'pg';
export class TenantLifecycleManager {
  private db: Pool;
  constructor(pool: Pool) { this.db = pool; }
  /**
   * Marks the tenant deleted in a single fast indexed UPDATE,
   * then hands the heavy work to a background purge job.
   */
  async softDeleteTenant(tenantId: string): Promise<void> {
    await this.db.query(`UPDATE tenants SET deleted_at = now(), status = 'PENDING_PURGE' WHERE id = $1 AND deleted_at IS NULL`, [tenantId]);
    await this.enqueueBackgroundPurgeJob(tenantId);
  }
  /**
   * Background worker: delete grandchild rows in small, independently
   * committed batches so no single transaction holds locks for long.
   * Purge order is bottom-up: grandchildren, then children, then the tenant.
   */
  async purgeActivityLogs(tenantId: string, batchSize = 1000): Promise<void> {
    let deleted = 0;
    do {
      // Each batch is its own transaction (one statement = one commit here).
      const result = await this.db.query(`WITH target AS ( SELECT l.id FROM user_activity_logs l JOIN users u ON u.id = l.user_id WHERE u.tenant_id = $1 LIMIT $2 FOR UPDATE OF l SKIP LOCKED ) DELETE FROM user_activity_logs WHERE id IN (SELECT id FROM target)`, [tenantId, batchSize]);
      deleted = result.rowCount ?? 0;
      // Breathe between batches so normal traffic keeps moving.
      await new Promise((r) => setTimeout(r, 50));
    } while (deleted > 0);
  }
  private async enqueueBackgroundPurgeJob(tenantId: string): Promise<void> {
    // Push onto your job queue (BullMQ, pgmq, SQS, etc.)
  }
}

Run the same batched pattern for users after the logs are gone, then delete the tenants row last. Because each batch commits on its own, locks are released continuously instead of being held for the whole purge.

5. Executive Summary & Systems Rules

  • Index every foreign key column: PostgreSQL does not do it for you. Without an index, each cascade step is a full sequential scan, which is what turns a fast delete into a long, lock-holding transaction.
  • It's duration, not lock escalation: PostgreSQL holds ordinary RowExclusiveLock during a cascade and never escalates to a table lock. The contention comes from one slow transaction holding many row locks, so the fix is to make the work fast and short.
  • Don't hard-delete large trees synchronously: Soft-delete with deleted_at, then purge in small committed batches from a background worker, using FOR UPDATE SKIP LOCKED so the purge yields to live traffic.

Seeing slow deletes or lock pile-ups on your Postgres database? I help SaaS teams track down un-indexed foreign keys, redesign purge pipelines, and keep multi-tenant databases responsive under load. Book a consultation →

Originally published on ctousman.com.

Read on DEV Community ↗ ← Back to News

Comments

No comments yet. Start the discussion.