A Preview of DuckDB v2.0

But above all, it is a feature release, built from over 10,000 commits since we released v1.5 in March. Where last year was the year of the lakehouse, this release kicks off the year of DuckDB as a server. We previewed many of these features in the “State of the Duck” talk at DuckCon #7, if you prefer to watch instead of read. DuckDB is moving rather quickly, and we can only cover a small fraction of the changes here. Condensing all new features down to a shortlist is always a fight over what gets in, and yes, we know that what follows is technically a listicle (Ten Things Coming to DuckDB v2.0, Number Eight Will Shock You). We are not proud of the format, but it works, so here it is, starting with the SQL-level features and working down into the engine. 1. DuckDB as a Server: Quack and CONNECT DuckDB has been an in-process database since day one. But people have asked us – very persistently – for a client/server mode, and we have finally caved. The quack extension implements DuckDB's native protocol for talking to other DuckDBs. It was released as a preview shortly before DuckCon #7, graduates to stable in v2.0, and it is a big part of where DuckDB is headed: any DuckDB process can serve its databases over the network, and any other DuckDB can attach to it and route queries there using the new CONNECT statement. For example: DuckDB server CALL quack_serve( token = 'my_token' ); quack: DuckDB client ATTACH 'quack:server.example.com' AS qk (TOKEN 'my_token'); CONNECT qk; SELECT count(*) FROM events; -- executes on the server, -- results stream back DISCONNECT; CONNECT is the successor to the remote.query($$...$$) workaround we showed when Quack was first revealed – we looked at that syntax and said: no, this cannot be it. And CONNECT is not limited to Quack: it points your session at any remote database that supports it, and the new remote pushdown optimizer (#22914) ships SQL directly to PostgreSQL and MySQL instead of pulling tables over the wire: CONNECT 'postgres://localhost/mydb'; SELECT count(*) FROM orders; -- runs on the PostgreSQL server DISCONNECT; If you have worked with analytical systems in the past, you may assume that DuckDB cannot handle transactional workloads. But DuckDB has been built as a transactional, multi-connection database with full MVCC and transaction isolation since day one. Most users just never needed that in a single-user scenario. It turns out DuckDB handles transactions well: it's fast enough to compete with general-purpose databases like PostgreSQL on quite a few workloads, and the client/server pattern finally lets that machinery shine in multi-tenant, long-running deployments. Running DuckDB long-term also comes with new challenges, which is why v2.0 pushes on better metrics, logs, and observability (see, e.g., the metrics layer rework in #22799) that let you look at a DuckDB instance and see what it is actually doing. People even built standalone clients for the Quack protocol within weeks of the preview. We thought we were extending DuckDB to talk to other DuckDBs; the world said no, no, no, and built their own clients. Who would have thought. 2. VARIANT Becomes a First-Class Citizen The VARIANT type shipped in DuckDB v1.5, and the way to think about it is JSON on steroids. Basically, imagine if JSON were fast. Like JSON, a VARIANT column can store differently-shaped data in every row. Unlike JSON, it is not a text format: DuckDB automatically detects the common structure hidden in your semi-structured data and “shreds” it, so it compresses well in storage and executes fast in queries, all without you ever declaring a schema. This makes VARIANT a natural fit for real-time log ingestion, where streams of JSON-ish records share structure but evolve over time. 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. 3. Triggers 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. 4. SQL Dialect Additions As always, DuckDB's SQL dialect keeps growing.