DuckDB v2.0: client-server mode, triggers and an in-house parser

The DuckDB team has published a preview of version 2.0 — a major update assembled from more than 10,000 commits since v1.5 shipped in March. The release has been codenamed “Cyanoptera” in honor of the cinnamon whistling duck. The authors explain the bump in the major version number not as ceremony but as a combination of new capabilities and a small number of carefully considered breaking changes.

DuckDB as a server

If last year in the project was called “the year of the lakehouse,” then this release opens “the year of DuckDB as a server.” From day one DuckDB has been an embeddable (in-process) database, but users kept insistently asking for a client-server mode. The answer is the quack extension, which implements a network protocol: any DuckDB process can serve its databases over the network, while another can connect to it with the CONNECT operator and run queries remotely. CONNECT works not only with Quack: a new query-pushdown optimizer sends SQL straight to PostgreSQL and MySQL rather than dragging tables across the network. The authors remind readers that DuckDB was a transactional DBMS with full-fledged MVCC from the start — it's just that in a single-user scenario this was rarely needed. A curious detail: within weeks of the preview, enthusiasts wrote their own clients for the Quack protocol.

What else is inside

The VARIANT type (essentially “JSON on steroids”) becomes a first-class citizen: the data is automatically “shredded,” compresses well and is processed quickly without declaring a schema. The long-awaited triggers have arrived — BEFORE and AFTER, per-row and at the statement level, with transition tables. The SQL dialect has expanded as well: APPROX NEAREST joins for embedding search, DML inside CTEs, nested schemas, a simplified variable syntax ($x) and JSON modification functions.

Performance has grown substantially. On one microbenchmark with a recursive CTE over a graph of a million edges, v2.0 turned out to be roughly 40 times faster than v1.5.4 (0.12 s versus 4.90 s). Key roles were played by asynchronous I/O, which lets work with object stores like S3 scale, a rewritten recursive-CTE engine, and expanded row-group pruning via zone maps and Bloom filters.

Among the infrastructure changes are a new v2.0.0 storage format with ART indexes managed by the buffer (large indexed tables now open instantly) and an in-house extensible parser based on PEG instead of the one inherited from PostgreSQL. Extensions will be able to embed new syntax into the grammar, and the newly introduced dialect compatibility mode can already pretend to be Spark. The ICU library has been thrown out entirely: work with time zones, calendars and collations has been rewritten from scratch, the IANA database has been compressed to about 45 KB, and the operations have become 2–2.6 times faster.

A separate bet has been placed on the extension ecosystem: a stable C API will let you write and build an extension once, as well as register your own signed repositories. This fall a stakeholders' advisory board will be set up under the DuckDB Foundation. Preview builds with all the listed features are available right now.

Source: Hacker News