Hacker News Reader

Comment by hu3 | Hacker News Reader

Anyone has experience with Softdeletes? Any links?

I've worked both ways. Hard DELETE and soft UPDATE deleted_at = NOW().

They both have tradeoffs.

It seems to me that delete_at columns allow for faster development at the cost of more runtime bugs. Like why is this deleted row being listed in this <select>? Did I forget to add `WHERE deleted_at IS NULL` again?

Meanwhile a hard DELETE usually means I get to copy rows to a cemetery table just in case. And it forces me to think about what to do with Foreign Keys that point to the row being deleted. Can I really delete this row if 3 other tables point to it? It makes some business rules evident.

I'm thinking, perhaps I can serialize hard DELETED rows as JSON and store all system deleted rows in a trash table. This way I don't have to keep schema in sync there too.

View on HN

> Like why is this deleted row being listed in this <select>? Did I forget to add `WHERE deleted_at IS NULL` again?

This is adding more complexity but... views?

For example: rename the "foo" table to "all_foo" and "CREATE VIEW foo AS SELECT * FROM all_foo WHERE deleted_at IS NULL"

I think you could even create a trigger on the view so "DELETE FROM foo" gets replaced with an "UPDATE all_foo SET deleted_at = NOW()", but I've never tried that before.

Not bad. I forgot about the view solution. So thanks for remembering me.

I haven't worked with this approach. But I might give it a try.

> Did I forget to add `WHERE deleted_at IS NULL` again?

This seems like an unaddressed use case on the part of the RDBMS to me, a feature that shouldn’t be reinvented by ORMs or each individual project.

Instead, you should be able to just do DELETE SOFT FROM … in your queries and have the DB make the rows unavailable in all queries.

Or maybe even set it up, so that regular DELETE is treated like a soft delete, so that suddenly all of the ORMs and users wouldn’t be able to drop data on accident.

Maybe allow overwriting it with DELETE /*+DELETE_HARD*/ FROM … if you so choose, as well as do SELECT /*+WITH_SOFT_DELETED*/ … if you don’t want to introduce any historical incompatibility.

If such features don’t exist at the DB or a DB extension level, then we’ll be doomed to deal with ORMs reimplementing it which will be pretty bad if you ever need more than one tech stack connected to it, alongside the issues of all of your queries needing additional conditions.

I agree, DBs need to solve this better.

Temporal tables seems like a step in the right direction but they too add complexity and gotchas. And I think they are kind of overkill for just softdeletes.

Another low level solution are Views. But I have databases approaching a thousand tables. Duplicating 1000 tables as views would add significant friction to development and maintenance.

> I'm thinking, perhaps I can serialize hard DELETED rows as JSON and store all system deleted rows in a trash table. This way I don't have to keep schema in sync there too.

this is normally known as an audit table.

The problem with all of these methods though is that data is persisted, but the strategy to actually recover it is left as an exercise for the reader. And in my experience, never implemented and data is never actually reviewed/recovered in practice. The scenarios where a "recycling bin" is actually needed usually ends up encoded as a user-facing business requirement that ends up having an actually well-defined, non-generic, and actually recoverable logic that has nothing to do with the generically applicable soft/hard/audit strategies.

I agree it's very rare, if ever, that business folks require to undelete data.

But I have to say, with the developer hat on, softdeleted rows helped me debug systems more than once. With that said, softdeleted rows also wasted my time more than I'd like to admit while exploring data only to realize I was looking at stale, deleted data.

an audit table is useful for that purpose. soft delete is product of the confused mind, thinking one can get data recovery for almost-free. Audit tables admit that at best it'll be a manual process to be custom-designed for the problem at hand, when it comes to it

Reply on news.ycombinator.com