The Untold Story of SQLite (2021)
corecursive.com
corecursive.com
On a quiet day, I was able to save the temporary table used as part of the process and run the problematic query against it in an isolated fashion.
The query returned an extremely high number of results and when I discovered this I questioned my SQL-fu, my sanity and my trust in computers.
I found that we were hit by a bug that was fixed 6 years before I discovered it (https://sqlite.org/src/info/6f2222d550f5b0ee7ed). Sqlite's query planner assumed that a field with a not null constraint can never be null, which isn't the case for the right hand table in a left join.
I fixed it by adding a not null check in the query and then later by updating the library. After that the 1 hour query ran in ~700 ms.
This faster run time also helped with smaller projects and in the end allowed extending our test suite considerably.
Tldr: Keep your dependencies up to date.
I'd rather TL;DR it as use query EXPLAIN to see what may be slowing any query down.
At the same time there have been roughly 60 release notes that mention performance since 3.8.6, so these came for free with the update: https://www.sqlite.org/changes.html
> it’s the old joke of, you get 95% of the functionality with the first 95% of your budget, and the last 5% on the second 95% of your budget.
repeats every time
Hmm, maybe I didn't detect the sarcasm in your reply.
Basically issue tracking added to git repo directly
That being said my personal project are just push/pull/commit so I don't see the reason to change. Maybe some script to auto-push commits every hour or something but, well I have backups so that's not really required either
https://news.ycombinator.com/item?id=27718701 (2 years ago, 95 comments)
Looking into it as an outsider, it seems that a key inflection point was their adoption of a really industrial-strength test discipline at just the right time. Not only was it impressive to have written the engine, but it seems to be doubly impressive to knuckle down for a year to get the test coverage - and it paid off in spades.
Others say "freedom" is just another word for "nothing left to lose."
By the first definition though, I think one should keep in mind "With great [freedom] comes great responsibility."
> Wow, I’ve got an SQL database running on my Palm Pilot.
Weirdly, my thesis in 2000 was to write an SQL parser for Palm (Palm V - still have it). I remember there being such a massive gap in the market for a pervasive standards-compliant data storage solution. I used javacc, which is still around I think - I can’t imagine it covered 1% of the features of SQLite though. Bravo!
I've noticed the tendency for .db but I consider it a bad practice, all things considered.
Quantum mechanics?
Shane Harrelson did this for us about 10 years ago. He came up with this huge corpus of SQL statements, and he ran them against every database engine that he could get his hands on. We wanted to make sure everybody got the same answer, and he managed to segfault every single database engine he tried, including SQLite, except for Postgres. Postgres always ran and gave the correct answer. We were never able to find a fault in that. The Postgres people tell me that we just weren’t trying hard enough. It is possible to fault Postgres, but we were very impressed.
We crashed Oracle, including commercial versions of Oracle. We crashed DB2. Anything we could get our hands on, we tried it and we managed to crash it, but the point was that we wanted to make sure that SQLite got the same answers for all of these queries, or equivalent answers, because a lot of these queries, they’re indeterminate and the rows might come out in a different order because you [crosstalk 00:25:10] order by clause, so we wanted to make sure that all the database engines got equivalent answers. Mostly, we wanted to make sure that SQLite was getting the same answers everybody else is.
That’s another test suite, and then we have lots of smaller ones, as well. Between them all, it’s a lot of testing code, and it takes a long time to run.
Value (in the abstract, not just $ sense) accrued around SQL.
At some point, so much value accrued that people were using it for things it wasn't designed to do.
SQLite provided a solution for "people who want to use a database, but don't look like traditional database operators." Turns out there's a lot of those.
That this large userbase existed was a brilliant observation, combined with brilliant execution in shepherding and evolving SQLite since.
And none of the above would've been possible if the SQL interface hadn't been standardized and adopted over the last few decades*.
* Turns out, SQL's 50th anniversary will be 2024
SQLite (and it's founder, Richard Hipp) are an inspirational example of such success.
Which, of course, is very much the case with SQLite. It shows that a small team with a vision and an emphasis on quality can make a product that becomes pervasive in the industry for decades.
EDIT: did some counting, certainly a huge increase.
Threads with "sqlite" in the title:
2022: 346
2019: 142
SQLite has been appearing a lot more often on HN because of a different more recent fad: edge computing.
I suspect popularity here comes in waves through a Katamari Damacy effect. People start reading about a topic, and start posting, thus more people read, research, post, etc... until a saturation point, a cooling off period, and then a rebuild.
[0]: obviously not the same as whats happening on HN, I'd love to see someone pull these numbers from the HN API!
Go and Rust work great with SQLite because all the concurrency is mediated within a single application process.
During the PHP and Ruby years, SQLite did not work that well and still does not, because the different worker processes can't communicate and SQLite has an extremely poor-performing sleep loop around the write lock.
This gave it a low-performance reputation compared to MySQL and Postgres that it has struggled to shake off.
Did not expect to find such a cool anecdote about Postgres here!
Me: Put all in SQLite and write a SQL query.
There’s even a lib for Parquet making analysis on a small number of problematic files quite easy.
(DuckDB is a lot more ergonomic for that kind of thing though - it's really fantastic tech)
Certainly, there are hiring panels that appreciate these sorts of tricks to go around the solution, usually citing “out of the box” thinking, but the majority would probably just say “do it without that solution” or mark you as a fail.
Recent examples:
- https://simonwillison.net/2023/Jan/27/exploring-musiccaps/
- https://simonwillison.net/2022/Aug/21/scotrail/
- https://simonwillison.net/2022/Sep/5/laion-aesthetics-weekno...
The lesson is clear: if you want to win in the long run, you need not just great skills and tech, but you have to go public domain. Or to put it bluntly: #LicensesAreForLosers.
Public domain raises all sorts of challenges for potential adopters of the software that aren't an issue with a more deliberately designed license.
UPDATE: I misremembered this. He does talk about some of the surprise challenges in this interview, but does not go as far as saying that he regretted it: https://changelog.com/podcast/201#transcript-215
""" “We’ll sell you a license for SQLite.”
We do our best to talk them out of it and explain they don’t need this, but for a lot of people it’s cheaper to pay the fee and get the license than it is to convince their lawyers that they don’t need one. """
Priceless! Except that it's not. LoL.
This still doesn't add up to me.
Proof:
For every pair {protocolX,protocolY} where functionality(protocolX) = functionality(protocolY) && isPublicDomain(protocolX) == true && isPublicDomain(protocolY) == false, then speedAndUtility(protocolX) >> speedAndUtility(protocolY).