---
title: "Discussion on \"Foreign Key Cascade Deletes: Un-Indexed FKs, Slow Cascades, and Lock Contention in PostgreSQL\" | Hashnode"
description: "Discussion on \"Foreign Key Cascade Deletes: Un-Indexed FKs, Slow Cascades, and Lock Contention in PostgreSQL\". Why an un-indexed foreign key turns one cascade delete into a long lock-holding transaction, and how to purge safely instead"
url: "https://hashnode.com/posts/postgresql-foreign-key-cascade-locks-intent-lock-contention/6ae38ae7f776a9cc0ea73394"
---

[Feed](https://hashnode.com/feed) /

Discussion

# [Foreign Key Cascade Deletes: Un-Indexed FKs, Slow Cascades, and Lock Contention in PostgreSQL](https://ctousman.hashnode.dev/postgresql-foreign-key-cascade-locks-intent-lock-contention?utm_source=hashnode&utm_medium=feed)

Why an un-indexed foreign key turns one cascade delete into a long lock-holding transaction, and how to purge safely instead

Oct 29 · 8 min read · ctousman.hashnode.dev  
Read the article

-   [#postgresql](https://hashnode.com/tag/postgresql)
-   [#databasestorage](https://hashnode.com/tag/databasestorage)
-   [#performance](https://hashnode.com/tag/performance)
-   [#sql](https://hashnode.com/tag/sql)
-   [#system-architecture](https://hashnode.com/tag/system-architecture)
-   [#backend](https://hashnode.com/tag/backend)

## Written by

[Usman Khan](https://hashnode.com/@usmankhan-io)

@usmankhan-io · Karachi, Pakistan

  
[View profile](https://hashnode.com/@usmankhan-io)

Fractional CTO & Founder at Zentiq Labs | B2B SaaS Architecture, Distributed Systems & AI Integration | ctousman.com

Usman Khan is a Fractional CTO, Lead Systems Architect, and Founder of Zentiq Labs. With over 13 years of experience engineering high-concurrency B2B SaaS applications, he specializes in multi-tenant database isolation (PostgreSQL RLS), distributed Redis caching patterns, and production AI/LLM integrations. Usman writes architectural deep dives and engineering guides at ctousman.com.

## Responses 2

Markdown supported · ⌘↵ to post

[Materiais Fiscais](https://hashnode.com/@materiaisfiscais)

[Oct 30](https://hashnode.com/posts/postgresql-foreign-key-cascade-locks-intent-lock-contention/6ae38ae7f776a9cc0ea73394/comment/6ae4a0f741190ba61d30ffef)

Um detalhe que ajuda a entender o ganho dos 70x: o ON DELETE CASCADE é executado por gatilhos internos de integridade, que disparam uma busca na tabela filha para cada linha removida da tabela pai. Então, ao apagar um tenant com milhares de usuários, a tabela de logs recebe milhares de buscas, e sem índice cada uma vira uma varredura completa. Por isso o índice na coluna da FK pesa mais do que parece num teste com uma única linha. Para a purga em lotes, vale também definir um lock\_timeout na sessão, para que um lote que esbarre em linhas travadas falhe rápido e possa ser repetido, em vez de ficar esperando e segurando outros locks.

[AI Developer](https://hashnode.com/@lycore)

[Oct 30](https://hashnode.com/posts/postgresql-foreign-key-cascade-locks-intent-lock-contention/6ae38ae7f776a9cc0ea73394/comment/6ae445b12fe8878f59da6fa6)

The point that the damage comes from transaction duration rather than lock escalation is the right framing. One nuance for the diagnostic query: an existing composite index that leads with the FK column (for example tenant\_id, created\_at) already serves the cascade lookup, so a separate single-column index would be redundant. Checking leading columns rather than just indexed or not avoids adding write overhead. For the batched purge, deleting leaf tables first in small bounded batches keeps each transaction short enough that VACUUM is not held back. How would the soft-delete step interact with unique constraints on columns like email, since soft-deleted rows would still occupy those values unless the index is partial?
