Glonce

DuckDB 2.0 alpha reads S3 Parquet 2x to 3x faster in laptop test

infrastructureThe Glonce editorsHacker News🔥69Glonce rating69Impact55Novelty60Relevance95Surprise35Players35Credibility75
DuckDB 2.0 alpha reads S3 Parquet 2x to 3x faster in laptop test

A MotherDuck blog post, whose author is not named, reports hands-on results from the DuckDB 2.0 alpha. The author says 2.0 is coming this fall and the alpha is already out. Every number comes from one machine, an M5 laptop, plus the author's home internet, which the author says slows both versions about equally. The advice is to run your own tests before quoting the figures.

The first feature is async I/O for Parquet over S3. The test file is a 2.2 GB Parquet file (Stack Overflow votes, 228 million rows, 2268 row groups of about 122,000 rows). The query counts votes per type and reads one column out of four, about 230 MB. In 1.5.5 each of the 18 workers downloads and then decodes in turn, so the CPU idles while it waits on the network and you never get more than 18 downloads in flight. In 2.0 a separate pool of threads only downloads, keeps dozens of row groups in flight and parks the bytes in a buffer, while the workers only decode. One setting drives it: read_ahead_depth, which defaults to -1 (automatic, sized from the thread count). Setting it to 0 restores the 1.5 behaviour. The author's result is that reading over S3 is 2x to 3x faster in 2.0 with zero query changes. For thousands of tiny Parquet files there is no meaningful change, because the time goes on per-file round trips that reading ahead cannot remove; the author calls that layout a bad practice that 2.0 does not rescue.

The second feature is a rewritten recursive CTE engine. The DuckDB team claims 40x on graph reachability. The author explains that 1.5 re-read the whole table on every round, so a deep hierarchy meant thousands of full reads. In 2.0 the table is read once, a lookup on the parent column is built once, and each round only looks up the rows it just found, so cost follows the rows touched rather than rounds times table size. The author tested this on a generated 20,000-commit git repo, walking from HEAD back to the root. The post announces the query times, but no figures appear in the text, so the 40x number remains the team's claim. The author's summary: deep parent/child chains such as git history, lineage, reply threads or a full bill of materials benefit; a shallow hierarchy like an org chart will not show much.

The third feature is VARIANT, now a first-class data type, with shredding. When DuckDB writes a row group, it pulls fields that appear in most rows with a consistent type into their own real columns; rare fields and fields whose type varies from row to row stay in a binary remainder. The author stored five million JSON events three ways (JSON string, VARIANT, a typed table) and ran three queries: a filter on two fields, a grouped sum of a numeric sub-field, and a list lookup. VARIANT came out 2.7 times smaller than the JSON string. On queries touching shredded fields it was about 6 times faster than parsing the JSON text and within 20 percent of the typed columns, and 78 times faster than VARIANT in 1.5.5, which had the type but not shredding. The exception is lists: casting a VARIANT list to VARCHAR[] costs two seconds in this alpha, slower than the JSON path. The advice: use VARIANT for events with consistent fields and value kinds, promote the fields every query touches to real columns, and avoid list casts in hot queries for now.

Smaller items round out the post. Triggers with transition tables can write before and after rows into a history table. Nested schemas work (CREATE SCHEMA finance.reports). DML can run inside a CTE, for example a DELETE ... RETURNING feeding an INSERT. SET dialect_compatibility_mode = 'spark' offers a Spark SQL compatibility mode. SET external_file_cache_spill = true spills evicted remote file blocks to the temp directory; with a 300 MB memory limit, the second read of an 854 MB Parquet file on S3 went from 23.9 s to 0.35 s. The CLI gained a SQL formatter and a queryable history, read_json now detects ISO-8601 timestamps with an offset as TIMESTAMPTZ, and CREATE SECRET ... IN CONNECTION scopes a secret to one connection. Quack, the client-server protocol, goes to 1.0 with this release. The alpha installs with one line: curl https://install.duckdb.org | DUCKDB_VERSION=alpha bash. According to the author, MotherDuck will support 2.0 close to the release.

Key facts

  • On a 2.2 GB Parquet file on S3 (228 million rows, 2268 row groups), the author measured reading as 2x to 3x faster in 2.0 than in 1.5.5, with no query changes; async I/O is on by default via read_ahead_depth = -1.
  • The rewritten recursive CTE engine reads the table once and builds a parent-column lookup once; the DuckDB team claims 40x on graph reachability, and the author tested a generated 20,000-commit git repo.
  • VARIANT with shredding was 2.7 times smaller than a JSON string on five million events, about 6 times faster than parsing JSON text on shredded-field queries, and within 20 percent of typed columns.
  • VARIANT list casts to VARCHAR[] cost two seconds in this alpha, slower than the JSON path, so lists are not yet where shredding pays off.
  • All figures come from a single M5 laptop and a home internet connection; 2.0 is expected this fall and the alpha is available now.

Why it matters

DuckDB is widely used to query Parquet files directly, often straight from S3, and this post shows where the 2.0 alpha changes the picture for people building tables and pipelines. The headline gain needs no query changes: a separate download pool keeps many row groups in flight while workers decode, so network and CPU are busy at the same time. The recursive CTE rewrite targets deep hierarchies that people used to push to a graph database, and VARIANT shredding stores consistent JSON fields as real columns. The author's own framing is that the speed-up depends on how your data is shaped, and sometimes on how you model it.

Who it affects

Data engineers and analysts who read Parquet over S3 with DuckDB benefit from async I/O automatically. Anyone walking deep parent/child chains (git history, lineage, reply threads, a full bill of materials) is the target of the recursive CTE work. Teams storing event logs or other JSON in DuckDB are affected by VARIANT, with structured logs named as the ideal case. People with lakes made of thousands of 1 MB Parquet files will not see a meaningful change, and shallow hierarchies such as an org chart gain little. Users who need to migrate pipelines from Spark SQL get a compatibility mode, and MotherDuck customers are told 2.0 support will arrive close to the release.

How to use it

Install the alpha with curl https://install.duckdb.org | DUCKDB_VERSION=alpha bash, then check with duckdb -c "SELECT version()"; other clients are on the DuckDB installation page. Async I/O needs nothing: read_ahead_depth defaults to -1, and setting it to 0 returns the 1.5 behaviour. For recursive CTEs, keep the hierarchy as one parent/child table with integer ids and use USING KEY when the recursion carries a value such as depth or cost. For JSON events, store them as VARIANT instead of a JSON string, keep value kinds consistent so fields shred, promote the fields every query touches to real columns, and avoid list casts in hot queries for now. SET external_file_cache_spill = true helps repeated reads of remote files under a tight memory limit. The post gives no pricing.

How solid is it

This is a single author's hands-on test of an alpha, on one M5 laptop and a home internet connection to us-east-1, and the author tells readers to run their own tests before quoting the numbers. The 2x to 3x S3 range is stated without absolute query times for 1.5.5 versus 2.0. The 40x recursive CTE figure is the DuckDB team's claim, quoted by the author; the recursive CTE timing results are announced in the post but no figures appear in the text. No results on other hardware, operating systems or cloud compute are given, and the author only expects things to go faster in the cloud. The VARIANT comparisons (2.7 times smaller, about 6 times faster, within 20 percent of typed columns, 78 times faster than 1.5.5) come from one five-million-event dataset and three queries. The post is published by MotherDuck, which also says it will support 2.0.

Risks and caveats

The software is an alpha, and the post does not say it is production-ready. VARIANT list handling is slow in this alpha: casting a list to VARCHAR[] costs two seconds, slower than the JSON path. Fields that change type between rows, such as a latency_ms that is 231 in one line and "231ms" in the next, fall into the slower binary remainder. Reading ahead does not help with thousands of tiny Parquet files, and shallow hierarchies see little from the recursive CTE rewrite. The exact release date is only given as this fall, and no benchmark numbers are given for triggers, nested schemas, DML in CTEs, the Spark compatibility mode or Quack.

“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 ;)”

— Author of the MotherDuck post