In v2.0, this pipeline works end to end: shredded execution straight from storage (#20912), extraction pushdown into scans (#22478), shredded VARIANT reading and writing for Parquet, and a family of variant_* functions:
`CREATE TABLE events (payload VARIANT); INSERT INTO events VALUES (’{“user”: {“id”: 42, “tags”: [“a”, “b”]}}’::JSON::VARIANT);
SELECT variant_type(payload), variant_keys(payload) FROM events;
SELECT * FROM events WHERE variant_contains(payload, {‘user’: {‘id’: 42}}::VARIANT); `
Longer term, likely soon after v2.0 (but don’t hold us to it), we plan to back the regular JSON type with VARIANT, so existing JSON workloads get all of these benefits without changing a single query.
Triggers have been a long-standing feature request, and DuckDB v2.0 delivers them in full: 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: something happens in the system, and a trigger records what changed. For example:
`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 * 10 WHERE id <= 2; SELECT * FROM audit; `
id old_val new_val
1 10 100
2 20 200
Triggers fit naturally with long-running DuckDB services, and we are also planning to use them internally to build several upcoming features. They are fully exposed at the SQL level too, so you can build your own cool stuff with them.
As always, DuckDB’s SQL dialect keeps growing. A few favorites from this release cycle:
With NEAREST joins (#24137), top-k similarity search becomes a join clause, handy for vector and embedding workloads:
SELECT q.user_id, t.product_id FROM users q INNER JOIN products t APPROX NEAREST 2 BY SIMILARITY array_cosine_similarity(q.embedding, t.embedding);
DML inside CTEs (#21634, #21997, #24217) lets you use INSERT, UPDATE, DELETE, and COPY as pipeline steps:
WITH moved AS MATERIALIZED ( DELETE FROM staging RETURNING * ) INSERT INTO archive SELECT * FROM moved;
Nested schemas (#23492, #24222) allow schemas within schemas:
CREATE SCHEMA finance; CREATE SCHEMA finance.reports; CREATE TABLE finance.reports.q3 (revenue DECIMAL);
The new variable syntax (#21194) lets you write $x anywhere an expression is allowed, no more getvariable(...) verbiage:
SET VARIABLE threshold = 100; SELECT * FROM orders WHERE amount > $threshold;
The JSON mutation functions json_set, json_insert, json_replace, and json_remove (#23786) finally let you modify JSON documents in place:
SELECT json_set('{"a":1}', '$.b', '2');
json_set(’{“a”:1}’, ‘$.b’, ‘2’)
{“a”:1,“b”:2}
And recursive CTEs with USING KEY aggregation (#19481) enable iterative algorithms in pure SQL, backed by the rewritten recursive CTE engine described below:
WITH RECURSIVE tbl(a, b) USING KEY (a, avg(b)) AS ( SELECT 1, 5 UNION SELECT a, b - 1 FROM tbl WHERE b > 0 ) TABLE tbl;
a b
1 2.5
There is more: SQL-standard FETCH FIRST 2 ROWS ONLY (#23533), OVERLAY() (#22456), UNNEST in GROUP BY (#23644), and well-defined MERGE / UPDATE ... FROM semantics for multi-matched rows (#24058).
Interacting with object stores like S3 is central to the DuckDB experience: your data has to come from somewhere, and it often sits in object storage. DuckDB has long been able to read from object stores in parallel, but synchronous access placed a limit on how fast this could go. DuckDB v2.0 introduces asynchronous I/O throughout the engine. We described the design in detail in a dedicated blog post.
Thanks to asynchronous access, the I/O layer now scales independently from the query processing layer, which means far more parallelism for remote reads and dramatically faster queries on network storage. Parquet support came first (#23662), with CSV (#23961) and DuckDB’s own file format (#24654) following, along with asynchronous Parquet writes (#23283) and new MMAP and DIRECT_IO modes (#22988). Local storage benefits a little too, but network storage is where you will see the big gains.
As with every release, a lot of work went into making your existing queries faster without you doing anything. To pick some highlights: partial aggregates are now pushed below joins (#22572) and redundant aggregations are reused (#24543), the recursive CTE engine has been rewritten (#22211), aggregations now spill to disk when they outgrow memory (#24499), and the Windows CLI got approximately 2.2× faster at multi-threaded result materialization (#24036).
How much faster can this get? Here is a microbenchmark you can run on a laptop: single-source reachability over a graph with one million edges, written as a plain recursive CTE.
`CREATE TABLE edges AS SELECT (range % 100_000)::INTEGER AS src, ((range * 13 + 7) % 100_000)::INTEGER AS dst FROM range(1_000_000);
WITH RECURSIVE reachable(node) AS ( SELECT 0 UNION SELECT dst FROM edges, reachable WHERE src = node ) SELECT count(*) FROM reachable; `
Version Run time
DuckDB v1.5.4 4.90 s
DuckDB v2.0 (preview) 0.12 s
As you can see, DuckDB v2.0 is about 40× faster (!) for the same recursive query.
Row-group pruning has been massively expanded: [min-max indexes (zone maps)](/docs/current/sql/indexes.html#min-max-index-zonemap %}) and Parquet Bloom filters now skip data for structs, lists, decimals, UUIDs, IN filters, and even function predicates:
-- these now prune row groups instead of scanning them: SELECT * FROM logs WHERE contains(message, 'ERROR'); SELECT * FROM t WHERE substr(code, 1, 3) = 'NL-'; SELECT * FROM 'data/*.parquet' WHERE id IN (1, 5, 9);
Query planning also becomes partition-aware (#22336). Lakehouse formats (DuckLake, Iceberg and plain Hive-partitioned Parquet on S3) are all partitioned, and exploiting that partitioning is often the difference between scanning a dataset and skipping most of it. In v2.0, the planner and optimizer take full advantage of existing partitioning, and partitioned writes have been reworked as well (#22225, #22620).
DuckDB v2.0 bumps the default storage format version to v2.0.0 (#22875). The headline change is buffer-managed ART indexes (#21458, #23605): indexes are no longer pinned in memory, which means large indexed tables open instantly and their indexes are paged in on demand.
Column metadata is now loaded lazily (#22333), so wide tables open faster too. The DICT_FSST string compression method is enabled by default (#23733), deletes are stored compactly (#24336), and the storage layer performs much stronger corruption validation on read. In short: databases with big indexes and wide tables open faster and use far less memory.
DuckDB has famously always used a parser derived from PostgreSQL’s. We have decided that enough is enough: v2.0 ships our own modern, extensible PEG-based parser (#22194), an idea we first explored in our 2024 post on runtime-extensible parsers. This change ties into the extension ecosystem: extensions can now hook into the grammar itself, so expect extensions that expose entirely new SQL syntax. It also brings better error messages with precise source locations, and the first dialect compatibility mode:
SET dialect_compatibility_mode = 'spark';
You should not actually notice anything from the parser swap as we designed it to be compatible with the old one. If you do notice, please file an issue.
Timezone-aware timestamps, calendars, and collations in DuckDB have always been powered by the ICU library. ICU is a fine library, but we only ever used a small slice of it, while still carrying it around in every DuckDB distribution. In v2.0, the ICU library is gone entirely: the icu extension now implements timezones, calendars, and collations itself (#24463, #24403), with the timezone data built directly from the IANA database and compressed down to around 45 kB. Everything keeps working exactly as before:
SELECT '2026-08-14 12:00:00'::TIMESTAMPTZ AT TIME ZONE 'Europe/Paris'; SELECT * FROM names ORDER BY name COLLATE de;
Besides being much smaller and easier to keep up to date, the new implementation is also simply faster. Here’s a quick microbenchmark on a MacBook that converts 25 million timestamps to a timezone and filters 5 million strings with a German collation:
Extensions are one of the best things about DuckDB, but today, most of them, including our own, build against the unstable C++ API. That means extension authors have to re-target and rebuild for every DuckDB release, and community extensions can silently disappear when their authors stop keeping up. DuckDB v2.0 broadens the stable C API far enough that extensions can be written once, built once,
To make this sustainable over the long run, the C API is now generated from a declarative, versioned specification (#24135): every function in duckdb.h, duckdb_extension.h, and the extension ABI is described in YAML in the api_spec/ directory, with its full lifecycle on record, and CI verifies the committed headers against the spec so API and ABI can no longer drift apart. The release also brings unified symbol versioning (#24435), custom allocation handlers (#23945), and static linking of C API extensions into your application (#22251).