DuckDB 2.0 Performance: Why Async I/O, Recursive CTEs and VARIANT Matter

DuckDB has always been interesting for one particular reason: it brings serious analytical database capabilities into an unusually lightweight package.

You can run it directly on a laptop, query Parquet files without loading them into a traditional database first, work with cloud object storage, and process large datasets using SQL. That combination has made DuckDB increasingly useful for local analytics, data engineering, ELT pipelines, lakehouse workloads, and exploratory data work.

DuckDB 2.0 is taking that idea further.

The upcoming release is not simply about making the existing SQL engine execute the same workloads a little faster. Several changes affect the way DuckDB moves data, processes recursive queries, stores semi-structured values, and interacts with database-style features.

The official DuckDB preview describes 2.0 as a major release with a new SQL parser, a new default storage format, a reworked C API, triggers, VARIANT, asynchronous I/O, and other changes.

For people who mostly write SQL, however, the interesting question is simpler:

What will actually become faster or easier when DuckDB 2.0 arrives?

Three areas stand out:

1. Remote reads can benefit from asynchronous I/O.
2. Deep recursive queries can avoid repeatedly scanning the same data.
3. Semi-structured data can be stored with the new VARIANT type instead of remaining entirely as JSON text.

There are also several smaller improvements worth knowing about, including triggers, nested schemas, DML inside CTEs, SQL compatibility options, remote-file cache spilling, and CLI improvements.

This article focuses on those changes from a practical data-user perspective rather than looking at DuckDB from the perspective of someone developing the database engine itself.

What Is DuckDB 2.0?

DuckDB 2.0 is the upcoming major version of DuckDB, currently being developed toward a Fall 2026 release. The project has described it as more than a routine version bump: the release includes architectural and SQL-level changes alongside new functionality.

That distinction matters.

A normal performance release might improve an operator, optimize a query plan, or reduce memory usage. DuckDB 2.0 includes improvements that change where bottlenecks occur.

For example, consider a query reading Parquet data from Amazon S3.

The SQL might already be efficient. The Parquet file might already be columnar. DuckDB might already avoid reading columns that are not required.

Yet the query can still spend a large amount of time waiting for remote data.

That is not primarily a SQL problem.

It is an I/O scheduling problem.

Similarly, a recursive query can be logically simple but become expensive when each recursion step repeatedly scans a large table.

Again, rewriting the SQL is not necessarily the main answer.

The execution engine itself has to handle recursion more intelligently.

Semi-structured data creates another problem.

JSON is convenient because its schema can change from record to record. But that flexibility comes with a cost. If a query repeatedly asks for the same fields, parsing the same JSON text again and again is wasteful.

DuckDB 2.0’s VARIANT work addresses that problem by allowing frequently occurring structured parts of semi-structured values to be stored more efficiently.

So the bigger theme of DuckDB 2.0 is not simply:

“Everything is faster.”

It is closer to:

DuckDB is becoming better at understanding the shape of the workload and avoiding unnecessary work.

1. Async I/O Makes Remote Parquet Reads More Efficient

One of the most practical changes is asynchronous I/O for external data.

This matters particularly when DuckDB is reading Parquet or other files stored on object storage such as Amazon S3.

Imagine a Parquet file stored several network hops away from your laptop.

DuckDB needs to:

1. request data,
2. wait for the network,
3. receive the bytes,
4. decode the data,
5. process the rows,
6. request the next piece of data.

The problem is that network latency and CPU processing are two different types of work.

If the same worker is responsible for both, part of the machine can remain idle while the worker waits for remote data.

That is where asynchronous I/O changes the execution pattern.

According to MotherDuck’s benchmark using DuckDB 1.5.5 versus a DuckDB 2.0 alpha build, a query against a 2.2 GB Stack Overflow votes Parquet file stored on S3 took 18.8 seconds with 1.5.5 and 7.7 seconds with the tested 2.0 alpha. The benchmark was performed from an M5 laptop over a home internet connection, so these numbers should be treated as a workload-specific experiment rather than a universal benchmark.

That distinction is important.

You should not assume every S3 query will suddenly become exactly 2.44 times faster.

The improvement depends on factors such as:

• network latency,
• file layout,
• number of row groups,
• compression,
• CPU speed,
• number of columns read,
• object-store performance,
• query shape,
• and the amount of work performed per downloaded block.

Still, the architectural idea is straightforward.

How the Old Execution Pattern Creates Idle Time

Suppose a Parquet file is divided into hundreds or thousands of row groups.

A worker needs a row group.

It requests the bytes.

Then it waits.

Once the bytes arrive, it decodes them and performs the computation.

Only after that can it request another piece.

That produces a pattern like this:

Download → Wait → Decode → Download → Wait → Decode

The CPU cannot decode data it has not received.

At the same time, the network cannot download another row group if the worker is busy doing CPU-intensive decoding and the execution design does not maintain sufficient read-ahead.

In a remote analytical workload, that creates an opportunity for overlap.

DuckDB 2.0 introduces a separate downloading path so that data can be fetched ahead of the execution workers. The workers can then process already-available row groups while additional row groups are being downloaded.

Conceptually, the pipeline becomes:

Download A ────────┐
Download B ────────┤
Download C ────────┤
                   ↓
               Decode A
               Decode B
               Decode C

Network and CPU work can therefore overlap.

That is the central idea behind the improvement.

2. The read_ahead_depth Setting

The amount of read-ahead is controlled by read_ahead_depth.

In the tested 2.0 alpha, the default value is -1, which enables automatic sizing based on the available execution configuration. Setting it to 0 restores behavior closer to the older approach.

For most users, the important point is that asynchronous reading is intended to work automatically.

You do not normally need to rewrite a query simply to benefit from it.

For example:

SELECT VoteTypeId, COUNT(*) AS vote_count
FROM read_parquet(
    's3://us-prd-motherduck-open-datasets/stackoverflow/parquet/2023-05/votes.parquet'
)
GROUP BY ALL
ORDER BY 1;

The SQL itself is ordinary analytical SQL.

The improvement comes from how the engine retrieves the underlying data.

This is a useful pattern in database optimization:

You do not always need a smarter query when the engine can make the existing query execute more efficiently.

3. What the S3 Tests Show

MotherDuck tested several workloads while comparing DuckDB 1.5.5 with a 2.0 alpha build.

The published measurements included:

These are measurements from a specific test environment, not guaranteed performance figures for every machine or cloud region.

The pattern is nevertheless interesting.

Large remote files benefited substantially.

Tiny files did not.

And that tells us something important about data engineering.

4. Why Tiny Files Are Still a Problem

Suppose you have thousands of Parquet files that are only around 1 MB each.

It may be tempting to assume that asynchronous I/O will solve the performance problem.

It does not.

A tiny-file workload has a different bottleneck.

For each object, the system may need to perform operations such as:

• opening the remote object,
• obtaining metadata,
• reading the footer,
• determining the relevant data,
• requesting the actual data,
• and closing or releasing the request.

When a dataset contains thousands of tiny objects, the cost of all those individual operations can dominate the query.

Read-ahead cannot magically eliminate every per-file round trip.

This is why file layout remains an important part of analytical system design.

A fast execution engine does not make poor physical organization irrelevant.

For object storage, the question is not only:

“How fast can my database execute SQL?”

It is also:

“How many remote objects am I forcing the database to interact with?”

That distinction becomes increasingly important as data volumes grow.

5. Async I/O Is Especially Relevant to Cloud Analytics

Traditional local database workloads often have relatively predictable storage latency.

Cloud object storage is different.

Your query might be executed locally while the data is physically stored somewhere else.

That means network latency becomes part of the database execution plan, even if you never explicitly think about networking.

DuckDB’s asynchronous I/O approach is therefore particularly interesting for:

• S3-based data lakes,
• Parquet datasets,
• cloud-hosted analytical pipelines,
• remote CSV processing,
• local analysis over cloud data,
• and hybrid data workflows.

The key benefit is that DuckDB can spend less time waiting for the next piece of data.

6. Recursive CTEs Get More Interesting in DuckDB 2.0

The second major area is recursive SQL.

Recursive common table expressions, or recursive CTEs, are useful when the data contains relationships such as:

• parent and child,
• manager and employee,
• commit and parent commit,
• folder and subfolder,
• product and component,
• message and reply,
• dependency and dependent,
• lineage and upstream object.

A recursive CTE allows SQL to repeatedly follow those relationships.

Consider this simple employee hierarchy:

If Ana is the starting point, the query can discover Ben and Cléa.

Then it can discover Dev, Eli and Fay.

After that, it can find Gus and Hal.

The hierarchy is being traversed one level at a time.

A basic DuckDB query can look like:

WITH RECURSIVE team(person) AS (
    SELECT 'Ana'

    UNION

    SELECT e.employee
    FROM team t
    JOIN employees e
      ON e.manager = t.person
)
SELECT COUNT(*)
FROM team;

This is an example of recursive SQL as a graph traversal mechanism.

7. Why Deep Recursion Can Become Expensive

The problem is not recursion itself.

The problem appears when the same large dataset has to be examined repeatedly.

Imagine a table containing one million parent-child relationships.

Now imagine a recursive query requiring 1,000 levels.

If every iteration scans the same million-row table, the amount of work can become enormous.

The conceptual pattern becomes:

Round 1 → scan table
Round 2 → scan table
Round 3 → scan table
...
Round 1000 → scan table

That is a poor match for a deep hierarchy.

A better approach is to read the relationship data once and build a structure that allows the recursive process to locate children efficiently.

The DuckDB 2.0 work changes the execution strategy for recursive CTEs in this direction. The MotherDuck benchmark describes a design where the underlying table can be read once and lookups can then be performed against the discovered frontier rather than repeatedly scanning the entire table.

The practical consequence is significant for deep parent-child workloads.

8. Git History Is a Good Example

Git repositories provide an intuitive example.

Every commit has one or more parent commits.

You can represent a simple commit history using something like:

commit_id | parent_id

To find the complete ancestry of a commit, you start from HEAD and repeatedly follow its parent.

For a small repository, this is trivial.

For a repository with tens of thousands of commits, the depth of the recursive traversal can become much larger.

A query can look like:

WITH RECURSIVE ancestors(id) AS (
    SELECT MAX(id)
    FROM commits

    UNION

    SELECT p.parent_id
    FROM ancestors a
    JOIN commit_parents p
      ON p.commit_id = a.id
)
SELECT COUNT(*)
FROM ancestors;

In the benchmark published by MotherDuck, a generated 20,000-commit repository was used for an ancestry walk. The reported timings ranged from 1.8 to 16 seconds on DuckDB 1.5.5 across runs, while the tested DuckDB 2.0 alpha measured about 0.10 seconds in that experiment.

Again, this is a benchmark, not a universal performance promise.

Hardware, query structure, data distribution and version details all matter.

But the magnitude of the difference illustrates why execution strategy matters for deep recursion.

9. Recursive SQL Is Not Only for Organizational Charts

When developers hear “recursive CTE,” they often think about employee hierarchies.

That is only one application.

The same relational pattern appears in many datasets.

File systems

folder → child folder

Product structures

product → component

Message systems

message → reply

Data lineage

table → upstream dependency

Version histories

commit → parent commit

Organizational structures

manager → employee

In all these cases, the physical data can be remarkably simple.

Often, it is just a relationship table with two IDs.

The challenge is traversal depth.

That is exactly where improved recursive execution becomes valuable.

10. Where USING KEY Fits

There is another DuckDB feature worth understanding here: USING KEY.

It is not identical to the recursive CTE engine improvements discussed above, but it becomes particularly useful when a recursive computation needs to maintain state associated with keys.

DuckDB documentation explains that ordinary recursive CTEs accumulate rows in a union table, while USING KEY allows the recursive process to update an existing row when the same key appears again. The behavior is similar to maintaining a dictionary keyed by a specific column.

For example:

WITH RECURSIVE
    paths(node, distance)
    USING KEY (node)
AS (
    SELECT 1, 0

    UNION

    SELECT next_node, distance + 1
    FROM paths
    JOIN edges
      ON edges.source = paths.node
)
SELECT *
FROM paths;

The exact algorithm you need will determine whether USING KEY is appropriate.

The important conceptual difference is this:

A normal recursive CTE can keep accumulating intermediate rows.

A keyed recursive CTE can update the payload associated with an existing key.

That can reduce the size of the accumulated state and can be useful for algorithms involving values such as:

• distance,
• cost,
• depth,
• best-known value,
• or other evolving state.

DuckDB has documented USING KEY as a production feature since the 1.3 generation, so it should not be treated as something introduced for the first time by 2.0.

11. VARIANT: A Different Way to Store Semi-Structured Data

The third major area is semi-structured data.

JSON is everywhere.

APIs return JSON.

Applications write JSON logs.

Event pipelines frequently produce JSON payloads.

Analytics systems ingest JSON because it allows new fields to appear without requiring a complete schema migration.

That flexibility is useful.

But storing everything as raw JSON text has a downside.

Suppose every event contains:

{
  "type": "purchase",
  "user": {
    "id": 42,
    "country": "FR",
    "premium": true
  },
  "props": {
    "amount": 12.5,
    "currency": "EUR",
    "items": 2
  },
  "tags": ["a", "b"]
}

If this is stored as a string, the database has to interpret the textual representation when you query nested fields.

Now imagine five million events.

If every query asks for:

type
user.country
props.amount

you do not really have an unknown schema.

You have a mostly predictable schema with a few flexible fields.

That is the problem VARIANT is designed to address.

12. What VARIANT Changes

DuckDB 2.0 introduces VARIANT as a first-class type, with storage behavior that can separate frequently occurring structured fields from less predictable portions of the data.

The important idea is sometimes described as shredding.

Imagine millions of event records.

Across those records:

type             → almost always present
user.id          → numeric
user.country     → text
props.amount     → numeric

Those values have predictable structure and predictable types.

Instead of treating the entire object as one giant text blob, DuckDB can organize frequently occurring fields into separate internal storage structures.

The remaining irregular information can stay in a more flexible representation.

That gives you something between two traditional choices:

Fully raw JSON

Flexible, but potentially expensive to parse repeatedly.

Fully normalized relational schema

Very fast for known fields, but less flexible when event shapes change.

VARIANT sits between those approaches.

13. Why Consistent Data Types Matter

There is an important limitation.

A field is easier to optimize when it has a consistent meaning and type.

For example:

"latency_ms": 231

is predictable.

But if some records contain:

"latency_ms": 231

while others contain:

"latency_ms": "231ms"

the database now has to deal with multiple representations.

The same issue occurs when a field is:

number in some records
string in other records
object in another record

The more irregular the data becomes, the less opportunity there is for highly structured storage.

This leads to a practical rule:

Flexible does not mean structureless.

Even when using VARIANT, consistent field names and value types can improve storage and query behavior.

14. VARIANT Versus JSON Text

MotherDuck published an experiment using five million events and compared three approaches:

1. JSON stored as text.
2. VARIANT.
3. A fully typed relational representation.

The reported disk sizes were:

The benchmark also tested three queries involving filtering, aggregation, and list lookup.

For a filter involving structured fields, the published measurements were:

These numbers are particularly useful because they show both the strength and the current limitation of the approach.

VARIANT performed close to fully typed columns for the tested structured-field queries.

But the list query behaved differently.

That means you should not interpret VARIANT as:

“JSON is now always as fast as normal columns.”

That would be an incorrect generalization.

The more accurate conclusion is:

Frequently accessed, consistently typed fields can benefit significantly from VARIANT’s storage strategy, while some nested or list-oriented operations may still have additional costs.

15. Why VARIANT Can Save Storage

Raw JSON repeats a lot of textual information.

For example, if five million records contain:

"type": "purchase"

the text representation repeatedly stores:

• the field name,
• quotation marks,
• punctuation,
• and the value representation.

A typed storage system can represent that information much more compactly.

DuckDB’s VARIANT implementation can also take advantage of repeated structure.

That explains why the benchmark showed 85 MB for the tested VARIANT representation compared with 224 MB for the JSON string representation.

It is worth emphasizing that this is a workload-specific measurement.

Compression ratios will vary depending on:

• field repetition,
• data types,
• cardinality,
• nesting,
• optional fields,
• list contents,
• and the overall distribution of the events.

Still, it demonstrates why semi-structured data does not necessarily have to remain an expensive text blob.

16. Should You Stop Using Normal Columns?

No.

VARIANT does not make relational modeling obsolete.

If you know that a field is important, frequently queried, and structurally stable, a normal typed column can still be the clearest choice.

For example:

ALTER TABLE ev
ADD COLUMN event_type VARCHAR;

UPDATE ev
SET event_type = payload.type::VARCHAR;

A useful architecture can therefore look like this:

Stable, high-value fields
        ↓
Normal typed columns

Flexible or rarely used fields
        ↓
VARIANT

That gives you the advantages of explicit schema where you need it while retaining flexibility for less predictable attributes.

This is particularly useful for event data.

17. Structured Logs Are a Natural VARIANT Workload

Consider an application generating logs such as:

{
  "level": "INFO",
  "service": "payments",
  "trace_id": "abc123",
  "latency_ms": 231,
  "extra": {
    "region": "eu-west-1",
    "experiment": "checkout-v2"
  }
}

The standard fields may be present in almost every event:

level
service
trace_id
latency_ms

The extra object may vary substantially.

This is exactly the kind of situation where a flexible outer structure can make sense.

The stable fields provide predictable analytical access.

The irregular fields remain available without requiring a giant relational schema containing hundreds of rarely used columns.

18. But Do Not Ignore Data Modeling

The biggest lesson from these improvements is not a specific DuckDB setting.

It is that physical data design still matters.

A faster engine cannot fully compensate for:

• thousands of tiny remote files,
• inconsistent field types,
• unnecessarily repeated JSON parsing,
• badly structured hierarchies,
• or queries that repeatedly process data they do not need.

DuckDB 2.0 improves the engine.

It does not remove the need for sensible data engineering.

This is actually good news.

It means the same optimization principles remain useful:

• organize data intelligently,
• use columnar formats,
• keep related data together,
• avoid unnecessary file fragmentation,
• use stable types,
• and let the query engine exploit that structure.

19. Triggers Arrive as Another Important Database Feature

Performance is not the only interesting part of DuckDB 2.0.

Triggers are another notable addition.

A trigger allows database-side SQL to run when a table is modified.

For example, suppose you maintain a product price table.

Whenever a price changes, you might want to write the old and new values into a history table.

Without database-side triggers, every application that updates prices needs to remember to create the corresponding history record.

That creates a consistency problem.

One application may write the audit row.

Another may forget.

A third may implement it differently.

A trigger can put that behavior closer to the data itself.

A simplified example is:

CREATE TRIGGER price_history
AFTER UPDATE ON prices
REFERENCING
    OLD TABLE AS before_rows
    NEW TABLE AS after_rows
FOR EACH STATEMENT
INSERT INTO price_history (
    id,
    old_price,
    new_price
)
SELECT
    a.id,
    b.price,
    a.price
FROM before_rows b
JOIN after_rows a USING (id);

The example uses transition tables representing the rows before and after the update.

This opens the door to database-side patterns such as:

• audit history,
• change tracking,
• derived-table maintenance,
• data-quality enforcement,
• and other event-driven SQL behavior.

The precise feature set should be checked against the DuckDB version you deploy, especially while 2.0 is still in development. DuckDB lists triggers among the major features of the upcoming release.

20. Nested Schemas Improve Organization

Another small but useful addition is support for nested schemas.

For example:

CREATE SCHEMA finance.reports;

This makes it easier to organize related tables under a logical namespace.

For larger analytical projects, organization matters.

You might have:

finance
    reports
    staging
    raw

marketing
    reports
    staging

product
    analytics
    staging

Logical organization becomes increasingly useful as a DuckDB database grows beyond a few tables.

21. DML Inside CTEs Enables Atomic Data Movement

Another interesting SQL improvement is the ability to combine data modification and CTE workflows.

A common data-engineering operation looks like this:

staging table
      ↓
move rows
      ↓
production table

You may want the rows removed from staging and inserted into another table as part of one SQL operation.

The new functionality enables patterns such as:

WITH moved AS MATERIALIZED (
    DELETE FROM staging
    RETURNING *
)
INSERT INTO prod
SELECT *
FROM moved;

This is useful for workflows where a set of records needs to be transferred rather than simply copied.

For ETL and ELT pipelines, reducing multiple application-level steps into a database-side operation can simplify orchestration.

22. Spark SQL Compatibility

DuckDB 2.0 also includes a compatibility option intended to help with some Spark SQL migration scenarios.

The documented setting is:

SET dialect_compatibility_mode = 'spark';

This does not mean that DuckDB and Spark become identical systems.

They have different execution models, deployment models and feature sets.

Instead, compatibility modes can reduce friction for certain SQL workloads that originated in another dialect.

For organizations moving analytical logic between systems, small syntax differences can otherwise create unnecessary migration work.

23. Remote File Cache Spill Can Help Repeated Reads

Remote data has another problem beyond network latency.

Suppose DuckDB reads a large Parquet file from S3.

The system may cache recently accessed remote blocks.

But if memory is limited, older blocks can be evicted.

Without another persistent cache layer, the same data may need to be downloaded again later.

DuckDB 2.0 introduces an option called:

SET external_file_cache_spill = true;

The idea is to allow evicted remote file blocks to spill to temporary local storage rather than immediately disappearing.

In MotherDuck’s reported experiment, under a 300 MB memory limit, the second read of an 854 MB S3 Parquet file dropped from 23.9 seconds to 0.35 seconds when cache spilling was enabled.

That is another example of an optimization that depends heavily on workload characteristics.

If your queries repeatedly touch the same remote blocks, persistent local cache behavior can be much more important than it would be for one-time scans.

24. The DuckDB CLI Is Getting More Useful

DuckDB is frequently used directly from the command line.

That makes improvements to the CLI more than cosmetic.

The 2.0-era changes include functionality such as:

• SQL formatting,
• queryable shell history,
• .about,
• and .manual.

For example, automatic SQL formatting can make interactive work easier:

.auto_format on

And shell history can be queried through SQL-oriented interfaces.

This fits DuckDB’s overall philosophy nicely.

The database is not only something embedded inside a large application.

It is also a tool developers can open directly on their laptops and use as an analytical shell.

25. Better Timestamp Handling for JSON

Timestamp handling is another area where small details can have major consequences.

Dates and timestamps frequently arrive from APIs as ISO-8601 strings.

A timestamp may include an explicit offset:

2026-09-18T14:30:00+06:00

If that offset is ignored, the resulting timestamp can represent a different instant than the original value.

The DuckDB 2.0 preview notes an improvement where read_json recognizes ISO-8601 timestamps containing an offset as TIMESTAMPTZ rather than silently discarding the offset.

For distributed analytics, preserving timezone information correctly is not a minor detail.

It can affect:

• event ordering,
• reporting windows,
• daily aggregations,
• log analysis,
• and cross-region datasets.

26. Connection-Scoped Secrets

DuckDB can work with remote storage and external services, which means credentials become part of the data-access story.

DuckDB 2.0 also adds the ability to scope a secret to a particular connection:

CREATE SECRET s
IN CONNECTION (...);

This is useful when different connections need different credential contexts.

It also reinforces an important principle:

Credentials should be scoped as narrowly as practical.

That is especially relevant when the same process interacts with multiple storage systems or environments.

27. What These Features Mean for Data Engineers

Looking at all these features together reveals a common direction.

DuckDB 2.0 is improving several layers of the analytical workflow:

Remote storage
      ↓
Async I/O

Recursive computation
      ↓
Better traversal execution

Semi-structured data
      ↓
VARIANT + structured storage

Database behavior
      ↓
Triggers + DML improvements

Developer workflow
      ↓
Better CLI + compatibility features

These are different features, but they attack different forms of wasted work.

Async I/O reduces waiting.

Recursive execution reduces repeated scanning.

VARIANT reduces repeated parsing and inefficient storage of structured fields.

Cache spilling reduces repeated downloads.

Triggers reduce repeated application-side bookkeeping.

That is a useful way to think about the release.

28. When You Should Expect the Biggest Gains

Not every DuckDB workload will suddenly become dramatically faster.

The biggest improvements are likely to appear where the workload matches the problem the new feature addresses.

Remote Parquet workloads

If your data lives on S3 and queries are network-bound, asynchronous I/O can matter significantly.

Deep recursive workloads

If you traverse thousands of parent-child relationships, the recursive CTE changes can be much more relevant than they would be for a shallow hierarchy.

Semi-structured event data

If you have millions of JSON-like records with recurring fields, VARIANT can provide a useful middle ground between raw JSON and a fully normalized schema.

Repeated remote reads

If queries repeatedly touch the same remote data while memory is constrained, cache spilling may become valuable.

On the other hand, some workloads may see little change.

For example, a dataset containing thousands of tiny remote Parquet files still has a file-fragmentation problem.

A simple local query over already-resident data may also have little reason to benefit from remote asynchronous I/O.

Understanding the bottleneck is therefore more useful than simply upgrading the version and expecting a fixed percentage improvement.

29. DuckDB 2.0 Does Not Eliminate the Need for Good Modeling

One of the easiest mistakes in database discussions is to treat engine improvements as a replacement for data modeling.

They are not.

Consider two datasets.

Dataset A

20 large Parquet files

Dataset B

20,000 tiny Parquet files

Even with a highly optimized engine, Dataset B creates more object-management overhead.

Likewise:

Dataset A

latency_ms = numeric everywhere

Dataset B

latency_ms = numeric sometimes
latency_ms = string sometimes
latency_ms = object sometimes

The first dataset provides much more predictable structure.

The same principle applies to recursive relationships.

A clean:

parent_id
child_id

model is easier to traverse than a deeply nested collection of arbitrary objects.

Performance improvements amplify good data organization.

They do not replace it.

30. A Practical DuckDB 2.0 Strategy

If you are preparing an existing project for DuckDB 2.0, there are several practical questions worth asking.

Question 1: Where is my data?

If most of it is local, async S3 I/O may not matter much.

If the majority lives in S3, it becomes much more interesting.

Question 2: How large are my files?

If you have thousands of tiny objects, fixing the file layout may be more important than upgrading the database.

Question 3: Do I have recursive workloads?

Look for queries involving:

WITH RECURSIVE

Then determine how many iterations they normally perform.

Question 4: Do I store JSON as text?

If so, identify the fields queried most frequently.

Those fields are candidates for structured storage or promotion to normal columns.

Question 5: Are JSON value types consistent?

If the same field changes between numbers, strings and objects, the benefits of structured storage may be reduced.

Question 6: Do I repeatedly reread remote files?

If yes, investigate external file caching and cache spilling.

Question 7: Do applications repeatedly implement database-side logic?

If every application needs to manually maintain history or audit records, triggers may simplify the architecture.

31. How to Think About VARIANT in a Real Application

Imagine an analytics platform receiving application events.

Each event contains:

event_type
user_id
timestamp
country
properties
metadata

Some properties are predictable:

purchase amount
currency
product_id

Others are not.

One event might contain:

{
  "coupon": "WELCOME10"
}

Another might contain:

{
  "experiment": "checkout-redesign"
}

Another might contain:

{
  "recommendation": {
    "model": "v4",
    "score": 0.83
  }
}

Creating dozens of relational columns for every possible property may be unnecessary.

But storing the entire event as raw JSON text also makes repeated analytical access less efficient.

A hybrid approach is often more sensible:

event_type       → typed column
timestamp        → TIMESTAMP/TIMESTAMPTZ
user_id          → typed column
country          → typed column
properties       → VARIANT
metadata         → VARIANT

That gives you a stable analytical core while preserving flexibility.

32. The Bigger Picture: DuckDB Is Moving Beyond “Just a Local Analytics Tool”

DuckDB started with a strong identity as an embedded analytical database.

That identity remains important.

But the upcoming 2.0 release shows the project expanding into a broader database platform.

The official DuckDB preview specifically highlights DuckDB as a server, alongside the new parser, storage format, VARIANT, asynchronous I/O, triggers and other changes.

That does not mean DuckDB suddenly becomes a traditional client-server database in every respect.

Instead, the project is adding capabilities that make it useful in more contexts.

This is important for data teams because the boundary between:

local analytics

and:

production data infrastructure

continues to become less rigid.

33. What Developers Should Test Before Upgrading

Because DuckDB 2.0 is a major release, production teams should test their actual workloads rather than relying entirely on benchmark headlines.

Build a representative test suite containing:

• your largest Parquet files,
• your most common analytical queries,
• recursive queries,
• JSON-heavy workloads,
• S3 access,
• memory-constrained queries,
• and any SQL relying on version-sensitive behavior.

Then measure:

execution time
memory usage
storage size
remote bytes read
CPU utilization
cache behavior

For remote data, run the test from an environment that resembles production.

A laptop connected to home internet is useful for experimentation, but it is not automatically representative of an AWS workload running inside the same region as S3.

This is exactly why the published MotherDuck numbers should be treated as examples rather than universal guarantees.

34. Benchmark Your Data Shape, Not Someone Else’s

This may be the most important lesson from the entire release.

A benchmark can tell you what happened under a specific set of conditions.

It cannot tell you exactly what your system will do.

Suppose another team reports:

3x faster

That result might have come from:

• a large Parquet file,
• high network latency,
• a particular number of row groups,
• a specific CPU,
• a particular S3 region,
• and a query reading one column.

Your workload may look completely different.

Maybe you have:

many small files
local SSD
simple aggregations
no recursion
fully typed columns

In that environment, your improvement may be very different.

The right approach is therefore:

Understand the optimization, then benchmark the bottleneck it targets.

35. Final Takeaway

DuckDB 2.0 is interesting not because every query suddenly becomes faster, but because several important bottlenecks are being attacked at the engine level.

For remote analytical workloads, asynchronous I/O allows downloading and computation to overlap more effectively.

For deep hierarchical data, improvements to recursive CTE execution can reduce the need to repeatedly scan the same relationship table.

For semi-structured data, VARIANT provides a more efficient alternative to keeping everything as raw JSON text, especially when the same fields and data types occur consistently.

Around those headline features are several practical additions:

• triggers,
• nested schemas,
• DML inside CTE workflows,
• Spark SQL compatibility options,
• external-file cache spilling,
• CLI improvements,
• improved timestamp handling,
• and connection-scoped secrets.

But there is a broader lesson behind all of them.

Performance still begins with data shape.

A large Parquet file is generally easier to process efficiently than thousands of tiny objects.

A consistent event schema is easier to optimize than one where the same field changes type constantly.

A compact parent-child relationship is easier to traverse than an unnecessarily complicated hierarchy.

And a frequently queried attribute may deserve a real typed column even if the rest of the event remains semi-structured.

DuckDB 2.0 gives the engine more opportunities to take advantage of those properties.

So rather than asking only:

“How much faster is DuckDB 2.0?”

a more useful question is:

“Which bottleneck in my workload does DuckDB 2.0 remove?”

If your workload spends substantial time waiting on remote storage, async I/O is worth testing.

If you traverse very deep hierarchies, recursive CTE improvements deserve attention.

If you ingest large volumes of structured-but-flexible events, VARIANT is worth evaluating.

And if none of those are bottlenecks in your workload, the upgrade may still bring useful SQL, storage, database and tooling improvements—but you should measure before making performance claims.

DuckDB’s own documentation and preview material make clear that version 2.0 is a substantial release rather than a simple incremental update.

The most useful way to approach it is therefore not to memorize benchmark numbers.

Understand the mechanism.

Then test it against your own data.

That is where the real performance story begins.