I don't trust ON DELETE CASCADE

Postgres’s ON DELETE CASCADE simplifies referential cleanup by atomically deleting related rows through foreign-key relationships, but deeply connected schemas can turn a single deletion into a large transaction that generates substantial WAL, dead tuples, index updates, and locks across many tables. Such cascades can contribute to outages when schema migrations requiring stronger locks queue behind them, block later writes, or when missing foreign-key indexes cause expensive full-table scans. Logical replication introduces further risks because subscriber-side triggers and cascades are normally skipped, potentially leaving orphaned rows if not every dependent table is replicated, while publisher-side deletes can fail for cascade children lacking a usable replica identity. Suggested mitigations include short migration lock timeouts, comprehensive replication coverage and orphan checks, and ensuring foreign keys are indexed. For large-scale deletion workloads, soft deletes followed by scheduled, batched physical cleanup can reduce transaction size, limit lock duration, support recovery from accidental deletions, and better align with the broader principle of keeping OLTP transactions short and bounded.

Use this post, company, and trend context to find content marketing opportunities, perform competitive analysis, or address product feature gaps via the Plushcap MCP server or the Plushcap API.