8 ms·

DuckDB is terrific. I'm bullish on its potential for simplifying many big data pipelines. Particularly, it's plausible that DuckDB + Parquet could be used on a

DuckDB is terrific. I'm bullish on its potential for simplifying many big data pipelines. Particularly, it's plausible that DuckDB + Parquet could be used on a large SMP machine (32+ cores and 128GB+ memory) to deal with data munging for 100s of gigabytes to several terabytes, all from SQL, without dealing with Hadoop, Spark, Ray, etc. I have successfully used DuckDB like above for preparing an ML dataset from about 100GB of input. DuckDB is undergoing rapid development these days. There have been format-breaking changes and bugs that could lose data. I would not yet trust DuckDB for long-term storage or archival purposes. Parquet is a better choice for that.

RobinL 4y ago
I'd love to hear any real world experiences of anyone who's tried to run jobs that would usually require a spark cluster on a single machine with loads of cores and memory. How big can you go, and how does speed compare to Spark? (I'm guessing significantly faster from my experience using Duckdb on smaller machines)
- jakewins 4y ago
  I used it as glue for a monthly job, last week, processing ~1B event records, juggling them a bit and pushing them as 2M parquet segment files to S3. Mixed experience, would definitely not put it in a system that isn’t ok crashing frequently. It segfaults on Alpine (argh, C++!), and force exits the whole NodeJS process when it gets unexpected HTTP responses from S3. In an archive run of 2M pushes it’ll crash the process 4-5 times. Overall still really, really like it, but learned to not trust it
  - tracker1 4y ago
    Node + AWS for data ingress has been pretty painful in my experience (mostly dynamo feeds from large csv (whois database)). In the end, rewrote in C# (core 2) and it was able to complete more reliably. I'm guessing that go and rust would also be better. I like node, really like JS, but I just think that maybe the AWS libraries aren't that great in the space. It would run for 3-5 hours, then just blow up unexpectedly, even with plenty of memory overhead, and not really bandwidth limited, with appropriate retries and slowdown for dynamo rejections. If I never have to write ETL pipelines again, I won't be upset about it.
    - Master_Odin 4y ago
      There was a bug that was recently fixed in node 16.17+ that was causing hard node processes crashes when doing stuff with S3 and I think had to do with receiving multiple packets at once or something.
- wenc 4y ago
  I have a single machine EC2 instance with 32 cores and 240GB memory and about 200 GB of partitioned Parquet files. I use DuckDB and Python with complex SQL (window functions, inequality joins, quantile functions etc) to extract data from this data. Because it’s a single machine (no distributed cluster) DuckDB can heavily parallelize and vectorize. I don’t know if I can give you perf numbers but complex analytic queries over the entire dataset regularly finish in 1-2 mins (not scientific since I’m not telling what kinds of queries I’m running). I’ve used Spark SQL and DuckDB overall is just more ergonomic, less boilerplate and is much faster since it is so lightweight. Granted DuckDB can only process data on one machine (whereas Spark can scale up indefinitely by adding machines) but most data sets I work with fit on a single beefy machine. Distributed computing — most of the time, you ain’t gonna need it. It’s like StackOverflow: it serves 2B requests a month but only runs on a few on-prem servers. Most people think this is impossible but you can actually do a lot with very few machines if you’re smart about it. Same with data. Big data is overrated.
  - RobinL 4y ago
    Thanks - very interesting
- SnowflakeOnIce 4y ago
  My experience with a modest machine and a ~100GB dataset was that DuckDB was significantly easier to use and much faster (20x) than Ray Data. Have not compared directly with Spark. There was no cluster to set up or administer using DuckDB.
- adgjlsfhk1 4y ago
  spark is just a tool to let you take a computation that would run in an hour on your laptop if coded properly and send it to a server with 1000 cores where it runs in 2 hours.
nerdponx 4y ago
As far as I can tell, DuckDB is an alternative to "data frame" libraries like Data.table, Polars, Pandas, etc. Is that the case? What makes DuckDB a better choice than, say, Polars?
- corford 4y ago
  This blog post offers a nice summary: https://motherduck.com/blog/six-reasons-duckdb-slaps/ https://motherduck.com/blog/six-reasons-duckdb-slaps/
  - nerdponx 4y ago
    The blog post doesn't really make a comparison between DuckDB and data frame libraries. It mentions that the DuckDB Python bindings can interoperate with Pandas, but it doesn't really explain why you would use DuckDB instead of Pandas, or Polars (which is both faster and more portable than Pandas).
    - simonw 4y ago
      Don't Polars and Pandas both require your entire data to fit in memory?
      - nerdponx 4y ago
        Pandas doesn't. Polars I think has some lazy-loading capability, but it's not the default mode of operation and I don't think it supports all features. If DuckDB doesn't, then that's a big advantage.
        jcheng 4y ago
        I think you mean that Pandas does require your entire data to fit in memory? https://pandas.pydata.org/docs/user_guide/scale.html https://pandas.pydata.org/docs/user_guide/scale.html
        4y ago
krimpenrik 4y ago
Recently tried the GUI tool for ducks, forgot what's it called, something like 'Tab' and was quite disappointed. I feel duckdb needs a good tool like sqliteviewer to really take off.
- 0cf8612b2e1e 4y ago
  DBeaver supports duckdb.
- antonycourtney 4y ago
  I think you're referring to Tad (https://www.tadviewer.com https://www.tadviewer.com), which I developed. Tad isn't "the GUI tool for DuckDb"; it's a desktop app that provides a pivot table based viewer for tabular data files (CSV, Parquet, and DuckDb/SQLite database files). It uses DuckDb as its engine, but pre-dates DuckDb and was developed independently. It's listed in the DuckDb docs along with several others tools that work with or use DuckDb. All that said, I'm sorry you found it disappointing, and would welcome any constructive feedback on what specifically you found lacking, either here or to tad-feedback@tadviewer.com.
  - krimpenrik 4y ago
    Then I think I used it wrong, happens often. Thank you for your work. I was/am new to duckdb and since it was listed in the docs I assumed it was something like SQL viewer
benjaminwootton 4y ago
DuckDB is a relational OLAP store. If you want to do transformations on relational data using SQL then I think nowadays you would look at the modern data stack and do it with DBT. If you have genuinely big and unstructured data then of course you need a cluster and would reach for Spark. If you have smallish data then maybe DuckDB has a role because working with SQL is nicer than Pandas. But a lot of time you actually need the complexity of Pandas to do the transformation you need. DuckDB is neat but I still can’t quite convince myself of a killer use case.
- 89vision 4y ago
  > working with SQL is nicer than Pandas Really? I prefer working with dataframe apis. You get a nice sql-like paradigm plus all the control structures of the runtime.
  - wenc 4y ago
    SQL is much nicer for anything non-trivial. Pandas methods get unwieldy for complex aggregations. Also Pandas methods are imperative so cannot be optimized. SQL is declarative so it can be optimized to the hilt and DuckDB is faster than Pandas in almost all cases, even on Pandas data frames themselves! (partly due to vectorization).
  - camgunz 4y ago
    Databases are just much, much faster than Pandas, and that's before you start factoring the extraction and loading of data. I treat Pandas as a last resort when I can't do something in SQL, generally this is something like integrating with external services or running recordlinkage.
    - RobinL 4y ago
      If you're curious, I've written a FOSS record linkage library that executes everything as SQL. It supports multiple SQL backends including DuckDB and Spark for scale, and runs faster than most competitors because it's able to leverage the speed of these backends: https://github.com/moj-analytical-services/splink https://github.com/moj-analytical-services/splink
greggyb 4y ago
32 cores and 128GB RAM are now desktop-class specs. Latest generation commodity servers can supply you with hundreds of cores and TBs of RAM. Note: "commodity" != "cheap", at least not necessarily. Ninja edit before anyone misconstrues this. I am not saying that the typical desktop has these specs. I am saying that the class of hardware that is most commonly run on desktops includes SKUs that can meet this spec. Desktop-class means the same motherboard socket and processor architecture.
capkutay 4y ago
reminds me of this blog on streaming data to Parquet files and running queries on data in the native format. https://pedram.substack.com/p/streaming-data-pipelines-with-striim https://pedram.substack.com/p/streaming-data-pipelines-with-...
jetter 4y ago
I use Clickhouse to store close to 1TB of API analytics data (which would be 10TB in MongoDB, Clickhouse has insane compression ) and it's a wonderful and stable SQL-first alternative to DuckDB - which is a very exciting piece of software, but is indeed too young to embed into boring production. The last time I checked DuckDB npm package, it used callbacks instead of awaits..
- antonycourtney 4y ago
  I can understand how the older callback API for node.js might form a negative impression, but it's really not indicative of the maturity of the core db engine at all. And remember: the vast majority of users use the Python API. Even better news is that, as of a couple of months ago, there is now this package (which I wrote at MotherDuck and we have open sourced) which provides typed promise wrappers for the Duckdb API: https://www.npmjs.com/package/duckdb-async https://www.npmjs.com/package/duckdb-async. This is an independent npm package for now, but was developed in close coordination with the DuckDb core team.

RobinL 4y ago

I'd love to hear any real world experiences of anyone who's tried to run jobs that would usually require a spark cluster on a single machine with loads of cores and memory. How big can you go, and how does speed compare to Spark? (I'm guessing significantly faster from my experience using Duckdb on smaller machines)

jakewins 4y ago
I used it as glue for a monthly job, last week, processing ~1B event records, juggling them a bit and pushing them as 2M parquet segment files to S3. Mixed experience, would definitely not put it in a system that isn’t ok crashing frequently. It segfaults on Alpine (argh, C++!), and force exits the whole NodeJS process when it gets unexpected HTTP responses from S3. In an archive run of 2M pushes it’ll crash the process 4-5 times. Overall still really, really like it, but learned to not trust it
- tracker1 4y ago
  Node + AWS for data ingress has been pretty painful in my experience (mostly dynamo feeds from large csv (whois database)). In the end, rewrote in C# (core 2) and it was able to complete more reliably. I'm guessing that go and rust would also be better. I like node, really like JS, but I just think that maybe the AWS libraries aren't that great in the space. It would run for 3-5 hours, then just blow up unexpectedly, even with plenty of memory overhead, and not really bandwidth limited, with appropriate retries and slowdown for dynamo rejections. If I never have to write ETL pipelines again, I won't be upset about it.
  - Master_Odin 4y ago
    There was a bug that was recently fixed in node 16.17+ that was causing hard node processes crashes when doing stuff with S3 and I think had to do with receiving multiple packets at once or something.
wenc 4y ago
I have a single machine EC2 instance with 32 cores and 240GB memory and about 200 GB of partitioned Parquet files. I use DuckDB and Python with complex SQL (window functions, inequality joins, quantile functions etc) to extract data from this data. Because it’s a single machine (no distributed cluster) DuckDB can heavily parallelize and vectorize. I don’t know if I can give you perf numbers but complex analytic queries over the entire dataset regularly finish in 1-2 mins (not scientific since I’m not telling what kinds of queries I’m running). I’ve used Spark SQL and DuckDB overall is just more ergonomic, less boilerplate and is much faster since it is so lightweight. Granted DuckDB can only process data on one machine (whereas Spark can scale up indefinitely by adding machines) but most data sets I work with fit on a single beefy machine. Distributed computing — most of the time, you ain’t gonna need it. It’s like StackOverflow: it serves 2B requests a month but only runs on a few on-prem servers. Most people think this is impossible but you can actually do a lot with very few machines if you’re smart about it. Same with data. Big data is overrated.
- RobinL 4y ago
  Thanks - very interesting
SnowflakeOnIce 4y ago
My experience with a modest machine and a ~100GB dataset was that DuckDB was significantly easier to use and much faster (20x) than Ray Data. Have not compared directly with Spark. There was no cluster to set up or administer using DuckDB.
adgjlsfhk1 4y ago
spark is just a tool to let you take a computation that would run in an hour on your laptop if coded properly and send it to a server with 1000 cores where it runs in 2 hours.

nerdponx 4y ago

As far as I can tell, DuckDB is an alternative to "data frame" libraries like Data.table, Polars, Pandas, etc. Is that the case? What makes DuckDB a better choice than, say, Polars?

corford 4y ago
This blog post offers a nice summary: https://motherduck.com/blog/six-reasons-duckdb-slaps/ https://motherduck.com/blog/six-reasons-duckdb-slaps/
- nerdponx 4y ago
  The blog post doesn't really make a comparison between DuckDB and data frame libraries. It mentions that the DuckDB Python bindings can interoperate with Pandas, but it doesn't really explain why you would use DuckDB instead of Pandas, or Polars (which is both faster and more portable than Pandas).
  - simonw 4y ago
    Don't Polars and Pandas both require your entire data to fit in memory?
    - nerdponx 4y ago
      Pandas doesn't. Polars I think has some lazy-loading capability, but it's not the default mode of operation and I don't think it supports all features. If DuckDB doesn't, then that's a big advantage.
      - jcheng 4y ago
        I think you mean that Pandas does require your entire data to fit in memory? https://pandas.pydata.org/docs/user_guide/scale.html https://pandas.pydata.org/docs/user_guide/scale.html
        4y ago

krimpenrik 4y ago

Recently tried the GUI tool for ducks, forgot what's it called, something like 'Tab' and was quite disappointed. I feel duckdb needs a good tool like sqliteviewer to really take off.

0cf8612b2e1e 4y ago
DBeaver supports duckdb.
antonycourtney 4y ago
I think you're referring to Tad (https://www.tadviewer.com https://www.tadviewer.com), which I developed. Tad isn't "the GUI tool for DuckDb"; it's a desktop app that provides a pivot table based viewer for tabular data files (CSV, Parquet, and DuckDb/SQLite database files). It uses DuckDb as its engine, but pre-dates DuckDb and was developed independently. It's listed in the DuckDb docs along with several others tools that work with or use DuckDb. All that said, I'm sorry you found it disappointing, and would welcome any constructive feedback on what specifically you found lacking, either here or to tad-feedback@tadviewer.com.
- krimpenrik 4y ago
  Then I think I used it wrong, happens often. Thank you for your work. I was/am new to duckdb and since it was listed in the docs I assumed it was something like SQL viewer

benjaminwootton 4y ago

DuckDB is a relational OLAP store. If you want to do transformations on relational data using SQL then I think nowadays you would look at the modern data stack and do it with DBT. If you have genuinely big and unstructured data then of course you need a cluster and would reach for Spark. If you have smallish data then maybe DuckDB has a role because working with SQL is nicer than Pandas. But a lot of time you actually need the complexity of Pandas to do the transformation you need. DuckDB is neat but I still can’t quite convince myself of a killer use case.

89vision 4y ago
> working with SQL is nicer than Pandas Really? I prefer working with dataframe apis. You get a nice sql-like paradigm plus all the control structures of the runtime.
- wenc 4y ago
  SQL is much nicer for anything non-trivial. Pandas methods get unwieldy for complex aggregations. Also Pandas methods are imperative so cannot be optimized. SQL is declarative so it can be optimized to the hilt and DuckDB is faster than Pandas in almost all cases, even on Pandas data frames themselves! (partly due to vectorization).
- camgunz 4y ago
  Databases are just much, much faster than Pandas, and that's before you start factoring the extraction and loading of data. I treat Pandas as a last resort when I can't do something in SQL, generally this is something like integrating with external services or running recordlinkage.
  - RobinL 4y ago
    If you're curious, I've written a FOSS record linkage library that executes everything as SQL. It supports multiple SQL backends including DuckDB and Spark for scale, and runs faster than most competitors because it's able to leverage the speed of these backends: https://github.com/moj-analytical-services/splink https://github.com/moj-analytical-services/splink

greggyb 4y ago

32 cores and 128GB RAM are now desktop-class specs. Latest generation commodity servers can supply you with hundreds of cores and TBs of RAM. Note: "commodity" != "cheap", at least not necessarily. Ninja edit before anyone misconstrues this. I am not saying that the typical desktop has these specs. I am saying that the class of hardware that is most commonly run on desktops includes SKUs that can meet this spec. Desktop-class means the same motherboard socket and processor architecture.

capkutay 4y ago

reminds me of this blog on streaming data to Parquet files and running queries on data in the native format. https://pedram.substack.com/p/streaming-data-pipelines-with-striim https://pedram.substack.com/p/streaming-data-pipelines-with-...

jetter 4y ago

I use Clickhouse to store close to 1TB of API analytics data (which would be 10TB in MongoDB, Clickhouse has insane compression ) and it's a wonderful and stable SQL-first alternative to DuckDB - which is a very exciting piece of software, but is indeed too young to embed into boring production. The last time I checked DuckDB npm package, it used callbacks instead of awaits..

antonycourtney 4y ago
I can understand how the older callback API for node.js might form a negative impression, but it's really not indicative of the maturity of the core db engine at all. And remember: the vast majority of users use the Python API. Even better news is that, as of a couple of months ago, there is now this package (which I wrote at MotherDuck and we have open sourced) which provides typed promise wrappers for the Duckdb API: https://www.npmjs.com/package/duckdb-async https://www.npmjs.com/package/duckdb-async. This is an independent npm package for now, but was developed in close coordination with the DuckDb core team.