DuckDB 2.0 on a MySQL analytical replica: what we measured

DBTrail keeps a Parquet copy of your MySQL data and answers analytical queries on it with DuckDB. DuckDB 2.0 came out on October 21. Before switching, we wanted to know one thing: does it make DBTrail faster?

Short answer: the heaviest queries get up to 44% faster. Some mid-size joins get slower. One setting recovers part of that, and we reported the rest to the DuckDB team, with a small example that shows it.

The numbers

We used the same 14 analytical queries and the same data for every version: an open analytics benchmark derived from TPC-C, with 200 warehouses and 78 million order lines. It does not follow the TPC specifications, and the results are not comparable to published TPC results. Four DuckDB builds answered from the same Parquet copy, on one machine with 4 cores (AWS r7a.xlarge). Each query ran on one version at a time, in rotating order, for an hour. These are median times over 45 rounds:

The table shows the seven queries that moved the most. The other seven moved by 20% or less, or by a few milliseconds.

• 1.5 is the same as 1.4. You can upgrade without a performance surprise.
• 2.0 is a big win on heavy joins and aggregations: 21% to 44% faster on four of them.
• 2.0 is slower on some mid-size joins, by up to 82% (Q09).
• Overall, 2.0 finishes the 14 queries 15% sooner, or 18% with one setting.

All four versions return the same answers.

Why some queries got slower

We followed the slowdown down to two changes in DuckDB 2.0.

1. A new optimizer step that guesses wrong on our data. DuckDB 2.0 can sum rows before it joins them (partial_aggregate_pushdown). On our tables it expected about 5,000 groups and got 3.3 million. Turning it off is one line, and that is the “tuned” column above:

SET disabled_optimizers = 'partial_aggregate_pushdown';

2. A join shortcut that 2.0 no longer takes. When DuckDB joins a small filtered table to a big one, it passes the key range to the scan of the big table. In 1.5 the scan uses that range to drop rows early. In 2.0 the scan receives the same range but does not apply it to rows, so the join gets about ten times more rows. No setting changes this, and nothing on our side fixes it either: we tried two other ways to write the query, and both were slower.

What we did about it

We didn’t work around it quietly. We wrote a small example that anyone can run in a minute to see it: about 40 lines of SQL that generate two Parquet files and run one join. It needs no DBTrail and none of our data. On 1.5, the scan passes on 3 million rows. On 2.0 it passes on 30 million, and the query takes 1.6x as long. It also happens with DuckDB’s own tables, not only with Parquet, and on the latest dev build.

Before filing it, we had the report reviewed for accuracy, reproducibility and tone. The review caught a wrong explanation in our first draft. Then we filed it: duckdb/duckdb#28269. Two days later the DuckDB team confirmed they could see it too. The report asks whether the change is intentional, and offers to test a fix against the example and our larger dataset.

DuckDB is the reason DBTrail can answer analytical queries on MySQL data in seconds. We use it for free, and nobody asks us for anything in return. That is the deal that makes open source work: you take a lot, and you give back what you can. DBTrail is open source too, and this time what we could give back was a clear report that anyone can check in a minute.

What this means for you

DuckDB 1.4 support ends on November 17, so DBTrail moves to DuckDB 1.5 now. It runs at the same speed as today and is supported until March 2027. We’ll move to 2.0 once #28269 is fixed, so the mid-size joins don’t pay for the heavy ones getting faster.

If you run DuckDB 2.0 yourself on joins with a selective filter on one side, check EXPLAIN ANALYZE. If the scan of the big table passes on far more rows than the join keeps, you are probably seeing the same thing. Follow #28269 for the fix.