Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

The trouble with soft delete

richarddingwall.name

Post reply on HN
Print view View original

11–20 of 20 posts

Re: The trouble with soft delete

#11

Post by prodigal_erik »

jrockway wrote: post #9
earl wrote: post #7

So Richard basically claims that one of the big problems with soft delete is that, from now on, all queries against the table have to add a where clause excluded soft deleted rows; he claims this is some sort of tax. Is he that ignorant of what a reasonable ORM (or at least, Ruby on Rails) will do for you? eg named_scope, etc...

Yes, he is. I could implement the soft-delete feature in the database, write one class (that sets up the queries chained off of it to be aware of deletions), and then substitute that class for the original via the dependency injection system. Total effort? 15 minutes. Total changes to the application logic and the code that actually queries the database? 0. If you are writing PHP, where you hard-code a query and then…

That only works if everyone you work with religiously uses a single ORM and dynamic programming language, because it makes calls to that ORM the only (cumbersome) query language that gives correct results. It doesn't fix the ad hoc SQL query your marketing guys are pasting out of email from a developer who went to another startup last year (yes, I have seen this happen).

Re: The trouble with soft delete

#12

Post by bmj »

patio11 wrote: post #3

A million inserts into a table a year is causing performance problems? Assuming 200 work days and all accesses being in a four hour period that is about one row inserted every three seconds. DBs are not my bag, baby, but that presumably should not be killing you. At the day job, when we do a soft delete as defined here, we tend to create a view of the active rows in the table. Accessing through the view rather than t…

We use soft deletes in our system (primarily because we have to maintain all data), and we use views to retrieve results. The views also flatten the data a bit, joining appropriate tables, which again simplifies queries.

Re: The trouble with soft delete

#13

Post by earl »

prodigal_erik wrote: post #11
jrockway wrote: post #9

Earlier quoted context omitted.

Yes, he is. I could implement the soft-delete feature in the database, write one class (that sets up the queries chained off of it to be aware of deletions), and then substitute that class for the original via the dependency injection system. Total effort? 15 minutes. Total changes to the application logic and the code that actually queries the database? 0. If you are writing PHP, where you hard-code a query and then…

That only works if everyone you work with religiously uses a single ORM and dynamic programming language, because it makes calls to that ORM the only (cumbersome) query language that gives correct results. It doesn't fix the ad hoc SQL query your marketing guys are pasting out of email from a developer who went to another startup last year (yes, I have seen this happen).

Yeah, but any sql query can go stale...

Re: The trouble with soft delete

#14

Post by randallsquared »

jrockway wrote: post #9
earl wrote: post #7

So Richard basically claims that one of the big problems with soft delete is that, from now on, all queries against the table have to add a where clause excluded soft deleted rows; he claims this is some sort of tax. Is he that ignorant of what a reasonable ORM (or at least, Ruby on Rails) will do for you? eg named_scope, etc...

Yes, he is. I could implement the soft-delete feature in the database, write one class (that sets up the queries chained off of it to be aware of deletions), and then substitute that class for the original via the dependency injection system. Total effort? 15 minutes. Total changes to the application logic and the code that actually queries the database? 0. If you are writing PHP, where you hard-code a query and then…

So your response basically boils down to "If you do everything else exactly right (and have since the application was designed), this won't bite you"? I'll agree that that's certainly true...

Re: The trouble with soft delete

#15

Post by jerhinesmith »

earl wrote: post #7

So Richard basically claims that one of the big problems with soft delete is that, from now on, all queries against the table have to add a where clause excluded soft deleted rows; he claims this is some sort of tax. Is he that ignorant of what a reasonable ORM (or at least, Ruby on Rails) will do for you? eg named_scope, etc...

Even a decent ORM will only partially solve the problem.

For example, if you have an :active named_scope in rails that respects an object's active state, you still need to remember to do MyObject.active.find everywhere you want to exclude inactive results (meaning it's still just as easy to forget it). This got a little bit better with default_scope, but now you have the problem of trying to jump through hoops in the 1 or 2 cases where you do want to bring back inactive objects -- which, in my opinion, still imposes some sort of tax (albeit a slightly more readable one).

Granted, I'm still fairly new to rails, so if there really is a way to just be able to do MyObject.find in all cases where I want to ignore inactive records and MyObject.include_inactive.find in the 1 or 2 edge cases where its needed, then I will readily concede the absence of said tax.

Re: The trouble with soft delete

#16

Post by truebosko »

I read this article and the entire time I was thinking how easy this problem is solved with a good ORM, like in Django.

All I do is setup two managers, one the default so in the admin I can browse my records without any filtering and then a second one which I use exclusively in my views that simply adds .filter(hidden=False) to the get_query_set method. So simple, and have never ran into any problems.

Re: The trouble with soft delete

#17

Post by nixme »

jbert wrote: post #5
russell wrote: post #4

Dingwall lumps a number of separate issues under "soft delete": undo, audit trails, soft create, and performance in the presence of historical data. He presents several solutions, not all of which I would buy. The is_deleted column is a pretty simple solution that we all use and there are a number of solutions to the problem of retrieving only the active columns, such as views. Audit trails and performance are more i…

> but the 95% historical data made writes slow because of the large number of indexes. Why does updating an index take more time if there are many table rows? i.e. if you have 5 indices on a table and 1 write/second, what's the difference in the work done by the db whether there are 1000 rows or 100k rows?

Simple explanation: Most database indexes are forms of B-trees. Insertion into a binary tree isn't constant time O(1), it's usually O(log n).

Re: The trouble with soft delete

#18

Post by jrockway »

randallsquared wrote: post #14
jrockway wrote: post #9

Earlier quoted context omitted.

Yes, he is. I could implement the soft-delete feature in the database, write one class (that sets up the queries chained off of it to be aware of deletions), and then substitute that class for the original via the dependency injection system. Total effort? 15 minutes. Total changes to the application logic and the code that actually queries the database? 0. If you are writing PHP, where you hard-code a query and then…

So your response basically boils down to "If you do everything else exactly right (and have since the application was designed), this won't bite you"? I'll agree that that's certainly true...

The problem is not the "soft delete", but rather peoples' inability to write computer programs, then.

Re: The trouble with soft delete

#19

Post by jbert »

nixme wrote: post #17
jbert wrote: post #5

Earlier quoted context omitted.

> but the 95% historical data made writes slow because of the large number of indexes. Why does updating an index take more time if there are many table rows? i.e. if you have 5 indices on a table and 1 write/second, what's the difference in the work done by the db whether there are 1000 rows or 100k rows?

Simple explanation: Most database indexes are forms of B-trees. Insertion into a binary tree isn't constant time O(1), it's usually O(log n).

Thanks, I was thinking in terms of hashes (which doesn't really make sense for searching/ordering).

Re: The trouble with soft delete

#20

Post by rmason »

Always record the date of the soft delete. That way you can have a daily process to move records x days old to a historical table.

If it's something like a rapidly changing customers list then delete any customers where the soft delete date is greater than three years old.

Post reply on HN