81–90 of 121 posts
Earlier quoted context omitted.
I have long day-dreamed of what a “use it (soon) or loose it” runtime would mean. Allocated blocks would just expire after a set time.
So kind of like browsers work now with the tab unloading and Android killing apps? Personally I find it really obnoxious and disruptive.
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;
MySQL has this as default as far as I recall. But then I never delete, I just set "deleted" to yes.
It's not enabled by default out of the box, but some companies do override their configuration to enable it for new sessions, iirc Facebook did this.
[1] https://dev.mysql.com/doc/refman/8.4/en/mysql-tips.html#safe...
[2] https://dev.mysql.com/doc/refman/8.4/en/server-system-variab...
Earlier quoted context omitted.
I think garbage collection memory management can be thought of a delete optimized database.
Runtimes with garbage collectors typically optimize for allocation, not deletion.
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…
That said, I agree with you in general. An under-used technique is to simply encrypt everything and then use a key store that is guaranteed capable of deleting data. This makes it easy to comply with legal deletion requests even across backups, though of course, you need to manage the keys very carefully and ensure that they are also backed up.
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…
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?
Or maybe a 72 hour day, so I'd have time for things that I'd just enjoy.
Earlier quoted context omitted.
I think that gp’s comment can be reinterpreted as: why should this landmine exist when databases could notify a reader of its manual about this issue in an explicit way, for example: DELETE FROM t WHERE … BATCH 100 Which would simulate batched queries when called outside of transaction. This would remove the need of a client to be connected (or at least active) for a duration of this lenghty operation. If DELETE is s…
Rather than batching, I would want a "NO ROLLBACK DELETE" sort of command. The real expensive part of the delete is rewriting the records into the transaction log so that a cancel or crash can undo the delete. If you've gone to the effort of batching things, you are still writing out those records, you are just giving the db a chance to delete them from the log. I'd like to save my ssds that heartache and instead all…
One simple way in Oracle is to take a table lock, copy the data you want to preserve out to a temporary table, truncate the target table, copy the data back in.
> For example, deleting 1 million rows in a single transaction is a textbook case of what not to do. Instead, splitting the operation into smaller batches, such as deleting 10,000 rows across 100 iterations, is far more effective. Why do I as a user have to do that? Why can't the database implement batching internally and automatically transform my 1-million-rows query into an appropriate list of batched queries? (Ed…
Admin panels may do that. Say for example deleting a bucket with a million rows. In that case I'd fire off a job and tell the user, yeah sure the delete is successful. But check the logs to be sure.
No message (other than an INFO log message) when the operation completes successfully, but failures are handled and notified properly.
One solution for performance degradation with soft deletes is to partition the table by some field like `created` monthly. Queries will need to include `created` in the query is the main downside.
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.”
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.
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 available, and even if imperfect they improve the situation.