![\"\"](\"https://duckdb.org/images/blog/thumbs/duckdb-release-2-0-0.svg\")
The DuckDB team
\nTL;DR: The DuckDB team is happy to announce that today we're releasing DuckDB version 2.0.0 LTS, codenamed “Cinnamon Teal” (Spatula cyanoptera). This is our biggest feature release so far, and it ships a polished version of the Quack protocol, asynchronous I/O, a first-class VARIANT type, agent mode, triggers, an extensible SQL parser and much more, built from close to 20,000 commits since DuckDB 1.5.
We are proud to release DuckDB 2.0.0, codenamed “Cinnamon Teal” after the Spatula cyanoptera, a strikingly reddish-brown duck found in the western Americas.
\nWhile DuckDB 1.0 was all about stability and backward compatibility, DuckDB 2.0 is above all a feature release.\nIn this blog post, we summarize the highlights and then walk through the breaking changes and how to migrate.\nWe showcased many of the new features in “A Preview of DuckDB v2.0”, which covers each one in more depth.\nFor the complete release notes, see the release page on GitHub.
\n\nTip To install the new version, please visit the installation page.\nSome clients, such as the CLI and Python, are already available. Other client libraries (e.g., Go, R, Java) and some extensions can take a few days to release due to the extra changes and review rounds required.
With this release, there are currently three DuckDB lines available.\nDuckDB 2.0 is a long-term support (LTS) release with support until at least October 2027.\nThe previous LTS release, version 1.4, is still supported until late November 2026.\nTo ensure a smooth transition and allow sufficient time for testing, we extended the support period of DuckDB 1.5 until March 2027, effectively also turning it into an LTS release.\nFor details, see the release calendar.
\nTo find your way around the documentation for all of the new features, we also published a “What's New in the Docs for 2.0” page that links each feature below to its documentation.
\nDuckDB has been an in-process database since day one.\nIn DuckDB 1.5.3, released in May, we introduced the Quack protocol that lets you run DuckDB in a traditional client-server setup, along with the quack extension, which implements DuckDB's native protocol, so any DuckDB process can serve its databases.
CONNECTDuckDB 2.0.0 ships a polished version of the Quack protocol with substantial performance and stability improvements. It also introduces the new CONNECT statement that allows attaching and switching to a remote database in a single command for better ergonomics:
CALL quack_serve(\n token = 'my_token'\n);\n\nCREATE TABLE events\n (date DATE, name VARCHAR);\n\nINSERT INTO events VALUES\n ('2027-04-07',\n 'DuckCon #8');quack:
\nATTACH 'quack:localhost'\n AS qk (TOKEN 'my_token');\n\nCONNECT qk;\nSELECT * FROM events;\n\n┌────────────┬────────────┐\n│ date │ name │\n│ date │ varchar │\n├────────────┼────────────┤\n│ 2027-04-07 │ DuckCon #8 │\n└────────────┴────────────┘For simplicity, this example runs the server and the client on the same machine. By default, the server only listens on localhost. To accept connections from other machines, keep the server on localhost and put it behind a TLS-terminating reverse proxy. The allow_other_hostname option, which exposes the server directly, is only meant for testing.
The CONNECT statement is not limited to Quack: it points your session at any remote database that supports it, including PostgreSQL and MySQL. For these, the new remote pushdown optimizer ships queries directly to the remote server instead of pulling tables over the wire.
The CLI also has shortcuts for setting up a Quack client-server pair: the new -serve and -connect arguments start a Quack server or connect to one directly from the command line:
duckdb -serveCREATE TABLE t AS\n SELECT 42 AS x;quack:
\nduckdb -connectFROM t;\n\n┌───────┐\n│ x │\n│ int32 │\n├───────┤\n│ 42 │\n└───────┘DuckDB 2.0 introduces asynchronous I/O for reading Parquet and CSV files. The I/O layer now scales independently from the query processing layer, issuing reads in the background and keeping multiple requests in flight. This means far more parallelism for remote reads and dramatically faster queries on object storage such as s3://.
We described the design in detail in a dedicated blog post.\nIn that post, we showed the network throughput over the course of a query with and without asynchronous I/O:
\n![\"Network](\"https://duckdb.org/images/blog/v20-release/sync-vs-async-network-throughput-light.svg\")
![\"Network](\"https://duckdb.org/images/blog/v20-release/sync-vs-async-network-throughput-dark.svg\")
Network throughput. Higher utilization and shorter runtime are better.
\nVARIANT Type Becomes a First-Class CitizenThe VARIANT type stores semi-structured, schema-less data such as JSON while keeping the performance of columnar execution.\nSo you can think of it as JSON on steroids: a VARIANT column can store differently shaped data in every row, but unlike JSON it is not a text format.
DuckDB 1.5 already introduced VARIANT, but DuckDB 2.0 fully integrates it. DuckDB 2.0 detects the common structure in your data and “shreds” it, so it compresses well and executes fast. The pipeline works end to end, from shredded execution straight out of storage to shredded VARIANT reading and writing for Parquet, backed by a family of variant_* functions. For example:
CREATE TABLE events (payload VARIANT);\nINSERT INTO events\nVALUES\n ('{"user": {"id": 42, "tags": ["a", "b"]}}'::JSON::VARIANT),\n ('{"event": "click", "x": 10, "y": 20}'::JSON::VARIANT);\n\nSELECT variant_type(payload), variant_keys(payload)\nFROM events;┌───────────────────────┬───────────────────────┐\n│ variant_type(payload) │ variant_keys(payload) │\n│ varchar │ varchar[] │\n├───────────────────────┼───────────────────────┤\n│ OBJECT │ [user] │\n│ OBJECT │ [event, x, y] │\n└───────────────────────┴───────────────────────┘Coding agents such as Claude Code, Codex and Cursor increasingly drive the DuckDB CLI on their users' behalf. The 2.0 CLI detects when it is being run by an agent and switches to agent mode: it prints compact Markdown tables instead of padded boxes, says clearly when a result was truncated, stops runaway queries early and reports errors as JSON.
\nLong-running SQL queries report their expected cost prior to running, so the agent can decide whether to wait. In our experiments on TPC-H, agent mode cut the amount of DuckDB output the model had to read by 59%. You can turn it on for manual testing using the -agent flag or instruct your agent to turn it off using the -no-agent flag.
DuckDB has always used a parser derived from PostgreSQL's. In 2.0, our own modern, extensible PEG-based parser, which debuted as an opt-in feature in DuckDB 1.5, becomes the default. Extensions can now hook into the grammar itself (an idea we first explored in our research on runtime-extensible parsers), so expect extensions that expose entirely new SQL syntax. Error messages now come with precise source locations. For details, see the PEG parser documentation. The new parser is designed to be compatible with the old one, so you should not notice the swap. If you do, please file an issue. In the meantime, you can switch back to the old parser using the legacy_parser extension by running SET enable_legacy_parser = true;.
The old parser had served DuckDB since its first commit in 2018. Its grammar was generated with YACC/Bison, which produces an LALR(1) parser, so as DuckSQL grew, even small additions could collide with existing rules and raise shift/reduce or reduce/reduce conflicts. A PEG grammar avoids this by trying alternatives in the order they are written, so adding syntax no longer risks breaking unrelated rules. Python itself moved to a PEG-based parser in version 3.9 for the same flexibility.
DuckDB 2.0 ships a revamped C API v2, which is considerably more powerful than its predecessor and exposes components such as file systems and table functions to extensions. Before marking it as stable, we would like to test it in the field, so please give it a try and send us your feedback!
\nYou can also register and host your own trusted extension repositories, so an organization can host and sign its own extensions and have them install and load just like the built-in ones:
\nSET allow_extension_repositories = 'allowed';\n\nCREATE EXTENSION REPOSITORY my_repo\n WITH PREFIX 'https://extensions.example.org';\n\nINSTALL AND LOAD my_ext FROM my_repo;A repository is a name, a URL prefix and one or more RSA public keys that are trusted to sign the extensions served from it. The prefix can point at anything DuckDB can read: a local path, https, s3, you name it. At CREATE time, DuckDB fetches the repository's public keys and pins them into the repository definition, printing each key's SHA-256 fingerprint so you can compare it against one published out of band.
This feature is still a work in progress and its syntax may change in future releases. For more details, see the Extension Repositories page.
\nDuckDB's core extensions are also shipping several new features, fixes and performance optimizations. These are too numerous to all list here, so we only highlight a few of them and will cover them in dedicated blog posts in the coming weeks:
\nATTACH options that derive a table's location from its namespace or load table metadata from the metadata-location file returned by the catalog. Further options control the URL prefix and the namespace separator.CREATE TABLE (including partitioning and Delta table properties), supports time travel and improves VARIANT support.DuckDB 2.0 delivers triggers: the CREATE TRIGGER statement with BEFORE and AFTER triggers, FOR EACH ROW and FOR EACH STATEMENT, transition tables via REFERENCING OLD/NEW TABLE, multiple triggers per event, RETURNING on triggered tables and DROP TRIGGER. The classic use case is audit tables, where a trigger records what changed in response to a table modification. Some combinations, such as BEFORE ... FOR EACH ROW and UPDATE ... FOR EACH ROW triggers, are not yet supported; see the limitations.
CREATE TRIGGER trg_audit\n AFTER UPDATE ON target\n REFERENCING\n OLD TABLE AS o\n NEW TABLE AS n\n FOR EACH STATEMENT\n INSERT INTO audit\n SELECT n.id, o.val, n.val\n FROM o JOIN n ON o.id = n.id;Triggers can also maintain materialized views incrementally! For example, to keep track of the average age of people per nationality in a table age_statistics, you can use two triggers – one for insert and another for delete operations – and store the sum and the count alongside the average.
To see more details, click here.
\nCREATE TABLE people (name VARCHAR, nationality VARCHAR, age INTEGER);\nCREATE TABLE age_statistics (\n nationality VARCHAR PRIMARY KEY,\n sum_age INTEGER,\n num_people INTEGER,\n average_age DOUBLE\n);On insertion, we aggregate the new rows per nationality and merge them into age_statistics, adding a row for each new nationality:
CREATE TRIGGER people_insert\n AFTER INSERT ON people\n REFERENCING NEW TABLE AS n\n FOR EACH STATEMENT\n MERGE INTO age_statistics s\n USING (\n SELECT nationality, sum(age) AS sum_age, count(*) AS num_people\n FROM n\n GROUP BY nationality\n ) d\n ON s.nationality = d.nationality\n WHEN MATCHED THEN UPDATE SET\n sum_age = s.sum_age + d.sum_age,\n num_people = s.num_people + d.num_people,\n average_age = (s.sum_age + d.sum_age) / (s.num_people + d.num_people)\n WHEN NOT MATCHED THEN INSERT\n VALUES (d.nationality, d.sum_age, d.num_people, d.sum_age / d.num_people);On deletion, we subtract the deleted rows and drop a nationality once its last person has left:
\nCREATE TRIGGER people_delete\n AFTER DELETE ON people\n REFERENCING OLD TABLE AS o\n FOR EACH STATEMENT\n MERGE INTO age_statistics s\n USING (\n SELECT nationality, sum(age) AS sum_age, count(*) AS num_people\n FROM o\n GROUP BY nationality\n ) d\n ON s.nationality = d.nationality\n WHEN MATCHED AND s.num_people = d.num_people THEN DELETE\n WHEN MATCHED THEN UPDATE SET\n sum_age = s.sum_age - d.sum_age,\n num_people = s.num_people - d.num_people,\n average_age = (s.sum_age - d.sum_age) / (s.num_people - d.num_people);Let's see people come and go:
\nINSERT INTO people VALUES\n ('Alice', 'US', 34), ('Bram', 'NL', 27), ('Csilla', 'HU', 45),\n ('Dave', 'US', 58), ('Eva', 'NL', 19), ('Femke', 'NL', 41);\nFROM age_statistics ORDER BY nationality;┌─────────────┬─────────┬────────────┬─────────────┐\n│ nationality │ sum_age │ num_people │ average_age │\n│ varchar │ int32 │ int32 │ double │\n├─────────────┼─────────┼────────────┼─────────────┤\n│ HU │ 45 │ 1 │ 45.0 │\n│ NL │ 87 │ 3 │ 29.0 │\n│ US │ 92 │ 2 │ 46.0 │\n└─────────────┴─────────┴────────────┴─────────────┘DELETE FROM people WHERE name = 'Alice';\nDELETE FROM people WHERE nationality = 'HU';\nFROM age_statistics ORDER BY nationality;┌─────────────┬─────────┬────────────┬─────────────┐\n│ nationality │ sum_age │ num_people │ average_age │\n│ varchar │ int32 │ int32 │ double │\n├─────────────┼─────────┼────────────┼─────────────┤\n│ NL │ 87 │ 3 │ 29.0 │\n│ US │ 58 │ 1 │ 58.0 │\n└─────────────┴─────────┴────────────┴─────────────┘Triggers bring us one step closer to materialized views, a long-standing item in the “future work” section of our roadmap.
\nAs always, DuckDB's friendly SQL dialect keeps growing. A few favorites from this release cycle:
\n$variable shorthand lets you write $x in most places where an expression is allowed, instead of getvariable('x').INSERT, UPDATE, DELETE and COPY as pipeline steps.NEAREST joins turn top-k similarity search into a join clause, handy for vector and embedding workloads.CREATE SCHEMA finance.reports.json_set, json_insert, json_replace and json_remove return modified copies of JSON documents (see also our blog post on JSON patches).There is more: DuckDB 2.0.0 supports the SQL-standard FETCH FIRST and allows UNNEST in the GROUP BY clause. It also ensures well-defined MERGE and UPDATE ... FROM semantics for cases when multiple rows are matched.
DuckDB 2.0 comes with a small set of breaking changes. For most users, upgrading is straightforward, but complex deployments running many DuckDB processes need more care. The storage format is the most likely source of problems: DuckDB versions before 2.0 cannot read database files created by DuckDB 2.0. DuckDB 2.0 can still read and write your existing database files, and these remain readable by older versions. We recommend testing 2.0.0 on a shadow system before switching production over. And, if your workload is mission-critical, you may want to wait for the first patch release, coming in November.
\nFor the full details, see the migration guide.
\nFollowing DuckDB's long stint on C++11, DuckDB 2.0 now requires a C++17 compiler.
\nWe also switched to Clang as the default compiler for our release builds. On Linux and macOS, both DuckDB and its core extensions are built with Clang. On Windows, the CLI is built with Clang, while most core extensions are still built with MSVC. We may move more Windows builds to Clang in the future, but this requires further changes.
\nTimezone-aware timestamps, calendars and collations have always been powered by the ICU library. In 2.0, the ICU library is gone entirely: the icu extension now implements timezones, calendars and collations itself, with the timezone data built directly from the IANA database and compressed down to around 45 kB.
Everything keeps working exactly as before, and the native implementation is also faster. If you find any issues, please let us know!
\nThe old single-arrow lambda syntax (x -> x + 1) is now disabled by default. Use the Python-style syntax instead:
SELECT list_transform([1, 2, 3], lambda x: x + 1);You can temporarily re-enable the old syntax with SET lambda_syntax = 'enable_single_arrow';. Both the flag and the single-arrow syntax will be removed entirely in v2.1.
The amalgamation build, which bundles DuckDB's entire source code into a single header and a single source file, is now deprecated. The amalgamation has grown too large to be realistically useful in most cases.
\nWe are gradually phasing out osx_amd64 from the list of fully supported platforms.\nFor the time being, DuckDB and core extensions are still built and supported, but community extensions are no longer built for this platform. DuckDB 2.1 will stop supporting osx_amd64.
These were a few highlights, but there are many more features and improvements in this release. There have been almost 20,000 commits by many contributors since DuckDB 1.5, shipping smaller features as well as performance and usability improvements. The full release notes can be found on GitHub.
\nWe would like to thank our community for the detailed issue reports and feedback that have driven this release, and our special thanks go to our contributors!
\n