DuckDB 2.0 alpha speeds S3 reads up to 3x and rebuilds recursive CTEs

DuckDB 2.0 has entered alpha testing ahead of a planned release this fall, and benchmarking published by MotherDuck indicates the update brings real speed gains for people who build data tables and pipelines rather than database engines — especially when the data lives on object storage.

Async I/O speeds up S3 queries with no query changes

The biggest win, according to the MotherDuck post, is asynchronous I/O, which asks nothing of the user: the same query simply runs faster.

The test case counted votes grouped by type over a 2.2 GB Parquet file on AWS S3 — Stack Overflow's votes dataset, 228 million rows split into 2,268 row groups — reading one column out of four, roughly 230 MB. DuckDB 1.5.5 needed 18.8 seconds; the 2.0 alpha finished in 7.7.

The post explains the mechanics behind the gap. In 1.5.5 each of the 18 worker threads handled every stage itself: fetch bytes, wait on the network, decode the Parquet, repeat. While waiting, the CPU sat idle; while decoding, no download was in flight; and no more than 18 downloads ever ran concurrently. Version 2.0 splits the work in two: a dedicated pool of threads only downloads, holding dozens of row groups in flight in a buffer, while the workers concentrate on decoding and always have data waiting. Network and CPU stay busy at the same time.

A single setting, read_ahead_depth, controls how far ahead the download pool may fetch. It defaults to -1, meaning automatic and sized from the thread count, so the behaviour ships enabled. Setting it to 0 restores the old pattern.

Other measurements point the same way. Reading one column across 23 large Parquet files totalling 13.6 GB fell from 11.8 to 3.9 seconds, and a 1.7 GB plain CSV dropped from 116 to 55 seconds. Thirty tiny Parquet files of about 1 MB each barely improved, going from 3.7 to 3.3 seconds, because the cost there is per-file round trips — a footer read followed by a data read — that prefetching cannot remove. MotherDuck notes that a lake built from thousands of 1 MB files remains a bad layout, and 2.0 does not rescue it.

All figures came from one machine, an M5 laptop, pulling data over a home internet connection to us-east-1, which slowed both versions roughly equally. The author recommends running your own benchmarks and expects faster absolute times from cloud compute.

Recursive CTEs read the table once instead of every round

The DuckDB team also rebuilt the recursive CTE engine and, according to the post, claims a 40x improvement on graph reachability workloads.

A recursive CTE is effectively a loop over a table, one iteration per level of depth. Org charts, folder trees, bills of materials, reply threads, data lineage and git history all take this parent/child shape, and the depth varies enormously: an org chart might run eight levels, while a git history runs to tens of thousands.

That depth was the problem in 1.5. Every round went back and re-read the entire table to find the next level, so eight levels meant eight full passes, and tens of thousands of levels meant tens of thousands of passes over the same data. In 2.0 the table is read once, a lookup structure on the parent column is built once, and each round only probes for the rows discovered in the previous one. Cost now tracks the rows a query actually touches rather than the number of rounds multiplied by table size.

Why it matters

DuckDB has become a common engine for local analytics and for pipelines that query Parquet lakes directly on S3. A two-to-three-fold improvement on remote reads, delivered by default and with zero query rewrites, translates directly into shorter pipeline runs.

The recursive CTE rewrite moves a whole class of graph-shaped problems — lineage tracking, hierarchy expansion, commit-history analysis — from impractical to ordinary.

The alpha also shows where the limits sit: the gains depend on how your data is shaped and modelled, and no amount of prefetching rescues a lake of tiny files. With the final release due this fall, pipeline builders now have a window to test their own workloads against the alpha before it lands.