Why DuckDB 2.0 is faster

Queries on external data and deep hierarchical datasets are now significantly faster without query changes.

DuckDB 2.0, set to release this fall with an alpha version now available, introduces significant performance improvements, particularly in its handling of external data and recursive queries. A key feature is asynchronous I/O, which drastically speeds up queries on remote storage like AWS S3. This is achieved by introducing a separate thread pool dedicated to downloading data ahead of the main query processing, allowing network and CPU tasks to run concurrently. For example, a query processing a 2.2 GB Parquet file on S3 that took 18.8 seconds in DuckDB 1.5.5 now completes in 7.7 seconds in the 2.0 alpha, with similar improvements seen across various file sizes and formats. Additionally, DuckDB 2.0 features a re-architected recursive Common Table Expression (CTE) engine. This enhancement drastically improves performance for queries involving deep hierarchical or cyclical data, such as analyzing git histories or organizational structures. The new engine reads the data once and builds efficient lookups, avoiding repeated full table scans that plagued the previous version. This change transforms tasks that previously required specialized graph databases into standard SQL queries, with a 20,000-commit git history analysis dropping from up to 16 seconds to just 0.10 seconds.

The introduction of asynchronous I/O in DuckDB 2.0 fundamentally alters how the database interacts with remote storage. By decoupling data fetching from query execution, it enables parallel processing of network-bound downloads and CPU-intensive computations. This improvement is particularly impactful for cloud-based data warehousing and analytics pipelines, reducing latency and accelerating time-to-insight for large datasets stored on object storage like S3. The default configuration, which enables this feature automatically, means users can expect performance gains without modifying their existing SQL queries.

The revamped recursive CTE engine in DuckDB 2.0 addresses a major bottleneck for analytical workloads involving graph-like structures. Previously, iterative traversals of parent-child relationships led to performance degradation due to repeated table scans. The new implementation's efficiency, demonstrated by its dramatic speedup on deep hierarchies, positions DuckDB as a more capable tool for tasks like data lineage tracking, supply chain analysis, and even social network analysis directly within the database. This advancement potentially reduces the need to offload such operations to dedicated graph databases.