Every database upgrade I've run has failed in one of 2 ways. The first kind crashes at 3am, pages somebody, and is fixed by breakfast. The second kind runs clean through every smoke test and returns a slightly different number for 3 weeks until finance asks why the dashboard moved. I'll take the crash every time.
DuckDB 2.0.0 LTS ("Cinnamon Teal") shipped on October 21, 2026, and it can fail both ways. It adds a client-server mode, async I/O for object storage, a VARIANT type, triggers, and a new parser. It also writes a file format older versions can't open, and it changes some SQL behavior in ways that give new answers without raising an error. If DuckDB sits anywhere in your pipelines, you have an upgrade decision to make this month.
My position is that the 2.0 additions are nice, but the upgrade plan is where the engineering happens. The concepts behind that plan are compatibility, rollback, write coordination, and verifying results. Those are also what a good data engineering interview should test, whatever tool is on the whiteboard.
19 days left on DuckDB 1.4 LTS
Start with the dates. The official 2.0 announcement says 1.4 LTS "is still supported until November 17, 2026." Today is October 29, so that leaves 19 days. 2.0 is supported "until at least October 2027."
Read the fine print on "LTS." DuckDB's release cycle docs say "Currently LTS releases will receive 1 year of support," and feature releases land about every 6 months. Enterprise people hear "LTS" and picture 5 years of security patches. Here you get 1 year.
Then there's the odd part. 1.5 was never labeled LTS, but its support got extended to March 2027, "effectively also turning it into an LTS release." So the non-LTS line outlives the previous LTS by roughly 4 months. If you're on 1.4 and need more time, moving to 1.5 is the cheap option, because it keeps the old file format and the old parser and stays supported into spring.
Also watch when deprecations land. Single-arrow lambdas and osx_amd64 builds are both removed in 2.1. If the 6-month cadence holds, 2.1 shows up around April 2027. That's my math, not a DuckDB date. Either way, "2.0 is supported through 2027" and "my 2.0 SQL keeps working through 2027" are separate promises, and only the first was made.
The DuckDB team's own advice is to test 2.0.0 on a shadow system first, and for mission-critical workloads, to wait for the first patch release, expected in November. Take that advice.
An LTS label tells you when the vendor stops fixing bugs. It doesn't tell you when your rollback plan stops working; that date is the first time a 2.0 process writes a file.
SQL changes in DuckDB 2.0 that won't throw an error
Deal with the file format first, because it decides whether you can roll back. According to the 2.0 migration guide, 2.0 writes storage version 69 by default. Point a 1.5.6 binary at that file and you get:
"Trying to read a database file with version number 69, but we can only read versions between 64 and 68."
2.0 can read and write your existing files, and it doesn't upgrade them automatically, so those files stay readable by older versions. New files are a different story. Once a 2.0 process creates one, the only way back to 1.x is to export and re-import. Reinstalling the old binary won't help.
If you need a way back, pin the storage version from day 1:
, Create/attach a database file that 1.5.x readers can still open
ATTACH 'shared.duckdb' AS shared (STORAGE_VERSION 'v1.5.6');
, Or set it for the session before creating new files
SET storage_compatibility_version = 'v1.5.6';
On the CLI, the equivalent is duckdb -storage-version v1.5.6 shared.duckdb.
Every reader and writer of a shared file has to move together. That includes the analyst's notebook pinned to 1.4, the cron job nobody owns, and the Lambda someone deployed in 2025 and forgot about. The Python client now needs Python 3.11 or newer, so some of those consumers are blocked before they ever open the file. Build a dependency map by tracing every process that opens every .duckdb file. You'll find at least one you didn't know about; I always do.
Next is the PEG parser, which replaces the PostgreSQL-derived parser. Usually you won't notice. The migration guide lists a few behavior changes that will change results quietly:
, 2.0 default: ~ and !~ do partial regex matching
SELECT 'senior data engineer' ~ 'engineer';
, Restore the pre-2.0 full-match semantics if your filters depend on it
SET regex_match_operator_semantics = 'full';
, Operator precedence changed: this returns 12 in 2.0
SELECT 2 * 3!;
X'…' and B'…' literals now produce BLOB and BITSTRING instead of VARCHAR. A regex filter in a dedup step that used to match whole strings can start matching substrings, and nothing crashes while your row counts drift.
Some changes are loud, which is the kind you want:
-
//and%by zero now raise an error instead of returning NULL. - A
MERGE INTOthat touches the same target row more than once now errors. - ICU moved out of core, so timezones, calendars, and collations need the
icuextension. If your containers can't download extensions at runtime, check this before cutover. - S3 URL parameters are disabled by default.
- Building from source needs a C++17 compiler now.
Then lambdas. x -> x + 1 is disabled by default. You can turn it back on:
SET lambda_syntax = 'enable_single_arrow';
Both the flag and the syntax disappear in 2.1, so this flag is a delay, not a fix. Use it to get through the upgrade, then grep your codebase and rewrite them. If the new parser breaks something you can't fix in time, the legacy_parser extension with SET enable_legacy_parser = true; gets you the old parser, but treat that as an emergency brake you pull once.
What works is running your production queries on 1.x and 2.0 against the same data and diffing the outputs. "It ran" isn't a pass. Compare row counts, checksums, and the aggregates finance looks at, and trust that diff over the release notes.
Client-server mode turns DuckDB into a shared database
For most of its life, DuckDB's pitch was what the "Why DuckDB" page still says: "there is no DBMS server software to install, update and maintain." 2.0 ships a polished version of Quack, the client-server protocol that first appeared in 1.5.3 in May 2026. Now there can be a server.
The Quack design post describes it as built "straight on the venerable HTTP," using request-response with a single round trip per query once connected. The defaults are reasonable: it binds to localhost only, listens on port 9494, and generates a random token at startup. 2.0 also adds CONNECT and DISCONNECT statements, and for PostgreSQL targets the remote pushdown optimizer sends SQL to the server instead of pulling whole tables over the wire.
The benchmark numbers in that post look great: 60M TPC-H lineitem rows moved in around 4.94 s with Quack, 17.40 s with Arrow Flight SQL via GizmoSQL, and 158.37 s with PostgreSQL. Read the methodology, though. DuckDB ran it on its own setup: m8g.2xlarge instances in the same availability zone with a 0.28 ms ping. Your cross-region setup will look different.
The security defaults are thin. In DuckDB's own words, the default authorization function "just says 'yes' to everything." The token is the entire access control story out of the box. The team's advice is a TLS-terminating reverse proxy, and they say, "We do not recommend opening up a DuckDB Quack endpoint directly to the Internet." Somebody will do it anyway, so put the proxy in place before the first remote query.
Concurrency is better than it was and still not fully solved. The same post says multiple processes can write in parallel, and it also admits "we hit a current limitation of DuckDB itself in concurrent insertions per second into the same table," with PostgreSQL scaling better on that workload. One more catch: attaching a remote catalog is all-or-nothing, so one table definition that can't bind fails the whole attachment.
Async I/O is the other big change. Pedro Holanda's pre-release design post shows TPC-H Q6 at SF100 against S3 dropping from 8.230 s on 1.5.5 to 2.844 s on a 2.0 dev build, with network throughput rising from around 5 Gbit/s to near the 25 Gbit/s limit. An 80.89 GB remote CSV went from 877.563 s to 45.264 s. On a local SSD the gain was roughly 1.5×. Most of the headline speedup comes from the network, so if your data sits on local disk, adjust your expectations.
Check these before you put "3× faster" in a planning doc:
- Row group count matters. In a single-file test, 1 row group took 25.26 s, 10 took 3.69 s, and 306 took 2.11 s. Check how your Parquet writer sizes row groups.
- The memory limit is soft. With an 8 GB limit, the benchmark peaked at 11.5 GB RSS, because read-ahead memory isn't tracked by DuckDB's memory manager.
- Async covers Parquet, uncompressed seekable UTF-8 CSV, and the native format. JSON and compressed CSV still use synchronous I/O. All the benchmarks came from dev builds on a single EC2 configuration.
What a data engineer should explain about DuckDB in an interview
If DuckDB comes up in your next loop, and it will more often now, the interviewer doesn't care whether you've memorized the CONNECT syntax. They want to know whether you can reason about tradeoffs. Here's what I'd want a candidate to explain.
Start with when DuckDB beats a warehouse. The usual citation is Jordan Tigani's "Big Data is Dead", which argues 90% of queries processed under 100 MB among BigQuery customers spending over $1,000 a year. Know the caveats too: Tigani says his charts are "hand-drawn based on memory," the data can't be shared, and the post lives on a DuckDB company's blog. I agree with the direction of it; most companies have medium data and big egos. A strong candidate cites the claim and its limits in the same breath.
2.0 changes that question. Before, the conversation was about data size. Now it's about write coordination. Multi-process writes to the native format go through Quack, or you can use DuckLake with a PostgreSQL catalog (DuckLake 1.1 shipped alongside 2.0 and is intended for production use). So the questions become who writes, how many writers there are, how they coordinate, and how you upgrade all of them together. That last one is a pipeline architecture question, and it has nothing to do with load balancers.
VARIANT now "shreds" common structure, but only in row groups with at least 30,000 rows, so small micro-batches get no benefit. Object keys come back sorted, which breaks any pipeline that depends on key order or byte-level equality. DuckDB published no performance numbers for it, so a skeptical answer gets credit here.
Triggers versus idempotent upserts is my favorite question because it catches people. Triggers are new in 2.0, and the obvious use case is audit logging. On a table with UPDATE or DELETE triggers, though, INSERT ... ON CONFLICT, INSERT OR REPLACE, and MERGE INTO all fail. If your pipeline's idempotency depends on upserts, adding audit triggers means redesigning it. A candidate who can explain that tradeoff on the spot has done this work before.
Agent mode is a good test of whether someone measures the right thing. The 2.0 CLI has an agent mode for tools like Claude Code. DuckDB's own agent mode post reports output tokens dropping 59% across 132 TPC-H runs, while total cost and runtime barely moved, because the saved tokens were about 0.5% of total input. Agent mode actually took more turns (237 vs. 224). If you can tell that story, you've shown you know the difference between optimizing a metric and optimizing an outcome.
Puzzle screens won't ask about any of this. The SQL round still happens, and it'll still be window functions, dedup, and gaps-and-islands; for sql coding questions, try DataDriven, the practice set we run for exactly that kind of problem. Spend the rest of your prep time being able to talk through a rollback plan out loud, because that's the job.
(I'll admit I still can't say "Cinnamon Teal" without sounding like I'm ordering a latte.)
2.1 is maybe 6 months out. File compatibility, silent semantic drift, writers stepping on each other, and the one consumer nobody inventoried will come up again then, the same as in every engine upgrade.
What's the oldest DuckDB version still pinned somewhere in your stack, and does anyone actually know who owns it?
Top comments (1)
The silent changes list is the part I'd print out before upgrading. The regex one worries me most: a filter like col ~ 'ACME' used to match only the full string, and now it also matches 'ACME Holdings', so row counts quietly go up instead of anything failing. Grepping the SQL for ~ and !~ before the upgrade is cheap, and the output diff you suggest only catches it if those exact queries are in the test set. Division by zero is the opposite case: queries that used to return NULL now fail loudly, so wrapping denominators in NULLIF(x, 0) ahead of time avoids a 2 a.m. surprise. I'd also run that 1.x vs 2.0 diff against a copy of the database file rather than the shared one, since a single 2.0 writer without the storage version pin is enough to cost you the rollback path for every 1.5 reader.