3 ms·
> Maybe one day I’ll learn to read a query plan. With SQLite's `.expert` mode you can delay that day a little longer: https://www.sqlite.org/cli.html#index_rec
by striking 3mo ago
> Maybe one day I’ll learn to read a query plan.
With SQLite's `.expert` mode you can delay that day a little longer: https://www.sqlite.org/cli.html#index_recommendations_sqlite_expert_ https://www.sqlite.org/cli.html#index_recommendations_sqlite...
sqlite> CREATE TABLE x1(a, b, c); -- Create table in database
sqlite> .expert
sqlite> SELECT * FROM x1 WHERE a=? AND b>?; -- Analyze this SELECT
CREATE INDEX x1_idx_000123a7 ON x1(a, b);
0|0|0|SEARCH TABLE x1 USING INDEX x1_idx_000123a7 (a=? AND b>?)
sqlite> CREATE INDEX x1ab ON x1(a, b); -- Create the recommended index
sqlite> .expert
sqlite> SELECT * FROM x1 WHERE a=? AND b>?; -- Re-analyze the same SELECT
(no new indexes)
0|0|0|SEARCH TABLE x1 USING INDEX x1ab (a=? AND b>?)
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!
- DANmode 3mo ago> they just tend to make it less obvious you're doing something unperformant Is this being positioned as a strength, in your comment?
- striking 3mo agoIt just is what it is. Sometimes you want to write the obvious query without the DB getting in your way, and other times you want to know as soon as possible that you're doing something that won't scale under exponential load. At this point in my career I prefer the latter, but the former will always have a special place in my heart.
- simonw 3mo agoI've worked with large MySQL databases that used row-based replication and things like an UPDATE or DELETE that affected millions of rows had to be applied in batches there, because otherwise one SQL query might result in a million updated rows needing to be sent to all of the replicas at once.
- cogman10 3mo agoYeah, I think anyone that's done significant database work has come to the understanding that large updates need to be done in batches, otherwise you nuke performance. Once you get to about 1M rows of data, batching is essential.
- Groxx 3mo agoI've built custom batch-processors because percona-toolkit's automatic stuff was far too aggressive :| 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.
- deleted 3mo ago[deleted]
- bananamogul 3mo agoAnother side effect of deleting 10 million rows in some databases (e.g., Oracle) is that the database writes out 10 million rows' worth of undo, which can swamp the disk space set aside for archive logs if you can't back it up and clear it off fast enough. Committing more frequently help, but if you have large databases and regularly need to purge, the best way in my experience is to use partitioning. Dropping the oldest (or whatever) partition is nearly instant and painless. 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).
- serhii777_123 3mo ago[flagged]
- inigyou 3mo agoLooks similar to EXPLAIN QUERY PLAN: https://sqlite.org/eqp.html https://sqlite.org/eqp.html Raw EXPLAIN dumps bytecode, which is usually much more verbose than you want. EXPLAIN QUERY PLAN dumps a summary.
- xetera 3mo agoIn my experience, SQLite explain plans are by far the most useless of any database out there. No concept of costing, no buffer information, no explain analyze. It's almost like they don't want you looking at it.
- inigyou 3mo agosqlite competes with fopen. If you don't like it, use a real database.
- simonw 3mo agoWhat's your criteria for a "real database"?
- deleted 3mo ago[deleted]
- antonvs 3mo agoA couple of obvious ones would be running as a standalone service, and supporting multiple concurrent writes. Both of these help support transparent handling of multiple concurrent clients, which seems like table stakes for a "real database".
- phil294 3mo ago...performance-wise. Not in general. It obviously does more than just opening a file. 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.