Hacker News Reader

Comment by pradn | Hacker News Reader

Soft deletion is just one way to achieve undeletion. The author's proposed solution of moving the resource to another table works just as well. You can move it back to the non-deleted table to perform the undeletion. You can keep around these deleted objects as long as you want; they work as a subset of a proper audit trail. The cost of course is you have more tables, but that is less of a cost than having to add "deleted=False" predicates in all of your queries.

Also note, if you use a soft-deleted column, indexes need to be keyed by that column as well if you want to access non-deleted objects quickly. That's extra complexity.

View on HN

Even more important; the deleted records don't need to live in your cache / RAM / etc. Potentially faster queries.

    but that is less of a cost than having to add "deleted=False" 
    predicates in all of your queries.

Maybe or maybe not. You can use a view. Or you may be using an ORM that lets you set a default scope (essentially, a default WHERE clause - ActiveRecord lets you do this)

It also depends on if you're designing an app for this from the ground up or if you're trying to retrofit an existing app with 90 million different hardcoded queries.

Also depends on what you want to do with the deleted records. Do you want to do things with them? Maybe after a user is deleted, you want them to be able to log in, but you would prefer them to see a "sorry - your account has been deleted" message instead of "user not found." Maybe you want your support staff to be able to look at deleted users. Etc. Now you need to update your app logic so that it's flexible enough to look at "users_deleted" and "users". Which may be at least as onerous as messing with the WHERE clause on every single query.

To be clear, I don't hate the "another table" solution. It's the right choice in a lot of situations IMO.

> but that is less of a cost than having to add "deleted=False" predicates in all of your queries.

It's like people have forgotten what views are.

Well, most likely they're queries that have "deleted=False" in them. ;-)

You almost made me ruin a keyboard. Jerk.

What seems to be missing here is the DB tech he is using. On a proper database you can do your "undeleted" with triggers and it's relatively trivial. Nonsense like a "deleted" column on your main data table just seems silly.

I worked at a place that kept all the deleted stuff in their main tables. It turned out over 90% of the rows were deleted. I'm not sure how often something was undeleted, but it was not very frequent. Some of these soft deleted rows were 5+ years old. Archive that crap.

Yeah, for sure, if deletion is going to be the exception rather than the rule.... keeping deleted stuff in the main tables is not ideal.

Of course, you can have your cake and eat it too. You can e.g. keep your deleted crap in the main table (which makes certain things easier) and then hard-delete or archive it to another table after N days.

I would never suggest archiving unless we're hitting some performance limit. Why generate more busy work? Just leave it there and if need be add more indexes and partitions.

well, they were running into performance issues. This table had something like 100 columns and at least 30 indexes already. This table was fat. Many columns, many rows, mostly unused garbage.

And several of these "columns" were actually JSON blobs that probably should've been in their own table. But what do I know. I left!

When we talk about best practices, it's probably not too useful to talk about these sorts of "extremely high technical debt situations."

Once you have an existing tirefire, it's a matter of damage control and "what is the least bad way to accomplish new functionality?" and the answer to that will always be unique for each unique tirefire.

Triggers don't solve what the author's getting at, i.e. who knows what else outside this database changes upon deletion that would need to be reversed? It's all in the article.

    Nonsense like a "deleted" column on your main data table just seems silly.

Why silly? Yeah, it's one more column, but most DBs just represent dates as bigints AFAIK so it's not really the end of the world.

Sure, you have to be conscious of adding "where deleted_at = NULL" (or the opposite) to all your queries. If you're going through an ORM this is usually trivial e.g. in ActiveRecord it can be a default scope on your model.

    On a proper database you can do your "undeleted" with triggers 
    and it's relatively trivial.

I like this solution as well, FWIW.

If I was retrofitting an existing application with soft-deletes I would definitely favor this solution.

There are some obvious advantages over a "deleted" column on your main table for sure.

One drawback is that you now have to keep two table definitions in sync. You also have to add conditional logic in a lot of places to handle whether a given piece of code is looking at a normal user from the "users" table or a deleted user from the "users_deleted" table. Instead of messing with the WHERE clause on every query, now you're messing with the table name on every query. So that's a wash IMO.

Reply on news.ycombinator.com