Soft Deletion Probably Isn't Worth It: a potential source of contention/deadlock

[mattcarmody]

good article, thanks for sharing!

I came across your article when I was dealing with another downside with soft deletion which is the potential for lock contention or deadlocks. If an index is created on the deleted_at column, it may cause seemingly narrow queries to hold locks on all the active rows, like any query that specifies WHERE deleted_at IS NULL. This makes any query interested in an active row contend with any other query interested in an active row, even if those queries aren't expected to return the same rows.

[brandur]

Thanks Matt!

And cool — interesting stuff. I'd never thought about that before, but it seems another very valid concern with soft deletion. I think I might need to dig a bit more deeply in what exactly the kinds of locks/contention would occur in such a situation before feeling informed enough to include it in the soft deletion article, but I might get there at some point.