Hacker News Reader

Comment by mywaifuismeta | Hacker News Reader

Can someone explain what the purpose of duckdb is? From what gather from the docs, it's a column-oriented in-memory SQL database? So I would use it over something like in-memory sqlite simply because it's faster due to vectorization? Or are there other aspects that I'm missing?

View on HN

Like SQLite, it's not necessarily in-memory, it has an on-disk format as well. I haven't given it a massive workout for bigger-than-memory workloads but in theory they are possible.

With that said, the resident memory size of stuff-in-parquet/arrow/duckdb is a lot less in practice than stuff-in-pandas (i know) and stuff-in-sqlite (i believe), so it still enables more in-memory workloads than you might otherwise be able to do.

I really like that you can use duckdb to sql query an on disk directory structure of parquet files with no preloading into RAM or other db formats. Super useful/quick and only one line of code! Instead of selecting from a table you just pass a glob pattern into the SQL - and since it’s a oneliner I can use it in adhoc notebooks too. It even has a to_df() method on the query result so you can get it into pandas for further manipulation

As you noted, it's column-oriented, so designed for OLAP workloads rather than OLTP workloads. Think data warehousing, BI, Big Data analytics, feature engineering ...

But isn't it in-memory? In my experience, data warehousing, BI, analytics, etc are usually defined by large data in data lake or warehouse that doesn't fit into memory. How would duckdb help here?

DuckDB can certainly handle larger than memory data! It's a streaming engine, so it doesn't need everything in memory all at once. Some operators (like sorting) can also buffer out to disk if you run low on memory mid-computation. We are working to expand that to more operators! It also can selectively read from parquet for only the columns and row groups that you need. (Disclaimer - I do documentation for DuckDB!)

No it can query larger-than-memory datasets.

Reply on news.ycombinator.com