61–70 of 87 posts
I am not sure of this is the best way to do this, but it works for me.
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…
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?
Earlier quoted context omitted.
You should run vacuum as often as possible in Postgres if you’re doing anything other than INSERTs, this is a design tradeoff in Postgres itself. It’s the reason autovacuum exists and why tuning it is so important for performance; nothing wrong with doing a VACUUM ANALYZE after finishing a large DML batch job.
what does as often as possible mean? It will auto-vacuum when it's idle, right? Why not just let it do that?
If you mean your database is seeing extended periods of no updates to a table, you still want to vacuum, maybe even vacuum full if you know when traffic stopped and for how long to get the best possible read performance.
If you have quiet periods in both reads and writes, enjoy the luxury of having an unused database to operate.
This generalizes to most (all?) databases. Selective deletion is largely an unsolved problem at scale in databases to the extent it doesn't release the deleted resources. Under the hood databases try to turn this into selective resource truncation, which scales much better, but in most cases that is not possible without careful design of your data model. Similarly, you often have to remind devs that in many databases…
Years ago I heard Oracle's db had an edge on PG when it comes to DELETEs. I guess that's still the case...
Earlier quoted context omitted.
> It takes far more work to delete/update than insert. Updating rows of text data is going to be more work, because variable-length text can't be updated in-place. So in terms of allocating space, it's more like a delete plus an insert. That's not surprising. (An in-place update that doesn't touch indices is generally going to be faster than an insert, though.) I'm not aware of instances where a delete is "far more w…
Not directly database related, but when it comes to writing files on disks, deletes on SSDs can be rather expensive because of the delete block size vs a simple write.
Years ago I heard Oracle's db had an edge on PG when it comes to DELETEs. I guess that's still the case...
Why are databases so hard?
Storing some data in a binary file isn't very hard. Making it so that you can do quick lookups on it (indexes) and implementing joins in a sane way is kinda hard, but easy compared to the real problem:
Ensuring ACID (in the case of "traditional" databases). I.e. Atomicity, Consistency, Isolation, Durability.
You need to protect against data corruption in the event of failure, all while guaranteeing atomic operations at the user level concurrently (in most production DBs; SQLite is a notable exception in that it fully serializes writes --- but it can get away with this because it's an embedded database with the primary use case of a single-process writer). And the entire thing must land on a known good state at the end of all of those concurrent transactions.
... and they must do it all while maintaining good performance, and sometimes on a combination of filesystem + hardware that's actively hostile towards the idea of data integrity (e.g. hidden RAM caches in disk or RAID controllers that don't flush on power loss --- thankfully, those are getting rarer, or so I've come to understand).
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…
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 And by deleting (up to) 10 each time we insert one, it ensures obsolete things will eventually get deleted.
Obviously, this isn't viable when the deletions must happen due to strict policy (e.g. legal compliance) since it can't ensure when things get deleted, just that they eventually do. IIRC, in my case, I used it for a password reset tokens table. There's no legal issue there and keeping expired ones around is fine as long as the code also checks `expires_at` to make sure it's still valid (which would be a good practice regardless, for defense in depth).