71–80 of 87 posts
Earlier quoted context omitted.
> And you cannot keep doing high concurrent DROP TABLEs to run your large scale CRUD app In this kind of use case/design, I would assume it would make use of partitions to make this more palatable in which case it would seem that you would bypass this issue of "high concurrent DROP TABLE". Large scale CRUD app just points to recent-ish partitions. Old partitions are either going to be low or on access and can be drop…
(Most) CRUD/OLTP applications don't delete data by timestamp; they delete by primary key. For those workloads, DROP TABLE (or dropping a partition) isn't a palatable option. The entire premise here is really about time-series workloads where most operations are based on a timestamp. In those apps partition dropping has been a standard and recommended retention strategy for years. That's precisely why extensions like…
UUID v7 to the rescue!
Years ago work was bit by the analogous thing in MySQL. Like it usually does, it took a chain of events: - We wrote a cronjob to periodically DELETE for a retention policy on a table we'd just created. Most senior person on the team reviewed it, looked fine. - Unusually for us, we prioritize QA'ing a different feature for release, delaying the release of this cronjob and a bunch of other code. - During that delay, th…
One thing I did a while ago was to make deletes part of inserts, to amortize the cost. The main reason was to avoid a separate cron job, but it had other benefits (and downsides) too. Something like: DELETE FROM foo WHERE expires_at Note the LIMIT: it ensures the latency stays under control even if we've suddenly hit 50k rows that need deleting. And by deleting (up to) 10 each time we insert one, it ensures obsolete…
I have no idea why, it seems such an obvious feature for supporting large databases.
Only by a weird definition of "scalable". The first sentence says: > Counterintuitively, large DELETEs add work to the database. There is nothing counterintuitive about this. It takes just as much work to delete a row as it takes to insert a row. Why wouldn't it? Obviously you have to do almost all the same operations: write a log, write the deletion, update indices, replicate it, etc. And yes, it's a well-known tric…
Only by a weird definition of "scalable". The first sentence says: > Counterintuitively, large DELETEs add work to the database. There is nothing counterintuitive about this. It takes just as much work to delete a row as it takes to insert a row. Why wouldn't it? Obviously you have to do almost all the same operations: write a log, write the deletion, update indices, replicate it, etc. And yes, it's a well-known tric…
> It takes just as much work to delete a row as it takes to insert a row. Why wouldn't it? Because your data structure/algorithm supports fast deletes? File systems support deleting entire directories instantly. I'm not aware of any fundamental reason why DELETE in a SQL database must take as long as an insert?
Only by a weird definition of "scalable". The first sentence says: > Counterintuitively, large DELETEs add work to the database. There is nothing counterintuitive about this. It takes just as much work to delete a row as it takes to insert a row. Why wouldn't it? Obviously you have to do almost all the same operations: write a log, write the deletion, update indices, replicate it, etc. And yes, it's a well-known tric…
> It takes just as much work to delete a row as it takes to insert a row. Why wouldn't it? Because your data structure/algorithm supports fast deletes? File systems support deleting entire directories instantly. I'm not aware of any fundamental reason why DELETE in a SQL database must take as long as an insert?
And file systems deleting huge directories instantly is the equivalent of DROP TABLE here, which I also mention in my comment.
Only by a weird definition of "scalable". The first sentence says: > Counterintuitively, large DELETEs add work to the database. There is nothing counterintuitive about this. It takes just as much work to delete a row as it takes to insert a row. Why wouldn't it? Obviously you have to do almost all the same operations: write a log, write the deletion, update indices, replicate it, etc. And yes, it's a well-known tric…
> It takes just as much work to delete a row as it takes to insert a row. Why wouldn't it? Because your data structure/algorithm supports fast deletes? File systems support deleting entire directories instantly. I'm not aware of any fundamental reason why DELETE in a SQL database must take as long as an insert?
What file system supports this? I’ve never experienced the joy of deleting an entire large directory and seeing it disappear instantly.
I’m not sure how this would work with any modern file system that supports basic notions like hard links.
Earlier quoted context omitted.
> And the obvious rebuttal to that is that it's equally hard to provide an upper bound for the runtime of a single insert This is precisely where you're going wrong. The insert is upper boundable in advance (you know the set of everything you might potentially have to insert), the delete isn't because you don't know what's in the db until you look. I strongly recommend poking around with Foundation for this, because…
> The insert is upper boundable in advance A concurrent DML happening then suddenly your MERGE INTO WHEN NOT MATCHED INSERT/INSERT INTO SELECT is way larger that you thought? I thought "some workloads can suddenly be way larger that I expected" was supposed to be a thing in all non-trivial DML.
One day you'll maybe discover ACID properties of RDBMS systems, and all the puzzles will fall into place.