Rendered at 10:25:47 GMT+0000 (Coordinated Universal Time) with Cloudflare Workers.
RobinL 20 hours ago [-]
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!
gopalv 17 hours ago [-]
> Not just reading data from S3, but the actual computations too.
Async io generally causes a CPU bump as a side-effect, when the bottleneck moves from the disk to the CPU (more usually memory bandwidth).
My experience is from a different SQL engine, but the improvement in scan rates directly translates all other operators getting more work to do per unit time.
More CPUs pegged at 100% is always a good sign of smooth flows, but optimizing further requires data layout changes from that point. Even if it is a sub-linear speedup out of concurrency scaling - more with more is still a win, assuming you can pay for more (the Dy-skew paper has an example I can quote).
RobinL 17 hours ago [-]
Yep that makes sense.
One thing we've seen with DuckDB, which they also mention in their docs, is that it's possible to 'over parallelize'. Beyond some number of cores, the overall workflow measured in wall time actually slows down when you add more CPUs, because of the overhead of parallelizing, despite them running at 100%.
We observe this even if we pull the data from S3 onto the local SSD before processing,
gh02t 16 hours ago [-]
This is also a problem in classical HPC, there aren't very many algorithms that can scale linearly with cores. That's partially why so many HPC algorithms tend to be built on matrix multiplication and Monte Carlo, because those do tend to scale near arbitrarily.
gopalv 12 hours ago [-]
> because of the overhead of parallelizing, despite them running at 100%
Duckdb might not be the reason, the real reason might be AWS.
It's been two years since I worked on this, but AWS Nitro might be triggering a high CPU Steal % when all the cores are busy at the same time.
You can do taskset -c 1-191 <server-process> and get a tiny bit of breathing room there.
I almost always do this above 32 cores, since this lets me always have a nice responsive ssh connection when the rest of the CPUs are churning high.
The more cores you have, the less of a problem sparing 1 core becomes.
RobinL 4 hours ago [-]
Will try this thanks!
dicey 19 hours ago [-]
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?
RobinL 19 hours ago [-]
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/
19 hours ago [-]
nicoritschel 19 hours ago [-]
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 wire 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 wire protocol or ADBC (which you can use with Arrow Flight).
Very nice. Did you add support for Quack v3 already? (released alongside DuckDB 2.0)? Unfortunately it's not compatible with v1 so you'll need to adjust it.
We used to use Metabase but the cloud / self hosted divide was too much fiddling, along with the expression language.
paoliniluis 20 hours ago [-]
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
jtbaker 19 hours ago [-]
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!
bambax 19 hours ago [-]
Yes! And the server isn't really a server, it's still "in process" so it stays lightweight and simple. This is absolutely fantastic.
xnx 20 hours ago [-]
Absolutely fantastic piece of software. Even beyond being used as engine for analysis, it's amazingly versatile for ingesting and reshaping data. Great job.
datadrivenangel 19 hours ago [-]
Very excited for Triggers. Now DuckDB really is close to being a data warehouse in a box.
zazuke 19 hours ago [-]
Back to the fundamentals! Triggers it is, indeed.
datadrivenangel 18 hours ago [-]
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)
wodenokoto 18 hours ago [-]
Is it possible to save DuckDB UI notebooks to files?
I just used DuckDB 2.0 last week to compress 7 terabytes of Azure logs (NDJSON) to 2.5% of the original size in Parquet format in under two hours with a medium sized VM!
Those logs were then small enough to run ad-hoc queries in tens of seconds directly from the archive blob storage.
I created a “frozen DuckLake” and sent the signed blob URL to the security analyst.
No need for a server, no need for Splunk!
It’s easy to use, fast, can be plopped anywhere on a network, and it’s free!
If it just had KQL syntax support, it would be perfect…
nicoritschel 12 hours ago [-]
> If it just had KQL syntax support, it would be perfect…
Good idea for an extension, especially with the new PEG parser.
pampas 8 hours ago [-]
+1 on kusto query langauge. It's much nicer than SQL.
mikkelam 18 hours ago [-]
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?
gopalv 17 hours ago [-]
> What are you guys using it for?
Transparent queries on server/browser in identical sql.
If you play all the tricks Google's Capacitor does to the data layout, you can shove ~2-3 million rows to a browser based query engine in 16-20Mb with duckdb-wasm. We can scale it up to ~1Gb with HTTP range queries, with cache-control.
Then we use Jev to write SQL queries over it in 1-2s, whenever someone has a followup question. Getting Jev to write SQL takes a bit of prep, but my co-founder wrote a paper about clairvoyant query execution a few years ago [1].
I could have 3000 users logged into the same application, but all of them would be bringing their own CPU to run duckdb. My compute scaling problems for my data cube is a solved problem with this approach.
This week I've been debugging a performance issue in a Ducklake and knocking out some good improvements, but this precise symptom—read_parquet() making tons of tiny range requests to load a parquet's footer from S3—was still baffling me. How lucky am I that I stumbled on your comment! Amazing, thank you for the pointer.
gopalv 14 hours ago [-]
> read_parquet() making tons of tiny range requests to load a parquet's footer from S3
This is somewhat unavoidable if you have a number of files and a tight filter which rejects most of them. The footer choice was a bad one in hindsight (not for HDFS), but for anything which uses XOR or other kind of erasure coding. The other other file format I worked with used headers only instead of footers, to save a few ms in latency for the initial GET
I don't think Ducklake can maintain a zone-map or ART index over parquet files, but there is no equivalent of iceberg metadata/something which has min/max/HLL compacted across multiple files yet.
The secondary indexes with file identifiers stored in a sequential file is most likely the next useful thing duckdb can make.
oa335 9 hours ago [-]
> If you play all the tricks Google's Capacitor does to the data layout
can you please elaborate? does duckdb support querying custom storage formats?
gopalv 8 hours ago [-]
> does duckdb support querying custom storage formats?
No, this is still parquet being loaded in.
The "trick" is running through sort order combinations which result in maximum packing in a Dremel style data format (like Parquet).
Here's the trick explained in enough detail to understand, but not enough to copy as-is
Amazing we did the exact same thing at work. Been working great. A lot of BI datasets can fit within sub 20MiB of ZSTD compressed parquet.
AlfeG 54 minutes ago [-]
Embedded into .Net monolith app to replicate MSSQL data into it and do queries around this data, because MSSQL is SLOW on those queries =)
CodeCompost 2 hours ago [-]
I've used it for data migration combined with Jupyter Notebook.
While I'm fully aware of DataFrame, I am more familiar with SQL. So I used Python scripts in Jupyter to load Excel sheets into DuckDB, and then run queries in DuckDB. Eventually I export the data back to Excel or SQL Server.
Not the most efficient way to do this I know, but it works for me.
skeeter2020 18 hours ago [-]
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.
thewisenerd 2 hours ago [-]
we get these hundreds of thousands of lines cost exports on a daily basis from our cloud provider
we process it 'daily' convert it from csv to parquet with a little post-processing for attribution, and have a small duckdb-powered dashboard/wrapper for reporting and dynamic queries
chatmasta 8 hours ago [-]
I’ve got a number of Claude scheduled workflows that pull data from our warehouse to make a dashboard. I have it add an embedded DuckDB to the artifact and load all data into there. Makes it easy to diff and extend the dashboard.
tcdent 18 hours ago [-]
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.
jdnier 18 hours ago [-]
Use it for viewing csv and json files. Also, converting between formats can be a one liner.
paoliniluis 15 hours ago [-]
You can use it to connect every single data source that's around your company and then use it as a federation engine (e.g. spreadsheets!)
jtbaker 18 hours ago [-]
Write a log processing/aggregation tool!
hugoberry 1 hours ago [-]
[flagged]
pletnes 19 hours ago [-]
What is the benefit of using async io for csv and parquet files?
skeeter2020 18 hours ago [-]
Helps use all of the available bandwidth by not blocking (otherwise idle) worker threads waiting for data to arrive. Requests and processing are managed independently, so you essentially get better utilization & improved performance.
jtbaker 18 hours ago [-]
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.
skeeter2020 18 hours ago [-]
yes, makes sense because bandwidth is less likely to be the bottleneck
fragebogen 19 hours ago [-]
>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?
pdet 16 hours ago [-]
The DuckDB format supports Async I/O.
Disclaimer: I'm the DuckDB developer who implemented Async I/O for parquet, csvs and the DuckDB Format.
adrian_b 49 minutes ago [-]
Then you should update the DuckDB documentation, which currently states that for the DuckDB format AIO shall be implemented in the future, not in the current release.
The release announcement links to several pages from a few months ago, like:
Today I see in the release announcement "DuckDB 2.0 introduces asynchronous I/O for reading Parquet and CSV files, as well as DuckDB's native storage format. "
So it seems that the announcement has been updated, because I am pretty sure that yesterday the ending of this sentence did not exist.
adrian_b 19 hours ago [-]
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.
skybrian 12 hours ago [-]
I wonder how terrible would it be to use DuckDB as the main database (running transactions) for a small project?
You might be able to make transactions work but it's really the opposite of what duckdb is designed for. Do it only for fun.
netcraft 12 hours ago [-]
As long as its a single writer, you can get pretty far
dbettin 11 hours ago [-]
"if you have worked with analytical systems in the past, you may assume that DuckDB cannot handle transactional workloads. But DuckDB has been built as a transactional, multi-connection database with full MVCC and transaction isolation since day one. Most users just never needed that in a single-user scenario. It turns out DuckDB handles transactions well: it's fast enough to compete with general-purpose databases like PostgreSQL on quite a few workloads, and the client/server pattern finally lets that machinery shine in multi-tenant, long-running deployments."
Does anyone have any links to benchmarks or stories of attempting this in practice? (Even what their internal benchmark that lead them to say "it's fast enough to compete with general-purpose databases like PostgreSQL on quite a few workloads" was?)
The DuckDB devs measured it ~30x faster than PostgreSQL (and ~3x faster than Arrow Flight) for bulk transfer, which pg is known to be very slow at, and a bit faster (<2x) than pg for small writes up to 8 threads, after which they say that pg continues to scale well with number of threads but DuckDB plateaus.
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!
Async io generally causes a CPU bump as a side-effect, when the bottleneck moves from the disk to the CPU (more usually memory bandwidth).
My experience is from a different SQL engine, but the improvement in scan rates directly translates all other operators getting more work to do per unit time.
More CPUs pegged at 100% is always a good sign of smooth flows, but optimizing further requires data layout changes from that point. Even if it is a sub-linear speedup out of concurrency scaling - more with more is still a win, assuming you can pay for more (the Dy-skew paper has an example I can quote).
One thing we've seen with DuckDB, which they also mention in their docs, is that it's possible to 'over parallelize'. Beyond some number of cores, the overall workflow measured in wall time actually slows down when you add more CPUs, because of the overhead of parallelizing, despite them running at 100%.
We observe this even if we pull the data from S3 onto the local SSD before processing,
Duckdb might not be the reason, the real reason might be AWS.
It's been two years since I worked on this, but AWS Nitro might be triggering a high CPU Steal % when all the cores are busy at the same time.
You can do taskset -c 1-191 <server-process> and get a tiny bit of breathing room there.
I almost always do this above 32 cores, since this lets me always have a nice responsive ssh connection when the rest of the CPUs are churning high.
The more cores you have, the less of a problem sparing 1 core becomes.
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/
I built a DuckDB extension so you can connect via Postgres wire 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 wire protocol or ADBC (which you can use with Arrow Flight).
https://github.com/sidequery/duckflight-extension
We used to use Metabase but the cloud / self hosted divide was too much fiddling, along with the expression language.
I just used DuckDB 2.0 last week to compress 7 terabytes of Azure logs (NDJSON) to 2.5% of the original size in Parquet format in under two hours with a medium sized VM!
Those logs were then small enough to run ad-hoc queries in tens of seconds directly from the archive blob storage.
I created a “frozen DuckLake” and sent the signed blob URL to the security analyst.
No need for a server, no need for Splunk!
It’s easy to use, fast, can be plopped anywhere on a network, and it’s free!
If it just had KQL syntax support, it would be perfect…
Good idea for an extension, especially with the new PEG parser.
What are you guys using it for?
Transparent queries on server/browser in identical sql.
If you play all the tricks Google's Capacitor does to the data layout, you can shove ~2-3 million rows to a browser based query engine in 16-20Mb with duckdb-wasm. We can scale it up to ~1Gb with HTTP range queries, with cache-control.
Then we use Jev to write SQL queries over it in 1-2s, whenever someone has a followup question. Getting Jev to write SQL takes a bit of prep, but my co-founder wrote a paper about clairvoyant query execution a few years ago [1].
I could have 3000 users logged into the same application, but all of them would be bringing their own CPU to run duckdb. My compute scaling problems for my data cube is a solved problem with this approach.
[1] - https://dl.acm.org/doi/10.14778/2733004.2733064
This is somewhat unavoidable if you have a number of files and a tight filter which rejects most of them. The footer choice was a bad one in hindsight (not for HDFS), but for anything which uses XOR or other kind of erasure coding. The other other file format I worked with used headers only instead of footers, to save a few ms in latency for the initial GET
I don't think Ducklake can maintain a zone-map or ART index over parquet files, but there is no equivalent of iceberg metadata/something which has min/max/HLL compacted across multiple files yet.
The secondary indexes with file identifiers stored in a sequential file is most likely the next useful thing duckdb can make.
can you please elaborate? does duckdb support querying custom storage formats?
No, this is still parquet being loaded in.
The "trick" is running through sort order combinations which result in maximum packing in a Dremel style data format (like Parquet).
Here's the trick explained in enough detail to understand, but not enough to copy as-is
https://cloud.google.com/blog/products/bigquery/inside-capac...
Search for the section with
"Capacitor builds an approximation model that takes into account all relevant factors and comes up with a reasonable solution."
or a more detailed dive in the paper (by the favourite author of performance engineers, Daniel Lemire)
https://arxiv.org/abs/1207.2189
While I'm fully aware of DataFrame, I am more familiar with SQL. So I used Python scripts in Jupyter to load Excel sheets into DuckDB, and then run queries in DuckDB. Eventually I export the data back to Excel or SQL Server.
Not the most efficient way to do this I know, but it works for me.
* IME most companies don't actually have BIG data, but are talking about (at most) a few TB, which DuckDB is fine with.
we process it 'daily' convert it from csv to parquet with a little post-processing for attribution, and have a small duckdb-powered dashboard/wrapper for reporting and dynamic queries
Sorry, does this mean that their own duckdb format already supports async or doesn't?
Disclaimer: I'm the DuckDB developer who implemented Async I/O for parquet, csvs and the DuckDB Format.
The release announcement links to several pages from a few months ago, like:
https://duckdb.org/2026/07/31/asynchronous-io
which according to you are no longer true.
EDIT:
Today I see in the release announcement "DuckDB 2.0 introduces asynchronous I/O for reading Parquet and CSV files, as well as DuckDB's native storage format. "
So it seems that the announcement has been updated, because I am pretty sure that yesterday the ending of this sentence did not exist.
> 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.
https://github.com/shreeve/duckdb-harbor
https://duckdb.org/2026/08/17/duckdb-20-highlights
The DuckDB devs measured it ~30x faster than PostgreSQL (and ~3x faster than Arrow Flight) for bulk transfer, which pg is known to be very slow at, and a bit faster (<2x) than pg for small writes up to 8 threads, after which they say that pg continues to scale well with number of threads but DuckDB plateaus.