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?