Repeating MotherDuck's DuckDB 2.0 benchmarks on my own laptop

MotherDuck published Why DuckDB 2.0 is faster. The numbers in it come from one laptop, and the article itself says: “Run your own before quoting them”. This page is that run. It has the SQL files, the commands and the raw timer output, so the same thing can be done on another machine in about ten minutes.

Result first

Short reading of the table: the S3 claim and the recursive claim hold on my machine. VARIANT is smaller and much faster for fields, and it is slower than plain JSON for the list lookup. MotherDuck’s own table has the same slowdown in its bottom row.

What I ran, and what is not the same as the article

• DuckDB v1.5.5 (Variegata) d8cdaa33fd and v2.0.0-alpha46057 (Cyanoptera) b15358c371, the official command line builds for macOS on Apple Silicon.
• An Apple M4 Max with macOS 26.6, on home internet. The S3 bucket is in us-east-1 and I am in Europe.
• Test 1 uses the article’s own public file and its query. No AWS account is needed.
• Tests 2 and 3 use data I generated. The article does not publish its generators, so the shape is the same and the data is not. Compare the direction, not the milliseconds.
• Every number in the table is a single run. The part called “How much the numbers move” below says how far other runs were from these.

Two binaries side by side

The command line client is one file, so two versions can live next to each other.

mkdir -p bin && cd bin
curl -sL https://install.duckdb.org/v1.5.5/duckdb_cli-osx-arm64.gz | gunzip > duckdb-1.5.5
curl -sL https://duckdb-staging.duckdb.org/b15358c371/v2.0.0-alpha46057/duckdb/duckdb/github_release/duckdb-cli-osx-arm64.tar.gz | tar xz
mv duckdb duckdb-2.0
chmod +x duckdb-1.5.5 duckdb-2.0
export PATH="$PWD:$PATH"

The second address is a staging build of an alpha. It will stop existing at some point, and a newer alpha may give other numbers.

Test 1: one column from a Parquet file on S3

The file is one of MotherDuck’s public Stack Overflow exports. Nothing is uploaded or copied for this test: both versions read the same object the article used.

B=s3://us-prd-motherduck-open-datasets/stackoverflow/parquet/2023-05/
aws s3 ls --no-sign-request --human-readable $B

2023-07-15 16:01:48    0 Bytes
2023-07-15 20:16:11  517.2 MiB badges.parquet
2023-07-15 20:16:11    6.9 GiB comments.parquet
2023-07-15 20:16:11  163.3 MiB post_links.parquet
2023-08-16 16:55:13    4.1 GiB posts.parquet
2023-07-15 20:16:11    1.5 MiB tags.parquet
2023-07-15 20:17:01  733.7 MiB users.parquet
2023-07-15 20:23:34    2.2 GiB votes.parquetoutput

A Parquet file keeps its layout in a footer at the end, and parquet_metadata reads only that footer. It answers what “a 2.2 GB file” means for this query before a single data page is fetched:

SET s3_region = 'us-east-1';
SELECT path_in_schema AS column_name,
       count(*) AS row_groups,
       sum(num_values) AS row_count,
       (sum(total_compressed_size) / 1e6)::INT AS mb
FROM parquet_metadata(
  's3://us-prd-motherduck-open-datasets/stackoverflow/parquet/2023-05/votes.parquet')
GROUP BY ALL ORDER BY mb DESC;inside.sql

┌──────────────┬────────────┬───────────┬───────┐
│ column_name  │ row_groups │ row_count │  mb   │
│   varchar    │   int64    │  int128   │ int32 │
├──────────────┼────────────┼───────────┼───────┤
│ PostId       │       2268 │ 228077281 │  1098 │
│ Id           │       2268 │ 228077281 │   913 │
│ VoteTypeId   │       2268 │ 228077281 │   227 │
│ CreationDate │       2268 │ 228077281 │    86 │
└──────────────┴────────────┴───────────┴───────┘output

228 million rows in 2,268 row groups. The test query touches VoteTypeId only, which is 227 MB of the file, so the benchmark is really “fetch 2,268 pieces that add up to 227 MB and decode them”. That number matters for the next part.

SET s3_region = 'us-east-1';
SET enable_external_file_cache = false;
.timer on
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;s3.sql

The cache line matters. Without it a second run can be served from local memory, and then the test no longer measures the network.

duckdb-1.5.5 < s3.sql | grep real
duckdb-2.0 < s3.sql | grep real

Run Time (s): real 19.209 user 4.015271 sys 1.522111
Run Time (s): real 5.960 user 3.852375 sys 0.570655output

3.2 times faster. The article has 18.8 and 7.7 seconds, a factor of 2.4.

The user column is the part I find more useful than the headline. Both versions spend about 4 seconds of CPU. The work is the same. What changed is the wall clock around it: 1.5.5 needs 19 seconds to get 4 seconds of work done, so for most of that time the threads are waiting for bytes.

That fits the explanation in the article. In 1.5 the thread that decodes a row group is also the one that downloads it, so it blocks on the network. 2.0 moves the downloads to a separate pool that fetches row groups ahead of the decoders.

Switching the feature off

The article names the setting, read_ahead_depth, and says that zero brings the old behaviour back. It gives no measurement for that, and it is the cheapest way to check that the speedup really comes from this one change and not from something else in a major release.

s3_off.sql is the same file with one more line at the top:

SET read_ahead_depth = 0;

duckdb-2.0 < s3_off.sql | grep real

Run Time (s): real 19.052 user 4.542453 sys 1.590115output

19.05 seconds on the 2.0 binary, against 19.21 on 1.5.5. With the setting at zero the new version behaves like the old one, to within the noise of my connection.

Test 2: a recursive query

The article walks a git history with a recursive CTE. My data is a straight line of 20,000 commits where every 1,000th commit is a merge with a second parent.

COPY (SELECT range AS id FROM range(1, 20001))
  TO 'commits.parquet';
COPY (
  SELECT id AS commit_id, id - 1 AS parent_id FROM range(2, 20001) t(id)
  UNION ALL
  SELECT id, id - 50 FROM range(1000, 20001, 1000) t(id)
) TO 'commit_parents.parquet';commits.sql

CREATE TABLE commits AS FROM 'commits.parquet';
CREATE TABLE commit_parents AS FROM 'commit_parents.parquet';
.timer on
WITH RECURSIVE ancestors(id) AS (
    SELECT max(id) FROM commits              -- HEAD
  UNION
    SELECT p.parent_id FROM ancestors a
    JOIN commit_parents p ON p.commit_id = a.id
)
SELECT count(*) FROM ancestors;walk.sql

duckdb-2.0 < commits.sql
duckdb-1.5.5 -list < walk.sql | tail -2
duckdb-2.0 -list < walk.sql | tail -2

20000
Run Time (s): real 1.727 user 3.134081 sys 1.489055
20000
Run Time (s): real 0.066 user 0.065740 sys 0.000690output

Both find all 20,000 commits. 1.73 seconds against 0.066, about 26 times.

A history like this is the worst case for a recursive CTE: the working set is one row, so there are close to 20,000 iterations, and each one is a join against commit_parents. According to the article, the old engine did the build side of that join again in every iteration, and 2.0 builds the hash table once and only probes it afterwards. The CPU columns agree: 4.6 seconds of user plus system time on 1.5.5 for a table of 20,019 rows, and 0.066 on 2.0.

The article’s range for the old version is 1.8 to 16 seconds. Mine sits at the low end of it. I do not know what their data looks like, so I cannot say why.

Test 3: JSON as VARIANT

Five million events, one JSON object per line:

COPY (
  SELECT
    ['view', 'click', 'purchase', 'signup'][1 + i % 4] AS type,
    {'id': i % 100000,
     'country': ['FR', 'DE', 'TR', 'US', 'GB', 'ES', 'IT', 'NL'][1 + (i // 4) % 8],
     'premium': i % 5 = 0} AS "user",
    {'amount': round(1 + (i * 37 % 10000) / 100.0, 2),
     'currency': 'EUR',
     'items': 1 + i % 5} AS props,
    [['a', 'b'], ['b', 'c'], ['a'], ['c', 'd', 'e']][1 + (i // 32) % 4] AS tags
  FROM range(5000000) t(i)
) TO 'events.ndjson' (FORMAT json);events.sql

{"type":"view","user":{"id":0,"country":"FR","premium":true},"props":{"amount":1.0,"currency":"EUR","items":1},"tags":["a","b"]}one

The same file is loaded twice, once as JSON text and once as VARIANT:

CREATE TABLE ev AS
  SELECT json AS payload FROM read_ndjson_objects('events.ndjson');load_json.sql

CREATE TABLE ev AS
  SELECT json::VARIANT AS payload FROM read_ndjson_objects('events.ndjson');load_variant.sql

duckdb-2.0 < events.sql
duckdb-2.0 json.db < load_json.sql
duckdb-2.0 variant.db < load_variant.sql
ls -lh json.db variant.db | awk '{print $5, $9}'

163M json.db
24M variant.dboutput

VARIANT is 6.8 times smaller here. In the article it is 2.6 times (224 MB to 85 MB). My events are very regular: every row has the same fields with the same types, and most values come from short lists. That is the best case for a format that turns repeated fields into real columns, so do not expect this ratio on real logs.

The three queries

.timer on
-- Q1: filter on two fields
SELECT count(*) FROM ev
WHERE payload.type::VARCHAR = 'purchase'
  AND payload.user.country::VARCHAR = 'FR';
-- Q2: sum of a numeric sub-field, grouped
SELECT payload.user.country::VARCHAR AS country, sum(payload.props.amount::DOUBLE) AS amount
FROM ev WHERE payload.type::VARCHAR = 'purchase' GROUP BY ALL ORDER BY 1;
-- Q3: list lookup
SELECT count(*) FROM ev WHERE list_contains(payload.tags::VARCHAR[], 'c');queries.sql

On the JSON table I had to write them differently, see the first pitfall below.

.timer on
SELECT count(*) FROM ev
WHERE (payload->>'type') = 'purchase' AND (payload->'user'->>'country') = 'FR';
SELECT payload->'user'->>'country' AS country, sum((payload->'props'->>'amount')::DOUBLE) AS amount
FROM ev WHERE (payload->>'type') = 'purchase' GROUP BY ALL ORDER BY 1;
SELECT count(*) FROM ev WHERE list_contains((payload->'tags')::VARCHAR[], 'c');queries_json.sql

duckdb-2.0 json.db < queries_json.sql | grep real
duckdb-2.0 variant.db < queries.sql | grep real

Run Time (s): real 0.065 user 0.800304 sys 0.038216
Run Time (s): real 0.082 user 1.032402 sys 0.016516
Run Time (s): real 0.062 user 0.790101 sys 0.005683

Run Time (s): real 0.006 user 0.039933 sys 0.007629
Run Time (s): real 0.004 user 0.047792 sys 0.006391
Run Time (s): real 0.548 user 7.322205 sys 0.035466output

The first block is JSON, the second is VARIANT.

• The filter goes from 65 ms to 6 ms and the sum from 82 ms to 4 ms. The article calls the mechanism shredding: when the data is written, fields that appear in every row with one type are stored as their own columns. A filter on type then reads one small column, and nothing is parsed at query time.
• The list lookup goes the other way: 62 ms on JSON, 548 ms on VARIANT, almost nine times slower. It is also the only query that keeps every core busy, with 7.3 seconds of CPU inside half a second of wall time.

The article has the same pattern with larger numbers: 357 ms on JSON and 1,960 ms on VARIANT for its list query. So this is a reproduced result and not an accident of my data. If the queries you care about look into arrays, measure that query before changing the column type.

VARIANT already existed in 1.5

The type is not new in 2.0. What is new is the shredding. The same data and the same three queries on 1.5.5:

duckdb-1.5.5 variant155.db < queries.sql | grep real

Run Time (s): real 0.865 user 10.446251 sys 0.404249
Run Time (s): real 0.859 user 10.843551 sys 0.416506
Run Time (s): real 0.818 user 10.765043 sys 0.387747output

Around 850 ms for each one, with more than 10 seconds of CPU. All three cost about the same, which is what a full read of every value looks like. That is more than ten times slower than the JSON table on 2.0 (I did not time the JSON table on 1.5.5). On 2.0 the two field queries are more than 100 times faster than on 1.5.5, and the list query moves only from 818 to 548 ms.

Four things that cost me time

The dot syntax returns zero rows on a JSON column. payload.type::VARCHAR = 'purchase' works on VARIANT. On my JSON column the same filter matched nothing: the cast keeps the JSON quotes, so the value is "purchase" with the quote characters inside the string. The ->> operator returns the text without them. A benchmark that counts zero rows is very fast and means nothing, so check the counts before the timings.

->> needs parentheses in a comparison. With two conditions joined by AND and no parentheses, the query stops with a binder error:

Binder Error: No function matches the given name and argument types 'json_extract(BOOLEAN, BOOLEAN)'.

The comparison is evaluated before the arrow. (payload->>'type') = 'purchase' is what I ended up with.

VARIANT in a 1.5.5 database file needs a storage version. A plain CREATE TABLE with a VARIANT column in a new database file is refused:

Invalid Input Error: VARIANT columns are not supported in storage versions prior to v1.5.0 (database "v155" is using storage version v1.0.0+)

Attaching the file with a newer storage version fixes it:

ATTACH 'variant155.db' AS d (STORAGE_VERSION 'v1.5.0'); USE d;
CREATE TABLE ev AS
  SELECT json::VARIANT AS payload FROM read_ndjson_objects('events.ndjson');

The 2.0 client changes its output when it thinks an agent is calling it. I ran the tests from a script, and the 2.0 alpha printed a different format. It says why:

duckdb agent mode on: AI_AGENT is set and stdout is not a terminal; -no-agent turns it off, .help agent explains the output

1.5.5 does not know the -no-agent flag, so the same command line cannot be used for both. I removed the AI_AGENT and CLAUDECODE variables from the environment of the script. If the output of a comparison script looks different between the two versions, this is the first thing to look at.

How much the numbers move

The table has one run per cell. Across the runs I did while preparing this:

• S3 on 1.5.5: between 19 and 22 seconds, and one run of 30 seconds that I count as an outlier of my connection.
• S3 on the 2.0 alpha: between 5.9 and 8.7 seconds.

So “three times” is a fair summary and “3.2 times” is one sample. The slowest 2.0 run is still more than twice as fast as the fastest 1.5.5 run.

I did not collect the same spread for the local tests. They do not touch the network, and the differences there are a factor of 9 and more, which is far outside anything I saw between two runs.

What this does not show

• One machine, one network, single runs.
• 2.0 is an alpha. The release can be faster or slower than this build.
• My generated data is more regular than real events. That helps VARIANT, in size more than anything.
• My JSON timings are about five times lower than the article’s. The machine, the data and the way the queries are written all differ, and I cannot separate the three.
• No other database is in this comparison. It is DuckDB against DuckDB.

Source

• Why DuckDB 2.0 is faster, MotherDuck