Once you get to about 1M rows of data, batching is essential.
Every DB needs it, eventually. Even NoSQL darlings like Cassandra - I've seen it go into a resource-constrained death-spiral on stuff that should be async / non-blocking and safe. If you need to stay up, it's always worth planning on, and making sure your logic works during long-running gradual migrations.
Is this being positioned as a strength, in your comment?
I wasn't clear exactly what the author was doing. "The worker crashes because it couldn’t write to the database and the VM shuts down" - why would the VM shut down? I assume VM here means the Virtual Machine (OS).
Raw EXPLAIN dumps bytecode, which is usually much more verbose than you want. EXPLAIN QUERY PLAN dumps a summary.
Also, you don't get to choose your competitors. SQLite is single-writer focused and an embedded library, but it's a sophisticated database engine after all, so comparisons with other "real databases" are more than appropriate. This meme really needs to die. Even more so when all you're comparing is query plan debugging output where there is no justification for unreadable output.
With SQLite's `.expert` mode you can delay that day a little longer: https://www.sqlite.org/cli.html#index_recommendations_sqlite...
Also wrt> My approach so far has been to just do these cleanup operations in small batches so that I don’t need to do database queries that take more than 5 seconds to run. This whole experience has given me more of an appreciation for why someone might want to use a “real” database like Postgres which can have more than one writer at the same time though.
The advice for those " “real” " databases is generally to also do cleanup operations in small batches, they just tend to make it less obvious you're doing something unperformant in the smaller case. You're more right than you thought!