DuckDB 2.0 alpha delivers faster S3 reads and recursive queries

The DuckDB team has released the alpha version of DuckDB 2.0, marking a significant step forward in performance for analytical workloads. This update focuses on three major architectural improvements: asynchronous input/output for cloud storage, a rewritten engine for recursive common table expressions, and a new native data type for handling semi-structured data. These changes aim to reduce query times and storage costs for engineers building data pipelines on laptops or in the cloud.

What happened

The most immediate performance gain comes from how DuckDB 2.0 handles data stored remotely, such as on Amazon S3. In previous versions, worker threads had to alternate between downloading data chunks and processing them, leaving CPU resources idle during network waits. The new version introduces a separate pool of threads dedicated solely to downloading data ahead of time. This allows the system to keep both the network connection and the CPU busy simultaneously, significantly reducing the total time required to read large Parquet or CSV files from object storage.

Beyond raw speed, the release addresses complex query patterns involving hierarchical data. Recursive common table expressions (CTEs), often used to traverse organizational charts or dependency trees, have been completely re-engineered. Previously, each level of recursion required a full scan of the source table, making deep hierarchies prohibitively slow. The new engine reads the table once and builds an internal lookup structure, allowing it to jump directly to relevant rows for each subsequent level of recursion. This change transforms operations that previously took seconds or minutes into sub-second tasks.

The third major addition is the VARIANT data type, designed to handle JSON and other semi-structured formats more efficiently. Instead of storing JSON as a plain text string, DuckDB 2.0 analyzes the data during write operations. It identifies fields that appear consistently with the same data type across most rows and stores them as separate, optimized columns. Only the irregular or rare fields remain in a binary blob. This process, known as shredding, reduces storage footprint and accelerates queries that filter or aggregate on those consistent fields.

Key details

• Async I/O improves S3 read speeds by two to three times without requiring any changes to existing SQL queries.
• The read_ahead_depth setting controls how many row groups are fetched in advance, defaulting to automatic sizing based on thread count.
• Recursive CTE performance improved dramatically in tests, dropping from up to 16 seconds to 0.10 seconds for a 20,000-commit git history walk.
• The new VARIANT type reduces storage requirements by approximately 2.7 times compared to storing JSON as text strings.
• Queries filtering on shredded VARIANT fields run about six times faster than parsing JSON text and approach the speed of native typed columns.
• List operations within VARIANT data currently perform slower than field access, suggesting users should avoid casting lists in hot query paths.

Background

To understand these improvements, it helps to know how analytical databases typically process data. Traditional engines often treat network latency and CPU processing as sequential steps. When reading from cloud storage, the database must wait for data to arrive before it can begin computation. Asynchronous I/O decouples these tasks, allowing the database to prefetch data while simultaneously processing previously fetched chunks. This is similar to how a video player buffers upcoming scenes while you watch the current one.

Recursive CTEs are a SQL feature used to query hierarchical structures, such as employee reporting lines or file directory trees. In older implementations, the database would repeatedly scan the entire table to find the next level of the hierarchy. If a tree was ten levels deep, the table was scanned ten times. The new approach builds an index-like structure in memory after the first scan, allowing the database to locate child records instantly without re-reading the source data. This shifts the cost from being proportional to the depth of the tree multiplied by the table size, to being proportional only to the number of rows actually visited.

Why it matters

For teams running their own data infrastructure, these changes reduce the need for specialized tools. Previously, deep hierarchical queries might have required exporting data to a graph database or writing custom application code to manage traversal. With DuckDB 2.0, these operations become feasible within standard SQL, simplifying the technology stack. Similarly, the improved S3 performance means that keeping data in low-cost object storage is more viable for interactive analysis, reducing the pressure to move everything into expensive high-performance block storage.

The VARIANT type offers a practical middle ground for handling messy real-world data. Engineers often struggle with the choice between rigid schemas, which break when data formats change, and flexible JSON strings, which are slow to query. By automatically optimizing the consistent parts of JSON data while preserving flexibility for the rest, DuckDB 2.0 allows teams to ingest semi-structured logs or event data without sacrificing query performance. This reduces the engineering effort required to clean and model data before it can be analyzed.

However, these benefits come with modeling responsibilities. The performance of VARIANT depends heavily on data consistency. If a field sometimes contains a number and sometimes a string, it cannot be shredded effectively and will remain in the slower binary storage. Teams must ensure that their data pipelines produce consistent types for key fields to realize the performance gains. Additionally, while async I/O helps with large files, it does not solve the inherent latency of accessing thousands of tiny files, reinforcing the best practice of consolidating small datasets into larger partitions.

What you can do

• Test the alpha release on your local machine using the provided installation script to benchmark your specific workloads.
• Review your S3-based queries to ensure you are reading large Parquet files rather than many small ones to maximize async I/O benefits.
• Identify recursive queries in your codebase, such as org chart traversals or bill-of-materials expansions, and re-run them to measure the new performance baseline.
• Audit your JSON-heavy tables for fields with consistent data types and consider converting them to the VARIANT type to reduce storage and improve filter speed.
• Avoid casting VARIANT lists to arrays in performance-critical queries until future updates optimize this operation.
• Monitor the read_ahead_depth setting if you experience memory pressure, though the default automatic configuration should suit most environments.