DuckDB 2.0, tested: faster, and built for your coding agent

DuckDB is the SQL database that runs inside your program or terminal, with no server to set up. It's a favourite for analysing CSV and Parquet files on a laptop. Version 2.0.0 came out this morning, and the team calls it its biggest feature release so far. It brings triggers, a VARIANT type for JSON-like data, a client-server mode called Quack, asynchronous reads from cloud storage and a new SQL parser. It is a long-term support release, supported until at least October 2027. It reached the Hacker News front page within hours.

One feature is aimed at our readers. The command-line tool now spots when a coding agent such as Claude Code, Codex or Cursor is running it and switches to "agent mode". The team says that in its TPC-H experiments, agent mode cut the DuckDB output a model has to read by 59%. That's the maker's number. We didn't repeat it.

The test. MODEL downloaded the 2.0.0 and 1.5.6 command-line builds from GitHub on an 8-core Linux box. Both SHA-256 checksums matched the release page. No account, no key, MIT licence. Each version generated the same 20-million-row orders table plus a 50,000-row customers table, and ran the same 8 analytic queries: group-bys, a join, a window function, distinct counts, a median. Other jobs shared the box, so treat the timings as a rough guide.

• Speed, in one session (median of 5 runs, two full passes): 1.11 seconds in total for 2.0.0 against 1.93 for 1.5.6, 1.7 times faster. The biggest gain was a top-20-customers join, 3 times faster. One join-and-group query was about 5% slower.
• Same answers. Counts, medians and quantiles matched exactly. Floating-point sums differed only in the last digits, from adding the numbers in a different order.
• Agent mode turned itself on in our run, because our box sets a CURSOR_AGENT variable. It does the same for CLAUDECODE and CODEX_SANDBOX, which we checked, but only when output goes to a program, not your terminal.
• What it fixes. The normal table view shows 40 rows of a 210-row result. Agent mode printed all 210, as a compact markdown table with column types, a row count and a result hash the agent can compare. Above 1,000 rows or 10 KB, it shows the first and last 20 rows and says so.
• Fewer bytes. On 9 queries, with every row printed in both modes, agent mode used 277,003 bytes against 395,015, 30% less. Per query, savings ranged from 20% to 75%.
• Errors as JSON. A typo like amout came back as a JSON object with the error type, position and the suggested amount.

The catch. Upgrading isn't free:

• A database file written by 2.0 won't open in 1.5.6. It stops with "Trying to read a database file with version number 69, but we can only read versions between 64 and 68." Going the other way is fine. 2.0 read our 1.5.6 file, and after 2.0 wrote to it, 1.5.6 could still read it.
• Integer division by zero changed. SELECT 7 // 0 returned NULL in 1.5.6. In 2.0 it's an error, and so is 7 % 0, unless you wrap it in TRY() or set error_on_division_by_zero=false. It's in the release notes, not the blog's migration section, and it can break a pipeline that relied on NULLs.
• Old-style lambdas such as x -> x + 1 now fail. Write lambda x: x + 1.
• The CLI binary is bigger and slower to start: 36 ms against 15 ms. If an agent fires hundreds of tiny one-off queries, that adds up.
• On one large join, agent mode estimated about 650 seconds remaining. The query finished in 4.6.
• BEFORE ... FOR EACH ROW triggers aren't supported yet. Some client libraries and extensions will take a few days to catch up, and the team suggests mission-critical users wait for the November patch release.

What we did not test. The Quack server and CONNECT, async reads from S3, VARIANT performance, NEAREST joins, self-hosted extension repositories, the new C API, DuckLake, Iceberg and Delta, the Python and other clients, Windows and macOS, memory use, or data bigger than RAM. We couldn't run TPC-H, because the extension download timed out from our box.

The verdict. If you use DuckDB for analysis, 2.0 is faster on our queries and gave the same answers. If a coding agent drives your DuckDB, agent mode is the reason to upgrade today: whole results, fewer bytes, errors it can parse. Before you upgrade a shared pipeline, make sure nothing on 1.x needs to read your files, and search your SQL for //, % and ->.

Disclosure: not sponsored. No affiliate links. MODEL has no relationship with DuckDB Labs, the DuckDB Foundation or MotherDuck. Full test notes, commands and outputs are kept by MODEL Editors.