This would be super useful for something I’m working on using datasette, querying across three tables with over 1.5m rows. Going to try the same queries on this tomorrow.
Exactly what I have been hoping to see as well. Analytical queries are a tad too slow for datasette, so I have had to materialize the more interesting roll-ups.
I’ve been using duckdb instead of Pandas these days because it’s much faster on larger datasets plus I can write more compact and complex SQL than I can Pandas constructs. The fact that duckdb can query in memory Pandas dataframes faster than Pandas itself is a plus.
It’s like having a local performant database engine that can query and join across Parquet, CSV, Pandas and now SQLite.
It is a wrapper on Sqlite, but I'm looking to switch to duckdb because its dialect is more comprehensive.
Previously, I was using ruby, sometimes python, and sometimes postgresql to process CSV. But it wasn't convenient enough. Most of the times I just tried to use Excel, but formula is so hard to use.
Would love to see a benchmark comparing SQLite, DuckDB and this SQLite/DuckDB combo with the same queries and data.
Is this adapter copying the contents of the SQLite db into DuckDBs own memory layout in order to optimise the queries or is it just “proxying” to the SQLite library?
I really like the DuckDB philosophy, and the maintainers were really responsive and helpful with my docs questions. Didn't end up using it because the compressed database files were much larger than SQLite for my data (weird use case, admittedly), and the .NET libraries were poor. I look forward to trying it out again, though.
I tried all of the available compression formats in parquet, natively in duckdb, and compressing raw duckdb files as a whole. IIRC just GZIPing SQLite files beat them all. The SQLite files were much smaller uncompressed (~220mb vs 800mb) which was probably most of the difference.
DuckDB's Node bindings leave a lot to be desired, as well. WASM bindings have a different API surface, too. From my reading of the code and docs, it looks like duckdb 3p bindings for anything other than python is in dire need fit and polish.
We're still on a journey to explore what APIs work best with JavaScript but the differences between WASM and Node are on purpose.
DuckDB-Wasm has isolated wasm heap memory and runs in a separate web worker.
That's why we serialize everything as Arrow IPC buffer and pass the ArrayBuffer through the workers message API as transferrable.
On Node.js, we can interact with DuckDB much more easily and don't want to pay the price for the IPC stream every time.
The truth is, we tried quite a few different APIs and this turned out the be a good trade off between efficiency and convenience.
But I'm happy to discuss this further if you have any suggestions.
23 comments
[ 4.9 ms ] story [ 75.6 ms ] threadOr are you talking about something else?
I’ve been using duckdb instead of Pandas these days because it’s much faster on larger datasets plus I can write more compact and complex SQL than I can Pandas constructs. The fact that duckdb can query in memory Pandas dataframes faster than Pandas itself is a plus.
It’s like having a local performant database engine that can query and join across Parquet, CSV, Pandas and now SQLite.
Kinda like a quick efficient local Spark.
It is a wrapper on Sqlite, but I'm looking to switch to duckdb because its dialect is more comprehensive.
Previously, I was using ruby, sometimes python, and sometimes postgresql to process CSV. But it wasn't convenient enough. Most of the times I just tried to use Excel, but formula is so hard to use.
Is this adapter copying the contents of the SQLite db into DuckDBs own memory layout in order to optimise the queries or is it just “proxying” to the SQLite library?
Just curious, what format did you use?
If it's Parquet, it does column compression via Snappy by default (which more optimized for speed than size).
You can change the compression to ZSTD or GZIP for higher compression ratios.
https://duckdb.org/docs/data/parquet
DuckDB's Node bindings leave a lot to be desired, as well. WASM bindings have a different API surface, too. From my reading of the code and docs, it looks like duckdb 3p bindings for anything other than python is in dire need fit and polish.
But I'm happy to discuss this further if you have any suggestions.
DuckDB, similar to SQLite, is an in-process query engine for local data.