I shared some notes on this on my blog, because I'm guessing a lot of people aren't quite invested enough to read through the whole paper: https://simonwillison.net/2022/Sep/1/sqlite-duckdb-paper/
Indeed, an excellent summary.
SQLite: Past, Present, and Future
81–90 of 147 posts
Re: SQLite: Past, Present, and Future
#82Why do people have to publish papers in a weird two column academic format instead of something that's more easily readable?
I read papers most of the time on phone and these two column papers are such a PITA to read lol
Re: SQLite: Past, Present, and Future
#83Regarding hash joins, the SQLite documentation mentions the absence of real hash tables [0] SQLite constructs a transient index instead of a hash table in this instance because it already has a robust and high performance B-Tree implementation at hand, whereas a hash-table would need to be added. Adding a separate hash table implementation to handle this one case would increase the size of the library (which is desig…
That's correct, the optimizations from this paper became available in SQLite version 3.38.0.
As we were writing the paper, we did consider implementing hash joins in SQLite. However, we ultimately went with the Bloom filter methods because they resulted in large performance gains for minimal added complexity (2 virtual instructions, a simple data structure, and a small change to the query planner). Hash joins may indeed provide some additional performance gains, but the question (as noted above) is whether they are worth the added complexity.
Re: SQLite: Past, Present, and Future
#84>SQLite is primarily designed for fast online transaction processing (OLTP), employing row-oriented execution and a B-tree storage format. I found that claim to be fairly surprising, SQLite is pretty bad when it comes to transactions per second. SQLite even owns up to it in the FAQ: >it will only do a few dozen transactions per second.
> SQLite is pretty bad when it comes to transactions per second. SQLite even owns up to it in the FAQ: "it will only do a few dozen transactions per second." That is an extremely poor quote taken way out of context. The full quote is: FAQ: "[Question] INSERT is really slow - I can only do few dozen INSERTs per second. [Answer] Actually, SQLite will easily do 50,000 or more INSERT statements per second on an average d…
Thanks for the clarification. It's true that transaction latency is limited by the write speed of the storage medium. However, an "average desktop computer" these days has an SSD that can support tens of thousands of transactions per second, depending on the workload.
Re: SQLite: Past, Present, and Future
#85SQLite vs Postgres for a local database (on disk, not over the network): who wins? (Each in their most performance oriented configuration)
I think the (unsatisfying) answer is "it depends". There's a huge amount of diversity in database workloads, even among the workloads served by SQLite as we mention in the paper.
For read-mostly to read-only OLTP workloads, read latency is the most important factor, so I predict SQLite would have an edge over PostgreSQL due to SQLite's lower complexity and lack of interprocess communication.
For write-heavy OLTP workloads, coordinating concurrent writes becomes important, so I predict PostgreSQL would provide higher throughput than SQLite because PostgreSQL allows more concurrency.
For OLAP workloads, it's less clear. As a client-server database system, PostgreSQL can afford to be more aggressive with memory usage and parallelism. In contrast, SQLite uses memory sparingly and provides minimal intra-query parallelism. If you pressed me to make a prediction, I'd probably say SQLite would generally win for smaller databases. PostgreSQL might be faster for some workloads on larger databases. However, these are just guesses and the only way to be sure is to actually run some benchmarks.
Re: SQLite: Past, Present, and Future
#86Re: SQLite: Past, Present, and Future
#87Earlier quoted context omitted.
> less interpreting/serialization/deserialization/copying/... = higher performance Unfortunately for many database workloads you are overestimating the relative cost of this factor. > even if the SQLite query engine is slightly less efficient than PostgreSQL And this is absurd - the postgresql query engine isn't just "slightly" more efficient. It is tremendously more sophisticated. People using a SQL datastore as a g…
With SQLite, though, you could reasonably just skip doing fancy joins and do everything in tiny queries in tight loops because SQLite is literally embedded in your app’s code. You can be careless with SQLite in ways you cannot with a monolithic database server because of that reason. I still agree there are use cases where a centralized database is better, but SQLite is a strange beast that needs a special diet to pe…
Sometimes. This is only universally true if your datasets are tiny and your access patterns are simple. Moving a shitty suboptimal O(n^2) or worse algorithm locally when something linear or better is possible is going to hurt no matter where the DB is.
> but SQLite is a strange beast that needs a special diet to perform best.
I don’t see what is strange about it - for large datasets it’s the same complexity issues as anywhere else.
Not sure specifically what your comment is trying to add, since I acknowledged the type of use case SQLite excels in - those where roundtripping are a dominating cost and “k-v” stores, ie simple queries. My entire point is that those are a common but still niche use case.
Re: SQLite: Past, Present, and Future
#88Why do people have to publish papers in a weird two column academic format instead of something that's more easily readable?
Ha ha .. that is what the conference requires. Turns out that there is research that shows that when you are reading paper printed on paper this 2-column format is good for readability and not wasting paper. Conferences still insist on this format even though most people print papers. Now the good news is that these days, conferences have an accompanying video associated with the paper, and that may be a good place t…
Thanks, will lookout for the video
(I tend to read most things on a screen and find two columns of small text tiring)
Re: SQLite: Past, Present, and Future
#89I'm confused that why in Figure3, seems in Raspberry Pi, latency is slower than same queries' latency in cloud server. Did I missed something?
Re: SQLite: Past, Present, and Future
#90After seeing one diagram: Why the hack are we still talking to databases with SQL strings and not directly specifying the Query-AST? Admins, sure (a fancy UI could help there as well) but why in our code?