5 ms·
Notes on the SQLite DuckDB Paper
- zX41ZdbW 4y agoDuckDB is obviously faster than SQLite on analytical queries, from 10 to 11 000 times on a benchmark [1]. But in comparison to SQLite, DuckDB cannot run all the queries due to worse memory management. It cannot run 12 out of 43 queries. [1]: https://benchmark.clickhouse.com/#eyJzeXN0ZW0iOnsiQXRoZW5hIChwYXJ0aXRpb25lZCkiOmZhbHNlLCJBdGhlbmEgKHNpbmdsZSkiOmZhbHNlLCJBdXJvcmEgZm9yIE15U1FMIjpmYWxzZSwiQXVyb3JhIGZvciBQb3N0Z3JlU1FMIjpmYWxzZSwiQnl0ZUhvdXNlIjpmYWxzZSwiQ2l0dXMiOmZhbHNlLCJjbGlja2hvdXNlLWxvY2FsIChwYXJ0aXRpb25lZCkiOmZhbHNlLCJjbGlja2hvdXNlLWxvY2FsIChzaW5nbGUpIjpmYWxzZSwiQ2xpY2tIb3VzZSI6dHJ1ZSwiQ2xpY2tIb3VzZSAoenN0ZCkiOmZhbHNlLCJDcmF0ZURCIjpmYWxzZSwiRGF0YWJlbmQiOmZhbHNlLCJkYXRhZnVzaW9uIjpmYWxzZSwiRHJ1aWQiOmZhbHNlLCJEdWNrREIiOnRydWUsIkVsYXN0aWNzZWFyY2giOmZhbHNlLCJFbGFzdGljc2VhcmNoICh0dW5lZCkiOmZhbHNlLCJHcmVlbnBsdW0iOmZhbHNlLCJIZWF2eUFJIjpmYWxzZSwiSW5mb2JyaWdodCI6ZmFsc2UsIk1hcmlhREIgQ29sdW1uU3RvcmUiOmZhbHNlLCJNYXJpYURCIjpmYWxzZSwiTW9uZXREQiI6ZmFsc2UsIk1vbmdvREIiOmZhbHNlLCJNeVNRTCAoTXlJU0FNKSI6ZmFsc2UsIk15U1FMIjpmYWxzZSwiUGlub3QiOmZhbHNlLCJQb3N0Z3JlU1FMIjpmYWxzZSwiUXVlc3REQiI6ZmFsc2UsIlJlZHNoaWZ0IjpmYWxzZSwiU2luZ2xlU3RvcmUiOmZhbHNlLCJTbm93Zmxha2UiOmZhbHNlLCJTUUxpdGUiOnRydWUsIlN0YXJSb2NrcyAodHVuZWQpIjpmYWxzZSwiU3RhclJvY2tzIjpmYWxzZSwiVGltZXNjYWxlREIgKGNvbXByZXNzaW9uKSI6ZmFsc2UsIlRpbWVzY2FsZURCIjpmYWxzZX0sInR5cGUiOnsic3RhdGVsZXNzIjp0cnVlLCJtYW5hZ2VkIjp0cnVlLCJKYXZhIjp0cnVlLCJjb2x1bW4tb3JpZW50ZWQiOnRydWUsIkMrKyI6dHJ1ZSwiTXlTUUwgY29tcGF0aWJsZSI6dHJ1ZSwicm93LW9yaWVudGVkIjp0cnVlLCJDIjp0cnVlLCJQb3N0Z3JlU1FMIGNvbXBhdGlibGUiOnRydWUsIkNsaWNrSG91c2UgZGVyaXZhdGl2ZSI6dHJ1ZSwiZW1iZWRkZWQiOnRydWUsIlJ1c3QiOnRydWUsInNlYXJjaCI6dHJ1ZSwiZG9jdW1lbnQiOnRydWUsInRpbWUtc2VyaWVzIjp0cnVlfSwibWFjaGluZSI6eyJzZXJ2ZXJsZXNzIjpmYWxzZSwiMTZhY3UiOmZhbHNlLCJMIjpmYWxzZSwiTSI6ZmFsc2UsIlMiOmZhbHNlLCJYUyI6ZmFsc2UsImM2YS40eGxhcmdlLCA1MDBnYiBncDIiOnRydWUsImM2YS5tZXRhbCwgNTAwZ2IgZ3AyIjpmYWxzZSwiZjE2cyB2MiI6ZmFsc2UsImM2YS40eGxhcmdlLCAxNTAwZ2IgZ3AyIjpmYWxzZSwicmEzLjE2eGxhcmdlIjpmYWxzZSwicmEzLjR4bGFyZ2UiOmZhbHNlLCJyYTMueGxwbHVzIjpmYWxzZSwiUzI0IjpmYWxzZSwiUzIiOmZhbHNlLCIyWEwiOmZhbHNlLCIzWEwiOmZhbHNlLCI0WEwiOmZhbHNlLCJYTCI6ZmFsc2V9LCJjbHVzdGVyX3NpemUiOnsiMSI6dHJ1ZSwiMiI6dHJ1ZSwiNCI6dHJ1ZSwiOCI6dHJ1ZSwiMTIiOnRydWUsIjE2Ijp0cnVlLCIzMiI6dHJ1ZSwiNjQiOnRydWUsIjEyOCI6dHJ1ZSwic2VydmVybGVzcyI6dHJ1ZSwidW5kZWZpbmVkIjp0cnVlfSwibWV0cmljIjoiaG90IiwicXVlcmllcyI6W3RydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWVdfQ== https://benchmark.clickhouse.com/#eyJzeXN0ZW0iOnsiQXRoZW5hIC...
- trinovantes 4y agoI'm surprised duckdb failed to run simple count/groupby queries. Is it simply OOM? If so, do you know how much more it would take to complete those queries?
- jerryjerryjerry 4y agoMy wild guess is lack of some functions, e.g., regexp, but need to take a deep look.
- 1egg0myegg0 4y agoIt's likely out of memory issues with many many distinct values in an aggregated column, or with a very large join intermediate. The joins are now able to handle larger than RAM, and larger than RAM intermediates in a group by is on the roadmap!
- jerryjerryjerry 4y agoLooking at the failed queries, yes, OOM might be one big factor due to aggregations (there is no join in these queries though).
- wenc 4y agoI’m a big fan of DuckDB. However it is true that memory usage is not optimized right now — I’ve had trouble with very large groupbys even with pragmas on memory limits (but I’m talking huge limits like 250 GB RAM). I’ve had to remodel and break down my problem to work around these limitations. I believe it’s on the DuckDB roadmap to get better at memory use but apart from that it’s very very good at what it does — ie complex analytical queries. It vectorizes ops so everything is fast and efficient.
- simonw 4y agoI wish more people would publish this kind of content. Academic papers aren't really designed for casual reading - I always find them to be a bit of a slog. It would be great if it was much more common for people to share their notes like this. I'm going to try and remember to write more of these in the future. I should add that I found this particular paper to be a whole lot more readable than most!
- mlerner 4y agoI try to do paper reviews (like https://news.ycombinator.com/item?id=31379383 https://news.ycombinator.com/item?id=31379383) because it is a great way for me to learn more and _hopefully_ share in a way that is useful for others . My writing abilities are still a work in progress :)
- simonw 4y agoThat one is great, thank you!
- snthpy 4y agoGreat suggestion and thank you for leading with a nice example! I enjoyed reading your notes and benefited immensely from them.
- wiremine 4y agoSimon's writing has always been crisp and insightful. If I remember correctly, Simon comes out of a newspaper background. I wonder how that's influenced his approach on these sorts of technical topics.
- svnpenn 4y agoWhen I look at comparisons like this, one of the first red flags I check for is binary size. First SQLite: https://sqlite.org/2022/sqlite-tools-win32-x86-3390200.zip https://sqlite.org/2022/sqlite-tools-win32-x86-3390200.zip Result: "1,108,480 sqlite3.exe". Then DuckDB: https://github.com/duckdb/duckdb/releases/download/v0.4.0/duckdb_cli-windows-i386.zip https://github.com/duckdb/duckdb/releases/download/v0.4.0/du... Result: "32,052,736 duckdb.exe" (already stripped) To me, this says that SQLite is a carefully considered codebase, with great care put into making a lean and high quality piece of software. While DuckDB seems to have the hallmark of bloated C++ code, with plenty of waste, and little care to these details.
- 1egg0myegg0 4y agoHmm, I would recommend reading a sample of the code first!
- IshKebab 4y agoThat's certainly one metric. But you can pick any single metric and find one is better than the other (e.g. data type support). You have to look at it overall.
- curious_cat_163 4y agoI wonder how well-motivated the OLAP use cases are. Is there really a lot of demand of doing analytics on edge devices? I see a windmill example in the introduction of a paper linked from the post above. Is that really a big thing in the windmill space? Are modern windmills needing to use embedded devices on them to perform real time analytics that would be enabled by OLAP engines and queries like that? Are there other such cases that folks know where this would be very useful?
- iknownothow 4y agoA lot of recent tooling around SQLite has opened up some new possibilities. The biggest one I can think of is Litestream which has allowed SQLite to have distributed reads. This makes SQLite a strong contender for serverless and extremely cheap OLAP. There have been a lot of posts about serverless Postgres as well lately in the form of neon.tech. If SQLite had better performance for OLAP queries, there is no way I wouldn't explore it as an option for work because it's so darn easy to use. The operational or hosting or cloud costs of other OLAP database is insanely high in comparison. P.S. It's my personal opinion and experience that most companies have only about 1TB or less of analytics data. Large OLAP databases are just a waste for such small data but currently apart from DuckDB there aren't any cheap competitors for these small use cases. Postgres and SQLite are too slow (without some additional engineering).
- chrisjc 4y agoI've been thinking about very similar needs, but perhaps accomplished in a somewhat different way. It would be really neat if SQLite or DuckDB implemented a way to create (register) read-only external tables (like Apache Iceberg). That way your large OLAP database (Snowflake, etc) could manage (DDL & CRUD) the external table, while SQLite and DuckDB would consume from it and automatically get all of the updates/deltas to the external table. clicks --> Iceberg bits --> Snowflake/MPP <--> table --> DuckDB pieces --> on S3 Snowflake could take care of all the analytical heavy lifting continually preparing/updating/unloading data to the Iceberg table in an analytics ready/friendly form (lags, leads, etc built into data). DuckDB could watch for changes on the Iceberg table (manifest) and update its internal representation of the external table as it changes. DuckDB can already read parquet files out on S3 so it should be somewhat trivial to implement? DuckDB could then handle all of the lightweight slicing/dicing of the analytics data; projections, counts, pagination, etc Here's an example of Snowflake managing an Iceberg table: https://www.youtube.com/watch?v=Kz5cWY_vRwU https://www.youtube.com/watch?v=Kz5cWY_vRwU
- snthpy 4y agoHow do people see Apache DataFusion fitting into this space? I've also been following DuckDB for a while and remain very excited about it. I've only recently become aware of DataFusion and am building a small project with it but I've not seen much discussion on it or any benchmark comparisons. DataFusion seems to be part of the Apache Arrow stable and they've done a great service to analytics space, with DuckDB also being big supporters. I'm hoping this means that DataFusion has a good chance of also getting good traction and adding another option in this space. Also Substrait for future backend independence.
- snthpy 4y agoI found this tweet from Andy Grove and in the linked benchmarks DataFusion is just behind DuckDB. https://twitter.com/andygrove_io/status/1559920848882073600?t=pECnkNsg9a6U2Lutfyp3Ag&s=19 https://twitter.com/andygrove_io/status/1559920848882073600?...
- yread 4y agoDiscussion of the paper yesterday https://news.ycombinator.com/item?id=32675861 https://news.ycombinator.com/item?id=32675861
- eimrine 4y agoI am writing a pet project and will try this as my first SQL database.