DuckDB 2.0: server mode, VARIANT, triggers and a mode for agents

The DuckDB team has released DuckDB 2.0.0 LTS “Cinnamon Teal”, named after a duck as always: the cinnamon teal, a reddish-brown duck from the western Americas. If 1.0 was the stability release, 2.0 is, according to its authors, their biggest feature release so far, with close to 20,000 commits since 1.5.

It’s also the first major release since AWS acquired DuckLabs, and it’s packed. I’ve run a few tests, so here’s what I found most interesting and a few things I ran into along the way.

Three supported versions

With this release, three DuckDB lines coexist:

The CLI and the Python client are already available. Other clients, such as Go, R or Java, and some extensions may take a few more days.

DuckDB as a server

DuckDB has always been an embedded, in-process database. Version 1.5.3, in May, introduced the Quack protocol for using it in client-server mode, and in 2.0 it arrives polished, with performance and stability improvements, plus a new statement, CONNECT, to attach to a remote database and switch to it in one step.

On the server:

CALL quack_serve(token = 'my_token');

CREATE TABLE events (date DATE, name VARCHAR);
INSERT INTO events VALUES ('2027-04-07', 'DuckCon #8');

And on the client:

ATTACH 'quack:localhost' AS qk (TOKEN 'my_token');
CONNECT qk;
SELECT * FROM events;

From the CLI it’s even more direct: duckdb -serve starts a server and duckdb -connect connects to it. By default the server only listens on localhost, and to accept connections from other machines they recommend putting it behind a reverse proxy with TLS.

CONNECT isn’t limited to Quack: it also works with PostgreSQL and MySQL, and in that case a new optimizer pushes queries to the remote server instead of pulling the tables over the network.

I tried it from Python with two processes and it works: the server listens on http://localhost:9494 and the client reads the table with ATTACH and the qualified name (qk.events). That said, in my test, after CONNECT the Python client wouldn’t materialize results (materialize() is not available while CONNECT-ed), so from Python I’m sticking with ATTACH for now.

Asynchronous I/O

DuckDB 2.0 reads Parquet, CSV and its own format with asynchronous I/O. Reading no longer depends on query processing: requests are issued in the background and several are kept in flight at once. For remote reads, like files on s3://, this means much more parallelism and noticeably faster queries.

It’s the improvement I’m most interested in, because when data sits in remote storage, the latency of each request usually matters more than the CPU. It complements what I wrote in the post on file formats and performance.

VARIANT, JSON on steroids

The VARIANT type stores semi-structured data, like JSON, but not as text: each row can have a different shape, and DuckDB detects the common structure and “shreds” it into columns so it compresses well and runs fast. It appeared in 1.5, and in 2.0 it’s integrated end to end, including reading and writing Parquet.

CREATE TABLE events (payload VARIANT);
INSERT INTO events VALUES
    ('{"user": {"id": 42, "tags": ["a", "b"]}}'::JSON::VARIANT),
    ('{"event": "click", "x": 10, "y": 20}'::JSON::VARIANT);

SELECT variant_type(payload), variant_keys(payload) FROM events;

A mode for agents

This one made me smile, and it makes a lot of sense. More and more, whoever uses the DuckDB CLI isn’t a person but an agent like Claude Code, Codex or Cursor. The 2.0 CLI detects when it’s being run by an agent and changes its behaviour:

• It prints compact Markdown tables instead of padded boxes.
• It says clearly when a result has been truncated.
• It stops runaway queries early.
• It returns errors as JSON.
• It reports the estimated cost of long queries before running them, so the agent can decide whether to wait.

In their TPC-H tests, agent mode cut the amount of output the model had to read by 59%. It can be forced with -agent or turned off with -no-agent.

Triggers

DuckDB 2.0 brings triggers: CREATE TRIGGER with BEFORE and AFTER, FOR EACH ROW and FOR EACH STATEMENT, and transition tables with REFERENCING OLD/NEW TABLE. The classic case is an audit table:

CREATE TABLE target (id INTEGER, val INTEGER);
CREATE TABLE audit (id INTEGER, old_val INTEGER, new_val INTEGER);

CREATE TRIGGER trg_audit
    AFTER UPDATE ON target
    REFERENCING OLD TABLE AS o NEW TABLE AS n
    FOR EACH STATEMENT
    INSERT INTO audit
    SELECT n.id, o.val, n.val FROM o JOIN n ON o.id = n.id;

INSERT INTO target VALUES (1, 10), (2, 20);
UPDATE target SET val = val + 1 WHERE id = 1;

FROM audit;

The announcement includes a more ambitious example: keeping a statistics table updated incrementally with two triggers and MERGE INTO, which is almost a materialized view. Materialized views have long been on DuckDB’s future work list, and triggers bring them closer. Some combinations aren’t supported yet, such as BEFORE ... FOR EACH ROW.

More new features

• A new SQL parser. DuckDB used a parser derived from PostgreSQL’s, generated with YACC/Bison. The default parser is now its own PEG-based one, which lets extensions add new syntax and gives error messages with the exact position. It should be compatible with the old one, and if something breaks you can switch back with SET enable_legacy_parser = true;.
• Your own extension repositories, signed with RSA keys, so an organisation can distribute its extensions as if they were official ones. It’s still a work in progress and the syntax may change.
• Extensions: DuckLake 1.1, improvements in Iceberg (REST catalogs) and in Delta, which can now create tables and time travel.
• SQL: data-modifying CTEs, NEAREST joins for vector similarity search, nested schemas, JSON mutation functions (json_set, json_insert…), FETCH FIRST and the $variable shorthand instead of getvariable('x').
• Timezones without ICU: the icu extension no longer depends on the ICU library. It uses IANA database data compressed into about 45 kB, and it’s faster too.

Before upgrading

There are a few changes worth keeping in mind:

• Storage format. DuckDB 2.0 reads and writes older files, and those stay readable by earlier versions. But a file created with DuckDB 2.0 can’t be read by earlier versions. If several processes on different versions share files, that’s the tricky part. They recommend testing on a shadow system and, for critical workloads, waiting for the first patch release, planned for November.
• Lambdas. The arrow syntax (x -> x + 1) is disabled by default, and the new one is lambda x: x + 1. I checked: with the arrow, the query fails with an error explaining the change. It can be temporarily re-enabled with SET lambda_syntax = 'enable_single_arrow';, but it will be gone in 2.1.
• Python 3.11 or later. While testing it with uv I found that the duckdb==2.0.0 package requires Python 3.11 at minimum. With an older Python, the package manager can’t find a compatible version.
• $variable in Python. In the Python client, $x inside a query is interpreted as a named parameter, not as the DuckDB variable, so the value has to be passed as a parameter.
• Platforms and building. A C++17 compiler is now required, the amalgamation (all the code in one file) is deprecated, and macOS x86_64 is being phased out: community extensions are no longer built for that platform and 2.1 will drop support for it.

My takeaway

What strikes me most about this release is that DuckDB is no longer just “SQLite for analytics”. With Quack and CONNECT it can work as a server; with triggers and data-modifying CTEs it moves closer to what you’d expect from a transactional database; and with agent mode it acknowledges that a good part of its use is no longer people typing in a terminal.

Day to day, with data in remote storage, asynchronous I/O is probably what will be noticed first, and VARIANT can greatly simplify working with JSON. But with the storage format change and the minimum Python version, the team’s own recommendation seems sensible to me: test it in parallel, and for production, wait for the first patch release in November.

Source: Announcing DuckDB 2.0.0 LTS “Cinnamon Teal”, the DuckDB team.