Asynchronous I/O in DuckDB: Work, Thread, Work

TL;DR: Beginning with the v2.0 release (slated for Autumn 2026), DuckDB will implement asynchronous read capabilities for CSV and Parquet files. This optimization is designed to maximize bandwidth utilization, particularly in cloud environments like EC2 paired with S3, where synchronous I/O often leaves performance on the table.

---

The Evolution of Data Access in DuckDB

In any database, the speed of query operators is irrelevant if the system cannot ingest data fast enough. For a long time, DuckDB avoided this bottleneck through aggressive brute-force reading data pruning. By utilizing projections and filter push-downs, the engine ensured it only touched the necessary bytes.

This strategy was highly effective because DuckDB was primarily designed as a local engine. Its "sweet spot" was querying data directly from a machine's local SSD. In that context:

• Data was split into manageable partitions (e.g., Parquet row groups or CSV buffers).
• Latency was negligible.
• Bandwidth was high.

Consequently, developers focused on optimizing joins, aggregations, and subqueries, while the synchronous data access path remained a secondary concern.

The Shift to the Cloud

The landscape has changed. DuckDB is now frequently used to query massive remote datasets—essentially acting as a "DuckLake." With the introduction of the Quack protocol in May, DuckDB can now operate as a server.

This shift means the assumption of "local SSD speeds" is often true false. In a typical cloud architecture:

1. Data resides in blob storage (e.g., Amazon S3).
2. Processing happens on a compute instance (e.g., EC2) in the same region.

In this scenario, network latency becomes a primary bottleneck. If the system cannot maintain enough concurrent requests, worker threads spend the majority of their time idling—waiting for HTTP responses—rather than processing data.

---

Synchronous vs. Asynchronous Execution

Consider a basic query targeting a remote Parquet file:

SELECT * FROM read_parquet('s3://bucket/file.parquet');

In a Parquet scan, the work is divided into row-group-based jobs, which further break down into fetch tasks that trigger byte-range requests.

The Synchronous Bottleneck

In a synchronous model, a single worker thread handles everything. It requests data and then stops until that data arrives.

The Asynchronous Solution

Asynchronous I/O allows a thread to initiate a read without being forced to wait for the result. This decouples the request from the processing.

In this model, ASYNC threads keep multiple fetch tasks "in flight" while the REGULAR worker thread focuses on decoding and aggregating. During the initial "warm-up" phase, the scan task parks, freeing the worker to handle other pipeline tasks until the first batch of data is ready.

---

Architectural Implementation

DuckDB utilizes two distinct thread pools to manage this workload:

The ASYNC pool is significantly larger because these threads are often dormant. The total number of asynchronous workers is capped at 256.

Key Strategies for Efficiency

To maximize throughput, DuckDB employs two critical mechanisms:

1. Read-Ahead Queue: Instead of requesting data "just in time," the system schedules fetch tasks for future jobs. While the worker is decoding Job $N$, the async threads are already pulling Job $N+1$ and $N+2$.
2. Asynchronous Memory Governance: Read-ahead increases throughput but consumes memory. If the network is faster than the decoder, prefetched data could trigger an Out-of-Memory (OOM) error. DuckDB implements a governance system to balance prefetching with available RAM.

---

Current Status and Roadmap

Asynchronous I/O is currently being rolled out. Here is the current support matrix:

• Parquet Files
• Uncompressed, seekable UTF-8 CSVs
• DuckDB Native Format (Coming soon)
• JSON (Coming soon)

How to access it:

• Now: Use the v2.0.0-dev preview builds.
• Fall 2026: Enabled by default in the official v2.0 release.