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

DELETEs Are Difficult

notso.boringsql.com

Post reply on HN
Print view View original

11–20 of 121 posts

Re: DELETEs Are Difficult

#11

Post by tempodox »

Terr_ wrote: post #6

For many of the most painful deletion questions, the root problem is that when the software was first made the stakeholders/product-org didn't think about use-cases for deleting things. At best, they assume a "do not show" property can be placed onto things, which falls apart when you get to legal issues that compel actual removal.

That depends on the context. In some cases you're not allowed to physically delete because you need to provide an audit trail for 10 years or so. You could move those rows into separate audit tables instead. However, that requirement should not come as a surprise.

Re: DELETEs Are Difficult

#12

Post by jaredsohn »

buro9 wrote: post #8
magicalhippo wrote: post #4

Earlier quoted context omitted.

Agreed, I've long been thinking that DELETE should require a WHERE clause. If you really want to just delete everything, just do WHERE 1=1.

UPDATEs should require a WHERE clause too. At which point we could just say all SQL should have a WHERE clause.

I think SELECTs without WHERE clauses are fine

Re: DELETEs Are Difficult

#13

Post by jandrewrogers »

DELETE is expensive at a deep fundamental level that we don’t think about much in computer science because we are more worried about losing data. The article is about Postgres but it generalizes. We don’t actually have any computer science for DELETE optimized databases. I’ve idly looked into delete-optimization in databases as thought experiments, since there isn’t much in the way of literature on it, and it is far more difficult than I think many people intuit. The article is partly a manifestation of this reality.

I think the nature of DELETE is one of the more interesting open problems in computer science. It is one of those things that, when we require precision, turns out to be very difficult to define.

Re: DELETEs Are Difficult

#14

Post by alexanderscott »

orionblastar wrote: post #3

Most databases I used have a Status column we could mark as active, inactive, or deleted. That way, you can see what records were marked as deleted and change them back in case of accidental deletion. Keep record retention with the Date_Modified column so you can use SQL delete to remove those deleted records that are older than a year or so.

this is a “soft delete”. as the author notes, depending on the nature of the data being stored a soft delete does not meet the requirements of many data privacy laws and compliance regulations (like GDPR’s right to erasure).

Re: DELETEs Are Difficult

#16

Post by ahoka »

buro9 wrote: post #8
magicalhippo wrote: post #4

Earlier quoted context omitted.

Agreed, I've long been thinking that DELETE should require a WHERE clause. If you really want to just delete everything, just do WHERE 1=1.

UPDATEs should require a WHERE clause too. At which point we could just say all SQL should have a WHERE clause.

And queries should start with WHERE.

Re: DELETEs Are Difficult

#17

Post by Svip »

> Unlike DELETEs, UPDATEs don’t trigger cascaded actions - they only involve triggers that are explicitly defined.

That's not entirely true. ON UPDATE CASCADE is a thing for foreign keys, at least in PostgreSQL (which this article is talking about), meaning that the foreign row referencing the row gets updated. Though, personally, I would never use ON UPDATE CASCADE, as it seems kind of funky.

Re: DELETEs Are Difficult

#18

Post by burntcaramel »

If data isn’t actually removed until vacuuming, then are systems that perform SQL DELETES actually GDPR compliant? Because technically the private data is still there on disk and could be recovered. “Until the autovacuum process or a manual VACUUM operation reclaims the space, the “deleted” data remains.”

Re: DELETEs Are Difficult

#19

Post by arcanemachiner »

Svip wrote: post #17

> Unlike DELETEs, UPDATEs don’t trigger cascaded actions - they only involve triggers that are explicitly defined. That's not entirely true. ON UPDATE CASCADE is a thing for foreign keys, at least in PostgreSQL (which this article is talking about), meaning that the foreign row referencing the row gets updated. Though, personally, I would never use ON UPDATE CASCADE, as it seems kind of funky.

> Though, personally, I would never use ON UPDATE CASCADE, as it seems kind of funky.

As a relative noob in the world of database administration, I'm glad to hear that someone else feels this way.

Re: DELETEs Are Difficult

#20

Post by physicsguy »

They are difficult but it shouldn’t be underestimated the cost of trying to keep consistency with the alternatives either.

I sometimes think that people ask the wrong question on this sort of thing - rather than thinking “what technical solution should I come up with” you should be thinking “what is the business requirement here, and what consistency guarantees are needed?”

In many cases you want to soft delete anyway and mark rows as stale rather than deleting them wholesale. Cascade deletes need to be very carefully thought about, as while they’re very handy they can be quite destructive if the relationships are not mapped out.

Personally having spent some time now in the microservices hole, I miss all the power SQL databases give you for this sort of thing. I think everyone should spend some time reading and digesting ‘Designing Data Intensive Applications’ and evaluating the trade offs in detail.

Post reply on HN