We've found DuckDB 2.0 to be way faster on high core count machines on EC2. Not just reading data from S3, but the actual computations too. Much more likely to see 100% utilisation up to about 128vCPUs or 192vCPUs, and that CPU usage to translate into workloads completing faster.
Still don't usually see a speedup past about 192vCPU, but prior to 2.0, speed would top out at more like 64vCPU, so this is a big win.
That has enabled us to scale Splink (FOSS record linakge software) to process 1bn records with 10bn record comparisons in 10 mins, which as far as I'm aware makes it the fastest linkage engine of this kind. Most of this performance comes from simply delegating the big calculations to the DuckDB engine! https://moj-analytical-services.github.io/splink/topic_guide...
I’m a huge fan of splink - and how you’ve been able to adopt duckdb.
Do you find that with the performance of duckdb, you see large deployments of spark based workflows less often? It seems hard to justify the complexity of spark if you can use duckdb , but I’m wondering if you see end users still reach for spark for any reasons?
There are a variety of people still use Spark, but this is usually due to constraints on their platform/infrastructure, not because it performs better. For instance people on Databricks can sometimes find it hard to use DuckDB on large machines, or they're just more familiar with Spark.
For a new user who has flexibility over where to run their workloads, we'd always recommend DuckDB. This is particularly the case now that:
- Splink 5 (released in September) allows you to run your workload in n chunks, meaning you can distribute it across multiple machines even on DuckDB
- DuckDB 2.0 parallelises better on large machines, so throwing a single massive machine at problems is now more viable
In general, after using Spark for a long time, I think for most workloads, it's just not really needed any more. Too complex, too difficult to tune. Perhaps if you have genuinely massive data. I've written a bit more about why I advise against Spark in most instances here: https://www.robinlinacre.com/recommend_duckdb/
The fact that can now be used as a client-server architecture is a game changer. It will for sure replace many data stacks that now run on many servers with tons of cpu and ram
Being able to put it behind an HTTP service / load balancer and implement horizontal scaling based on resource utilization, or even scale to zero via Cloud Run etc. Drool!
do you have not-big big data* that individuals or sub-teams/groups use? Lots of companies have centralized data <things> (bases, warehouses, lakes, swamps, etc) and runtime security is a challenging issue (i.e row-based security). If your audience measures their data in GB or MB, DuckDB can be an excellent way to punt on the security challenges and just provide them with ALL their data - maybe even locally - while they still get first-class DB tooling and performance (or even better than classic client-server). Example - old way: build a bunch of dimensions and facts for marketing, manage their access and permissions, produce reports, make sure you don't leak data they shouldn't see. new way: write (explore using AI) some extract/transforms from your SoT & create parquet files, use duckdb to serve that up to an individual or group's reporting solution or adhoc usage. IME that one-time investment is not that big and replaces a lifetime of scaling & access issues, or worrying about someone writing a query that doesn't join with the user table correctly!
* IME most companies don't actually have BIG data, but are talking about (at most) a few TB, which DuckDB is fine with.
I recently used it for a Rust profiler which writes Parquet files, so DuckDB then gives an easy SQL interface for things like summarization, analysis, and joins across runs.
It's very fast, especially on a decent sized cloud VM and when querying Iceberg. I've been running the alpha in production as a centralized analytics server for a small organization.
I built a DuckDB extension so you can connect via postgres protocol, or Arrow flight. DuckDB also has its own Quack protocol, but client support for Quack is very limited right now, whereas many data tools support Postgres protocol or ADBC.
Absolutely fantastic piece of software. Even beyond being used as engine for analysis, it's amazingly versatile for ingesting and reshaping data. Great job.
The fundamentals are fundamental for a reason. Don't go crazy with them, but triggers are great. (especially if you version control all your data definition language code)
> Other formats, such as JSON files and DuckDB's native database format, use synchronous I/O.
For the future:
> Most of its benefit comes from accessing remote data, but cold local reads can also benefit, albeit less. Next, we plan to add async reads for JSON and DuckDB-native files, as these are the two other formats most relevant to DuckDB core. Formats that live in out-of-tree extensions are not on the roadmap yet.
highest impact is when there is network overhead, like accessing them over S3/HTTP/S, and you're accessing many files as part of a query, like using a glob `-*.parquet` pattern. I imagine the same applies when you're doing local I/O, but not as pronounced of an improvement due to lower latency.
We've found DuckDB 2.0 to be way faster on high core count machines on EC2. Not just reading data from S3, but the actual computations too. Much more likely to see 100% utilisation up to about 128vCPUs or 192vCPUs, and that CPU usage to translate into workloads completing faster.
Still don't usually see a speedup past about 192vCPU, but prior to 2.0, speed would top out at more like 64vCPU, so this is a big win.
That has enabled us to scale Splink (FOSS record linakge software) to process 1bn records with 10bn record comparisons in 10 mins, which as far as I'm aware makes it the fastest linkage engine of this kind. Most of this performance comes from simply delegating the big calculations to the DuckDB engine! https://moj-analytical-services.github.io/splink/topic_guide...
Thanks to the team for all their work!
I’m a huge fan of splink - and how you’ve been able to adopt duckdb.
Do you find that with the performance of duckdb, you see large deployments of spark based workflows less often? It seems hard to justify the complexity of spark if you can use duckdb , but I’m wondering if you see end users still reach for spark for any reasons?
Appreciate the kind words.
There are a variety of people still use Spark, but this is usually due to constraints on their platform/infrastructure, not because it performs better. For instance people on Databricks can sometimes find it hard to use DuckDB on large machines, or they're just more familiar with Spark.
For a new user who has flexibility over where to run their workloads, we'd always recommend DuckDB. This is particularly the case now that: - Splink 5 (released in September) allows you to run your workload in n chunks, meaning you can distribute it across multiple machines even on DuckDB - DuckDB 2.0 parallelises better on large machines, so throwing a single massive machine at problems is now more viable
In general, after using Spark for a long time, I think for most workloads, it's just not really needed any more. Too complex, too difficult to tune. Perhaps if you have genuinely massive data. I've written a bit more about why I advise against Spark in most instances here: https://www.robinlinacre.com/recommend_duckdb/
Is it possible to save DuckDB UI notebooks to files?
The fact that can now be used as a client-server architecture is a game changer. It will for sure replace many data stacks that now run on many servers with tons of cpu and ram
Yes! And the server isn't really a server, it's still "in process" so it stays lightweight and simple. This is absolutely fantastic.
Being able to put it behind an HTTP service / load balancer and implement horizontal scaling based on resource utilization, or even scale to zero via Cloud Run etc. Drool!
I really want to use DuckDB. But I have struggled to find a use case in my company.
What are you guys using it for?
Use it for viewing csv and json files. Also, converting between formats can be a one liner.
do you have not-big big data* that individuals or sub-teams/groups use? Lots of companies have centralized data <things> (bases, warehouses, lakes, swamps, etc) and runtime security is a challenging issue (i.e row-based security). If your audience measures their data in GB or MB, DuckDB can be an excellent way to punt on the security challenges and just provide them with ALL their data - maybe even locally - while they still get first-class DB tooling and performance (or even better than classic client-server). Example - old way: build a bunch of dimensions and facts for marketing, manage their access and permissions, produce reports, make sure you don't leak data they shouldn't see. new way: write (explore using AI) some extract/transforms from your SoT & create parquet files, use duckdb to serve that up to an individual or group's reporting solution or adhoc usage. IME that one-time investment is not that big and replaces a lifetime of scaling & access issues, or worrying about someone writing a query that doesn't join with the user table correctly!
* IME most companies don't actually have BIG data, but are talking about (at most) a few TB, which DuckDB is fine with.
I recently used it for a Rust profiler which writes Parquet files, so DuckDB then gives an easy SQL interface for things like summarization, analysis, and joins across runs.
Write a log processing/aggregation tool!
It's very fast, especially on a decent sized cloud VM and when querying Iceberg. I've been running the alpha in production as a centralized analytics server for a small organization.
I built a DuckDB extension so you can connect via postgres protocol, or Arrow flight. DuckDB also has its own Quack protocol, but client support for Quack is very limited right now, whereas many data tools support Postgres protocol or ADBC.
https://github.com/sidequery/duckflight-extension
Absolutely fantastic piece of software. Even beyond being used as engine for analysis, it's amazingly versatile for ingesting and reshaping data. Great job.
Very excited for Triggers. Now DuckDB really is close to being a data warehouse in a box.
Back to the fundamentals! Triggers it is, indeed.
The fundamentals are fundamental for a reason. Don't go crazy with them, but triggers are great. (especially if you version control all your data definition language code)
>DuckDB 2.0 introduces asynchronous I/O for reading Parquet and CSV files.
Sorry, does this mean that their own duckdb format already supports async or doesn't?
For now, the latter:
> Other formats, such as JSON files and DuckDB's native database format, use synchronous I/O.
For the future:
> Most of its benefit comes from accessing remote data, but cold local reads can also benefit, albeit less. Next, we plan to add async reads for JSON and DuckDB-native files, as these are the two other formats most relevant to DuckDB core. Formats that live in out-of-tree extensions are not on the roadmap yet.
What is the benefit of using async io for csv and parquet files?
highest impact is when there is network overhead, like accessing them over S3/HTTP/S, and you're accessing many files as part of a query, like using a glob `-*.parquet` pattern. I imagine the same applies when you're doing local I/O, but not as pronounced of an improvement due to lower latency.