By ByteBot
DuckDB published its v2.0 preview yesterday, August 17, and the headline number is hard to ignore: recursive CTEs are now 40 times faster. A 1-million-edge graph reachability query that took 4.90 seconds in v1.5.4 finishes in 0.12 seconds. That’s not an optimization — that’s a rewrite. Codenamed “Cyanoptera” and built from over 10,000 commits since March’s v1.5, the release targets fall 2026 with preview builds available now.
But the 40x number isn’t the most significant thing in this release. DuckDB is quietly changing what kind of database it is — and developers who run it in production need to know what’s coming.
Performance That Unlocks New Workloads
The recursive CTE speedup matters beyond benchmarks. Hierarchical reports, dependency graphs, shortest-path queries, and network reachability analyses were all genuinely painful in DuckDB before. At 40x faster, you stop reaching for a graph database extension or writing Python loops around SQL. That door is now open.
The rest of the performance story is similarly concrete. Timezone conversions on 25 million timestamps run 2.2x faster after DuckDB removed the ICU library entirely — timezone data is now built natively from the IANA database and compressed to roughly 45 KB. German collation filtering on 5 million rows runs 2.6x faster for the same reason. According to the official DuckDB v2.0 preview post, these gains come from partial aggregate pushdown below joins and a rewritten query optimizer — not just faster hardware assumptions.
Async I/O, also arriving in v2.0, handles Parquet files on S3 roughly 3x faster than synchronous reads, and CSV files on S3 up to 19x faster. That story has its own dedicated coverage:
Related: DuckDB Async I/O Lands in v2.0: S3 Queries Up to 19x Faster
VARIANT Type Finally Goes Production-Ready
The VARIANT type — DuckDB’s semi-structured data column — graduates to production-ready in v2.0. If you’ve been storing event logs, API responses, or any JSON-shaped data in a VARCHAR or JSON column, this changes your calculus. VARIANT parses data at ingestion into a binary, type-grouped format. There’s zero re-parsing at query time, and DuckDB automatically detects common structure and “shreds” it for columnar access. The result is up to 10-100x faster queries compared to plain JSON columns for typical semi-structured workloads.
V2.0 ships a complete VARIANT function API: variant_type(), variant_keys(), variant_contains(), plus extraction pushdown into scans and Parquet read/write support. The DuckDB team plans to eventually back the regular JSON type with VARIANT infrastructure too. For now, the VARIANT type documentation covers when to switch and how.
New SQL Features Worth Knowing
The new PEG-based SQL parser is more than a technical swap. Extensions can now hook into the grammar directly — meaning third-party DuckDB extensions can introduce new SQL syntax without the core team’s involvement. Error messages are also significantly better, with precise source locations instead of vague offset numbers. A Spark dialect compatibility mode ships immediately: SET dialect_compatibility_mode = 'spark';.
Two other additions stand out. First, DML inside CTEs enables atomic move-and-archive operations previously requiring multiple transactions:
WITH moved AS MATERIALIZED (
DELETE FROM staging RETURNING *
)
INSERT INTO archive SELECT * FROM moved;
Second, NEAREST joins enable approximate similarity search natively: INNER JOIN products t APPROX NEAREST 2 BY SIMILARITY array_cosine_similarity(...). No extension required. For teams doing vector search inside DuckDB, that reduces considerable workaround complexity.
Full trigger support also arrives: BEFORE/AFTER triggers, row-level and statement-level, with transition tables via REFERENCING clauses. Audit tables can now be maintained natively inside DuckDB without application-layer change tracking.
Breaking Changes — Plan Before You Upgrade
V2.0 ships with three categories of breaking changes. The storage format bumps to v2.0.0, which means existing .duckdb files cannot be opened by v2.0 directly — the migration path is EXPORT DATABASE with your old version, then IMPORT DATABASE with v2.0. There is no in-place upgrade. The lambda syntax transition is also completed, with details promised in the official release announcement. And extensions built for v1.x need recompilation against the new stable C API — now versioned in YAML with CI verification, so extension authors have a clear target.
The full breaking change list is being held for the fall release. If you’re running DuckDB in CI pipelines, production ETL, or with third-party extensions, stay close to the community discussion on HN and the official release notes before upgrading.
Key Takeaways
- Recursive CTEs are 40x faster in v2.0 — graph analytics and tree traversals are now viable workloads for DuckDB
- The VARIANT type is production-ready and delivers up to 100x faster queries than JSON columns for semi-structured data
- Triggers, DML-in-CTEs, and NEAREST joins significantly expand DuckDB’s SQL capabilities for application developers
- The storage format change requires a planned migration — no in-place upgrade from v1.x databases
- Preview builds are available at duckdb.org/install/preview — the full release targets fall 2026
