SQLite 3.43
sqlite.org
sqlite.org
So just to see if this was still a problem, I recompiled findsight.ai with this new amalgamation and yep, all queries that used to take about 300ms now take 3 to 10 seconds to complete. There's no difference in the query plans created, it's just a bug.
I'll probably bisect by commit (or whatever Fossil calls it) at some point to nail down what changed, but then again 3.39 is rock solid enough I don't have a reason to upgrade. In the meantime, if you're running into one of these edge cases, try downgrading to 3.39. I also have no gripes, it clearly works well enough for many people (since by now most bundles and bindings are running 3.4X).
Variants[1][2] of this bug have been reported, and some apparently fixed, but this continues to be an issue.
And my reference concerned this comment (which I may have misunderstood, sorry if that's the case):
> It is not planned for any patch releases because it is only a performance issue and not a malfunction.
If the bug is closed, and you still see it happening, file a new bug with a reproducible example so that the maintainers know it's (still) happening. And if that example means setting up a small dedicate repo with enough data to show the problem off, that's probably worth doing.
Well the sqlite devs should be aware of your specific test case (the specific query that is still 3 orders of magnitude slower). Are they?
After this the ball is with them
https://www.sqlite.org/cgi/src/info/609fbb94b8f01d6792e5941a...
This seems to have had a wider fallout, from there (thanks Fossil, cool feature!) I found the reference back to https://sqlite.org/forum/forumpost/64d36440e473516c, which reports another similar regression. Simply patching 3.43 to remove this cost adjustment doesn't fix the regression though, so I'll keep digging.
Edit: a workaround seems to be an ultra-agressive use of ANALYZE to give the autoindexing a better chance. Setting the analyze limit to -1 (unlimited) and forcing all pool connections to ANALYZE on close (i.e. not relying on PRAGMA optimize) does "fix" the regression the next time the service runs.
Thanks for diving into this btw.
https://www.sqlite.org/cpu.html
Regardless, I’d suggest opening a reproducible bug. They are really responsive to those types of things.
SQLite is one of the few product I know that only ships if there are no open bugs.
Couple hiccups like making sure when creating tables to use strict mode and enabling foreign keys that every one needs to read about.
I exported about 8million rows from my Apple health records (about eight years for something’s like steps from before watch) and into a SQLite database from the xml dump using python (twice as fast a rust for some reason) in about 110 seconds. Dates are stored as Unix time stamps. Had ChatGPT generate some queries that showed my percent change in things like resting heart rate for each year using LAG and AVG return in less than a second.
My job at work https://unsupervised.com/ does a lot of KPI work so I’ve been interesting in learning how to to correlate data to show improvements to KPIs (in my case resting heart rate or lean body mass)
Nextjs server components and https://github.com/kysely-org/kysely also make working with SQLite a breeze.
The data that the graph is based on is here (and is up to date): https://sqlite.org/docsrc/file?name=misc/speed-size-graph.od...
Looks like 3.43.0 uses about 9% fewer cpu cycles than 3.26.0 (the last version in that graphic).
https://stackoverflow.com/questions/1643365/why-no-love-for-...
I mean I was never crazy, RDBMS are useful, but SQL languages feel awkward not just to me.
I'm curious about an alternative to the SQL language or API for a database system, and I guess that sort of RDBMS would also have a very different design.
BTW the question I asked was about querying data without ever using a SQL language, like tapping directly into the data. Of course a DB system is not designed to be used like this, but to me, it should, or at least there should be better alternative to SQL.
For some reason, python list comprehension/dict comprehension and sets somehow feel like a weird, light alternative to a database system. I just get the raw data with SQL and do data things I should do with SQL, and even if it's ill-advised, it feels so much better, and it works.
Not to dismiss sqlite though, it's a wonderful software.
A good example of this is Redis. Redis is exactly what you described. It's just data structures as a service.
For Redis - this works. It's its purpose. It's a tool. Same with KV stores. Again, you are working directly with the storage mechanism.
However, now you're limited to that implementation detail. With SQL, or a traditional DB, your SELECT x by y could be served by a hashmap today, and maybe a btree tomorrow, and you don't need to change your app.
Whether this makes sense for you depends entirely on your application. For loops in Python are okay for some apps. It won't work when you have a billion records.
- It's backed by a mathematical theory (relational algebra).
- SQL has an international standard (although no implementation really follows this correctly if I'm not mistaken).
Lots of other languages have their own take on lists/arrays/dicts, but SQL is (generally) universal and often backwards compatible due to its extremely simple syntax.
I have a small hobby project (a frontend for a TV tuner device) that originally held channel and EPG data in a few python dicts.
This was clunky because I had to manually JSON-encode them and write them out, and saying "rewrite an entire 500kb config file when just 10 bytes had changed" always felt wrong. (Although, as I gather it, on an SSD, rewriting one byte may as well be the same as the whole file).
I replaced the guts with SQLite in part for that. I figured it's used enough for "we changed one row of 5,000" use cases that it would be optimized for that case. It also made cross-references simpler-- multiple table scans can be chained in a single statement, rather than having to nest queries and pass intermediary results from scan to scan. (For example, some program-guide data is more sensibly cross-referenced by physical channel number, and some by "display" channels, so being able to join in a table that maps the two streamlines stuff)
I can't imagine this being particularly pleasant if your data is too large or you want to do anything more complicated than a single SELECT.
A lot of databases are just data structures written to disk in an efficient manner. There's nothing really stopping anyone from doing the same and implementing the query language as a direct API to these on-disk structures. In my mind this all just falls under the general category of random access files. This can be extremely performant if you are very knowledgeable of your use case and storage characteristics.
SQL on the other hand is a query layer on top of these data structure engines which gives you a more general purpose language to work with. It doesn't map directly to the underlying data structures so you can model data differently at a more abstract level. Ultimately it will be compiled down into some sort of routine that attempts to perform data access to those underlying data structures in as optimized a manner as it can figure out.
It's all about trade-offs. There are also plenty of storage engines that already give you an on-disk data structure in a box (e.g. RocksDB).
I agree (making https://tablam.org to try a fix & working on https://github.com/clockworklabs/SpacetimeDB in the SQL conformance).
Before I think SQL was bad. *Now I'm certain*. SQL is absurdly massive for things that could have collapse all the syntax 10x or more with more principled approach (and ACTUALLY doing relational model).
However, working in an RDBM now I also understand why is not desirable to make "raw" calls to the DB: The engine MUST mediate all the calls to make things works (from query optimization, execution, iteration, lock management, transaction management, etc).
Is incredible how much sophistication is in a simple `SELECT * FROM table`.
What I wish is to build a `Wasm-like` IR so that is what anybody target, and `SQL` is not the mediator.
Hey Mario, I believe I referenced this when we were working together, but one attempt at this was/is MonetDB's "MAL" VM: https://www.monetdb.org/documentation-Jun2023/dev-guide/mone...
It's a fairly minimalistic somewhat assembly-like ("Monet Assembly Language", MAL) virtual machine that executes both program flow and relational algebraic operations (against purely binary relations, from which they are able to compose higher arity relations): https://www.monetdb.org/documentation-Jun2023/dev-guide/mone...
It's not a bad place to start from as an idea.
SQLite itself has a virtual machine, but it's just too highly specific for its own storage engine and transaction model
Another approach is what Umbra does, which is (in some circumstances) to generate a query plan in the form of LLVM intermediate representation and then compile it down to native code (one could also compile to web assembly, I suppose, if you love the crappy performance :-) ). That works best for longer-running analytical queries, and they have other approaches for shorter, ad-hoc transactional ones
https://link.springer.com/article/10.1007/s00778-020-00643-4 gets into it.
yes, that is the direction...
My weapon of choice when using Python is SQLAlchemy; it gives you composability.
Also, there's https://github.com/PRQL/prql that compiles to SQL.
Calling RDBMSs “useful” is quite the understatement. They are among the most commercially successful software products of all time, and the theory backing them has endured with us for over 50 years now.
BTW the question I asked was about querying data without ever using a SQL language, like tapping directly into the data. Of course a DB system is not designed to be used like this, but to me, it should, or at least there should be better alternative to SQL.
This approach has been tried over and over again with little success to show for it. Considering that a good number of working programmers would be hard pressed to recall what ACID even stands for, much less with any rigor, you have to ask yourself if you really want to entrust valuable data to them. Furthermore, mucking around with low level storage and access patterns adds tremendous amount of work to development, it is very unpleasant.
Does anyone have examples of the complex/ big projects that use SQLite ? I’m curious what problems they ran into, and how they were addressed.
There were other systems that would use these hundreds of databases a few gigabytes each and create models from them for early failure detection and other things.
I wish I could say this was difficult or didn't work but pretty basic scripting and sqlite on commodity hardware doing normal cpu computation got us there beyond our expectations using cheap digital ocean VPS servers with modest memory. I can be a pretty obnoxious brat when open source things are broken and nobody is taking them seriously. Here though, people took it seriously and it wasn't broken. I'm continually impressed by it. This was pre-pandemic for a timeline.
"Bedrock was built by Expensify, and is a networking and distributed transaction layer built atop SQLite, the fastest, most reliable, and most widely distributed database in the world." (https://bedrockdb.com/)
One of the problems is that the history may grow excessively large, which SQLite handles well but the History UI does not :)
One twitter-famous Core Data/SQLite engineer's bio on Twitter used to read "Over one trillion databases served", or something to that effect.
It seems to use a manifest with hashes for every file generated by their VCS.
It appears to be basically sha3sum against an included manifest file.
https://sqlite.org/src/file?name=tool/src-verify.c
I am kinda perplexed at the threat model resolved by including a manifest in the same tree as the adulterated sources, checked by a running a tool compiled from those same adulterated sources.
[god damn is it annoying to navigate through fossil to actually find a file]
Interesting. What's the thing with non-default branches that's broken?
The compact navigation bar hides the branch (this part has been fixed). Scroll down and you can no longer jump to a different branch without scrolling back to the top of the page… which takes a while because the code view widget (UGH) punts navigation key presses off into a black hole.
The new symbol navigator only links to the default branch even if you're on a different branch.
The compare branches button is gone. You have to open a pull request to compare branches (or manually craft the path).
Hyperlinks within markdown that reference the repo only go to the default branch.
Searching within a repo only searches default branch even if you're searching from a different branch. Granted the search results have never been good, but still.
More or less pick a non-default branch and experiment a little. Chances are you'll be forced back to the default branch in short order. Even when the ref name is a parameter in the URL. And that's about the nicest thing I can say about the new Github UI. Pretty much everything else is just worse universally (blurry text, laggy find within page, nothing rendered if javascript is disabled, etc, etc, etc).