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.
Markdown supported · ⌘↵ to post
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?
Materiais Fiscais
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.