Pangram verdict · v3.3
We believe that this text is a mix of AI and human-written content.
AI likelihood · overall
AIArticle text · 1,010 words · 5 segments analyzed
DuckDB 2.0 is coming this fall and the alpha is out! I ran the interesting features on my own laptop, and against S3, to see what actually changes for people who build tables and pipelines rather than database engines. Because yes, DuckDB 2.0 is faster.
But to get the speed bump you need to understand how your data is shaped, and sometimes how to model it. This post covers the three features I think matter most, with the numbers I got, plus a few hidden gems I found in the commit logs. Every number below is from one machine (an M5 laptop) and my home internet, which slows both versions about equally.
Run your own before quoting them ;) Let's start with the easiest and most exciting one: async I/O. Async I/O: querying over AWS S3 is much fasterThis is the one I like most, because nothing in your query changes. Here is a query that reads a 2.2 GB Parquet file on S3 (Stack Overflow votes, 228 million rows, 2268 row groups) and counts votes per type. It reads one column out of four, about 230 MB.Copy codeCREATE SECRET s3 (TYPE s3, PROVIDER credential_chain, REGION 'us-east-1'); SET enable_external_file_cache = false; -- so every run really hits S3 SELECT VoteTypeId, count(*) AS n FROM read_parquet('s3://us-prd-motherduck-open-datasets/stackoverflow/parquet/2023-05/votes.parquet') GROUP BY ALL ORDER BY 1; Same query, same laptopDuckDB 1.5.518.8 sDuckDB 2.0 alpha7.7 sQuick caveat: as I said in the introduction, this is my home internet to us-east-1, so both numbers are slow. Expect things to go faster if you run this from cloud compute.So what's the black magic here?
The file is cut into 2268 row groups of about 122,000 rows. For every row group DuckDB downloads the bytes, decodes the Parquet, counts votes per type, and merges the partial counts at the end. Two kinds of work: waiting on the network, and crunching on the CPU.In 1.5.5 each of the 18 workers does both jobs in turn: download, wait, decode, download, wait. While a worker waits its CPU is idle, while it decodes it has no download in flight, and you never get more than 18 downloads going.In 2.0 a separate pool of threads only downloads, keeping dozens of row groups in flight and parking the bytes in a buffer. The workers only decode, and there is always a row group ready for them. Network and CPU are busy at the same time. Async I/O SELECT VoteTypeId, count(*) AS n FROM read_parquet('s3://…/votes.parquet') GROUP BY ALL ORDER BY 1; DuckDB 1.5.5 download, wait, decode, repeat votes.parquet · 2268 row groups · one column 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 worker 1 idle wait decode wait decode wait decode wait decode wait decode wait decode wait decode wait decode wait decode wait decode wait decode wait decode wait decode done worker 2 idle wait decode wait decode wait decode wait decode wait decode wait decode wait decode wait decode wait decode wait decode wait decode wait decode wait done worker 3 idle wait decode wait decode wait decode wait decode wait decode wait decode wait decode wait decode wait decode wait decode wait decode wait decode wait done worker 4 idle wait decode wait decode wait decode wait decode wait decode wait decode wait decode wait decode wait decode wait decode wait decode wait decode done elapsed 0.0 s 0.2 s 0.4 s 0.6 s 0.8 s 1.0 s 1.2 s 1.4 s 1.6 s 1.8 s 2.0 s 2.2 s 2.4 s 2.6 s 2.8 s 3.0 s 3.2 s 3.4 s 3.6 s 3.8 s 4.0 s 4.2 s 4.4 s 4.6 s 4.8 s 5.0 s 5.2 s 5.4 s 5.6 s 5.8 s 6.0 s 6.2 s 6.4 s 6.6 s 6.8 s 7.0 s 7.2 s 7.4 s 7.6 s 7.8 s 8.0 s 8.2 s 8.4 s 8.6 s 8.8 s 9.0 s 9.2 s 9.4 s 9.6 s 9.8 s 10.0 s 10.2 s 10.4 s 10.6 s 10.8 s 11.0 s 11.2 s 11.4 s 11.6 s 11.8 s 12.0 s 12.2 s 12.4 s 12.6 s 12.8 s 13.0 s 13.2 s 13.4 s 13.6 s 13.8 s 14.0 s 14.2 s 14.4 s 14.6 s 14.8 s 15.0 s 15.2 s 15.4 s 15.6 s 15.8 s 16.0 s 16.2 s 16.4 s 16.6 s 16.8 s 17.0 s 17.2 s 17.4 s 17.6 s 17.8 s 18.0 s 18.2 s 18.4 s 18.6 s 18.8 s DuckDB 2.0 alpha a pool downloads ahead, workers only decode votes.parquet · 2268 row groups · one column 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 dl pool idle fetch done worker 1 idle decode done worker 2 idle decode done worker 3 idle decode done elapsed 0.0 s 0.2 s 0.4 s 0.6 s 0.8 s 1.0 s 1.2 s 1.4 s 1.6 s 1.8 s 2.0 s 2.2 s 2.4 s 2.6 s 2.8 s 3.0 s 3.2 s 3.4 s 3.6 s 3.8 s 4.0 s 4.2 s 4.4 s 4.6 s 4.8 s 5.0 s 5.2 s 5.4 s 5.6 s 5.8 s 6.0 s 6.2 s 6.4 s 6.6 s 6.8 s 7.0 s 7.2 s 7.4 s 7.6 s 7.7 s One setting drives this: read_ahead_depth, how many row groups the download pool may fetch ahead of the workers. It defaults to -1 (automatic, sized from your thread count), so async I/O is on out of the box.
Set it to 0 and you get the 1.5 behaviour back.S3 read1.5.52.0 alphaOne 2.2 GB Parquet file, one column18.8 s7.7 s23 large Parquet files, 13.6 GB, one column11.8 s3.9 sOne 1.7 GB plain CSV116 s55 s30 tiny Parquet files, about 1 MB each3.7 s3.3 sOne comment on the tiny files: no meaningful change, because the time there is per-file round trips (footer, then data) that reading ahead cannot remove.