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

91–100 of 121 posts

Re: DELETEs Are Difficult

#91

Post by dataflow »

Polizeiposaune wrote: post #90
dataflow wrote: post #50

Earlier quoted context omitted.

Even vacuuming wouldn't actually destroy the data right? Because filesystems don't guarantee they will overwrite or wipe any particular disk blocks. And even if they did, SSDs still wouldn't promise that the blocks aren't remapped instead of being wiped & reused.

> Because filesystems don't guarantee they will overwrite or wipe any particular disk blocks. Some filesystems have a richer interface to the underlying storage device, allowing them to invoke commands such as ATA TRIM or SCSI UNMAP - either incrementally as blocks are freed, or on demand - which request that the underlying storage device forget the block contents. So the necessary interfaces exist and are widely ava…

> Some filesystems have a richer interface to the underlying storage device, allowing them to invoke commands such as ATA TRIM or SCSI UNMAP

No, that's not a guarantee of data erasure. Not just because it's just a request that the device can disregard, but also because filesystems play tricks (like storing small bits of data inline, or logging data in various places, etc.) and they don't clear all those blocks just because you wanted to clear a couple bytes.

Re: DELETEs Are Difficult

#92

Post by magicalhippo »

eddd-ddde wrote: post #79
kijin wrote: post #26

Earlier quoted context omitted.

It might depend on the version, but last time I checked, DELETEing an entire table was much slower than TRUNCATE TABLE.

I'm pretty sure that only applies to Postgres.

Sybase SQLAnywhere as well, and not unlikely MSSQL too given its shared ancestry.

Delete with WHERE is sufficiently slow in MSSQL we have to do batched deletes, but I can't recall offhand if that holds for whole table deletion as well.

Re: DELETEs Are Difficult

#93

Post by hnbear »

mpweiher wrote: post #27
jandrewrogers wrote: post #13

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…

Yes...but it goes even deeper. For example, in physics, the paradox of Maxwells Demon is resolved when you consider the cost of deleting data: "In 1982, Charles Bennett showed that, however well prepared, eventually the demon will run out of information storage space and must begin to erase the information it has previously gathered.[8][12] Erasing information is a thermodynamically irreversible process that increase…

I like the idea of a todo list that comes with a built in auto-delete.

You either do your to dos, or it auto-deletes them for you. No worry about it getting full, but also some pressure to actually get them done or they'll be wiped.

And if you're happy they're wiped, then you probably didn't need to do it at all.

I wonder if there's something like that already.

Re: DELETEs Are Difficult

#94

Post by okasaki »

sitkack wrote: post #81
okasaki wrote: post #66

Earlier quoted context omitted.

So kind of like browsers work now with the tab unloading and Android killing apps? Personally I find it really obnoxious and disruptive.

You would prefer something a little more persistent?

I prefer to do my own app lifecycle management.

Re: DELETEs Are Difficult

#95

Post by cruffle_duffle »

aurareturn wrote: post #2

DELETE FROM films; I'm surprised databases makes it so easy to just delete an entire table. I think the command should be DELETE FROM films YES-I-KNOW-WHAT-I-AM-DOING;

That is why you always get in the habit of wrapping your stuff in “BEGIN TRANSACTION”. Then if and when you fuck up you can issue a rollback and be all good.

Re: DELETEs Are Difficult

#96

Post by selecsosi »

mike_hearn wrote: post #84
jandrewrogers wrote: post #13

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…

What about LSM trees? Something like RocksDB is very efficient at deleting. A delete operation is a tiny write (a tombstone) and then the actual deletion is done via compaction in the background with entire tablets being freed at once when the live data is evacuated. It's actually most efficient when deleting large ranges - when just replacing data it's not so efficient due to the write amplification. That said, I ag…

+1 This was our strategy at TempoIQ for our ts storage engine (built on top of rocks).

Very efficient and effective at managing tons of data ingestion (and deletion) at scale.

Not an easy out of the box tech to build on top of though when you have to build all the analytics/management pieces that something like PG gets you so I get the lack of public examples

Re: DELETEs Are Difficult

#97

Post by tonymet »

Why do DBs perform Delete operations online? Wouldn’t it be better to soft-delete (at the table-space level ) and then run scheduled task to clean up the table spaces?

Similar to git. When you “delete” files they are just removed from the tree. It isn’t until later that all refs and reflog references have expired , and gc is run, that the objects are actually removed.

Re: DELETEs Are Difficult

#98

Post by SoftTalker »

Svip wrote: post #32
rrr_oh_man wrote: post #24

Earlier quoted context omitted.

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

Personally, I like to be explicit and in control. In the application layer, I may be far away (at least mentally speaking) from the constraints in the database, and if I update/delete something, I don't want it to "magically" cascade through the database. For those reasons, I always prefer RESTRICT, both for ON DELETE and ON UPDATE. This forces me to clean up before I make the actual change I'm interested in, and any…

> if I update/delete something, I don't want it to "magically" cascade through the database.

This is the big reason I don't like triggers either. I would use them in only one case: if the database didn't support an auto-incrementing "identity" type, I might use a trigger to simulate that. But just as often I would prefer a stored procedure that got the new ID from a sequence, and then did the insert, especially if there were other things I needed a stored procedure to deal with.

Re: DELETEs Are Difficult

#99

Post by MatmaRex »

tonymet wrote: post #97

Why do DBs perform Delete operations online? Wouldn’t it be better to soft-delete (at the table-space level ) and then run scheduled task to clean up the table spaces? Similar to git. When you “delete” files they are just removed from the tree. It isn’t until later that all refs and reflog references have expired , and gc is run, that the objects are actually removed.

If you read the linked article, it explains that this is exactly what they do.

Re: DELETEs Are Difficult

#100

Post by tonymet »

MatmaRex wrote: post #99
tonymet wrote: post #97

Why do DBs perform Delete operations online? Wouldn’t it be better to soft-delete (at the table-space level ) and then run scheduled task to clean up the table spaces? Similar to git. When you “delete” files they are just removed from the tree. It isn’t until later that all refs and reflog references have expired , and gc is run, that the objects are actually removed.

If you read the linked article, it explains that this is exactly what they do.

thanks i got the summary for free
Post reply on HN