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

Introduction

The Problem: Slow Storage and Query Performance

Every database query eventually hits a wall. Sometimes that wall is CPU-bound computation—parsing, filtering, or aggregating millions of rows. But more often, the wall is I/O: waiting for data to move from disk or network storage into memory.

Consider a query that scans a 10GB Parquet file stored on Amazon S3. Your CPU can process data at gigabytes per second, but a single network request to S3 might take 50–100 milliseconds just to establish the connection and retrieve the first bytes. If your query reads data synchronously—requesting one chunk, waiting for it to arrive, processing it, then requesting the next—you're leaving most of your CPU idle while each network round-trip completes.

This is the classic I/O bottleneck. And it gets worse with remote storage, network file systems, or even spinning disks with high seek times.

What is Asynchronous I/O?

Asynchronous I/O is a technique that lets a program issue multiple I/O operations without waiting for each one to complete before doing other work. Instead of this synchronous sequence:

Request data chunk #1
Wait for it to arrive
Process chunk #1
Request data chunk #2
Wait for it to arrive
Process chunk #2

You can do this:

Request data chunks #1, #2, #3, and #4 all at once
Process chunk #1 when it arrives
While processing chunk #1, chunks #2, #3, and #4 are already in flight
Process chunk #2 as soon as it lands, and so on

The key insight is overlap: while the CPU processes data, the storage system is simultaneously fetching the next batch. Neither resource sits idle waiting for the other.

Why DuckDB? A Brief Overview

DuckDB is an in-process analytical database management system designed for OLAP workloads—queries that scan large volumes of data, aggregate it, and return relatively small result sets. Unlike client-server databases, DuckDB runs inside your application process, giving it direct access to your CPU and memory.

This architecture makes DuckDB particularly well-suited for asynchronous I/O. Since DuckDB controls its own execution engine, thread pool, and storage access, it can tightly integrate I/O scheduling with query execution. You don't need to configure anything or install a separate service—DuckDB handles the complexity internally.

What This Article Covers

This guide explains how DuckDB's asynchronous I/O works under the hood. We'll examine the architecture, the execution model, when it helps (and when it doesn't), and how to tune it. By the end, you'll understand why DuckDB's slogan could reasonably be "work, thread, work"—the constant dance between issuing I/O, scheduling threads, and processing results.

Understanding DuckDB's I/O Architecture

The FileSystem Abstraction

At the core of DuckDB's storage layer is a FileSystem abstraction—a C++ interface that defines how DuckDB reads and writes files. The interface provides methods like Read, Write, Open, Close, and FileExists.

What makes this abstraction powerful is that it can be implemented by different backends. DuckDB ships with a local file system implementation (using standard POSIX calls on Linux/macOS and Win32 calls on Windows). Extensions provide implementations for remote storage—the httpfs extension, for example, implements the same interface for S3, GCS, and other HTTP-based object stores.

This means the query engine doesn't care whether data lives on a local SSD or in an S3 bucket. It calls the same methods, and the underlying implementation handles the specifics.

Synchronous vs. Asynchronous Interfaces

The FileSystem interface exposes both synchronous and asynchronous methods.

Synchronous methods work as you'd expect: you call Read(), and the call blocks until the data is in your buffer. The current thread waits, doing nothing useful.

Asynchronous methods work differently. You call something like ReadAsync(), which returns immediately. You get back a handle or a callback that you can poll or wait on later. The actual I/O happens in the background, managed by the operating system or by DuckDB's own thread pool.

The interface design is crucial here. DuckDB's query engine is written against these interfaces, not against specific OS calls. This lets the engine use the same code path whether it's reading from a local file (where the OS might use io_uring or overlapped I/O) or from S3 (where the extension manages its own connection pool and HTTP requests).

Thread Pool and Task Scheduling

DuckDB uses a thread pool to manage concurrent execution. By default, the pool size equals the number of CPU cores on your machine. You can override this with the threads setting.

The thread pool follows a standard work-queue model. Tasks are submitted to a queue, and idle threads pick them up. Each task is a small unit of work—typically processing a chunk of data, executing a pipeline stage, or handling an I/O completion.

The Work-Stealing Scheduler Pattern

DuckDB's scheduler uses a work-stealing pattern. Each thread has its own task queue. When a thread finishes its work, it first checks its own queue. If empty, it "steals" work from another thread's queue.

Why does this matter for async I/O? Because I/O completion events are just tasks. When an asynchronous read finishes, DuckDB doesn't spawn a new thread—it pushes a completion task onto the scheduler. Any idle thread can pick it up. This means I/O completions are handled with minimal overhead, and threads that would otherwise be idle can immediately start processing the data that just arrived.

Key Takeaway: DuckDB's thread pool and work-stealing scheduler are the machinery that makes asynchronous I/O practical. Without them, you'd need dedicated I/O threads that sit idle when there's no I/O to process.

How Asynchronous I/O Works in DuckDB

Issuing Asynchronous I/O Requests

When DuckDB executes a table scan—say, reading a Parquet file—it doesn't issue one giant read for the whole file. Instead, it breaks the file into chunks. For Parquet, these chunks are row groups; for CSV, they're blocks of rows; for DuckDB's native format, they're database pages.

For each chunk, DuckDB issues an asynchronous read request. The exact mechanism depends on the underlying file system:

Local files on Linux: DuckDB can use io_uring (a modern Linux async I/O interface) or fall back to a thread-based approach.
Local files on Windows: DuckDB uses overlapped I/O with completion ports.
Remote storage (S3, GCS, etc.): The httpfs extension manages a pool of HTTP connections and issues parallel requests.

The key point is that DuckDB issues multiple requests without waiting for any of them to complete. If there are 64 row groups in a Parquet file, DuckDB might issue read requests for all 64 at once (or a limited number, depending on memory constraints and the io_thread_count setting).

Handling I/O Completion: The 'Work' Phase

Here's where the "work" in "work, thread, work" comes in.

When an I/O operation completes, the data is sitting in a buffer. But that's not the end of the story. The data often needs processing:

Parquet: The data is compressed (e.g., Snappy, Zstd). It needs decompression, then decoding from Parquet's columnar format into DuckDB's internal vector format.
CSV: The text needs to be parsed—splitting on delimiters, converting strings to numbers, handling quotes and escapes.
DuckDB native format: Pages may need to be decompressed and deserialized.

All of this is "work." DuckDB schedules this work as tasks on the thread pool. The completion of an I/O request triggers a new task that performs decompression, parsing, or whatever transformation is needed.

This is the critical design decision: DuckDB separates I/O from processing. The I/O happens asynchronously in the background; the processing happens synchronously on a thread pool thread. This separation allows the two to overlap.

Integration with Vectorized Execution

DuckDB uses a vectorized execution engine. Data is processed in batches of vectors—typically 2048 rows at a time. Each operator (scan, filter, join, aggregate) processes one batch, produces an output batch, and passes it to the next operator.

This architecture pairs beautifully with async I/O. Here's the flow for a scan:

The scan operator issues async read requests for the next N chunks.
It processes the first chunk that arrives (which is now in memory).
While processing chunk #1, the reads for chunks #2 through #N are already in flight.
When chunk #1 is done, the scan operator checks if chunk #2 has arrived. If yes, it processes it. If not, it either waits or works on another pipeline.

In practice, DuckDB's scheduler handles this. The scan operator doesn't block—it submits completion tasks to the thread pool. Other threads can pick up those tasks and continue the pipeline.

Prefetching and Overlap of I/O and Computation

The net effect is prefetching. DuckDB looks ahead in the file and issues reads for data it hasn't processed yet. While the CPU is busy decompressing and processing chunk #1, the storage system is fetching chunks #2, #3, and #4.

This overlap is where the performance gains come from. On high-latency storage (like S3), the round-trip time for a request might be 50ms. If DuckDB processes a chunk in 10ms, then synchronous I/O would give you a 60ms cycle per chunk—50ms waiting, 10ms working. Asynchronous I/O overlaps the 50ms wait with processing, so the effective cycle time approaches 10ms per chunk.

Key Takeaway: Asynchronous I/O in DuckDB is about overlapping I/O wait time with CPU work. The engine issues reads ahead of time, then processes data as it arrives, keeping both the storage system and the CPU busy.

Benefits and Use Cases

Performance Gains on High-Latency Storage

The biggest wins come from storage systems with high latency. Local SSDs have latency in the microseconds to low milliseconds range—async I/O still helps, but the gains are smaller. Network storage (NFS, SMB) and cloud object stores (S3, GCS) have latency in the tens to hundreds of milliseconds. Here, async I/O can dramatically improve throughput.

Community benchmarks and DuckDB Labs tests show 2–3x improvements on high-latency storage for scan-heavy queries. In a DuckDB Labs benchmark, async I/O reduced the time to scan a 10GB Parquet file from S3 by 40% compared to synchronous I/O.

Large Scans and Data Ingestion

The most obvious beneficiary is large table scans. Whether you're reading a 500GB Parquet file or querying a DuckDB database with a 200GB table, async I/O keeps the pipeline fed.

Data ingestion benefits too. When you run COPY or INSERT INTO ... SELECT from a large file, DuckDB can read the source file asynchronously while it's writing to the destination. The same applies when ingesting multiple files—DuckDB can issue reads for several CSV or Parquet files concurrently.

Writing and WAL Flushes

Async I/O isn't just for reads. DuckDB also uses it for writes.

When you write a large result set to a file, DuckDB can flush data to disk asynchronously while the query continues producing more rows. The write happens in the background, and the query engine doesn't block on disk I/O.

The Write-Ahead Log (WAL) also benefits. When DuckDB commits a transaction, it writes to the WAL. With async I/O, these WAL flushes can be batched and overlapped with other work, reducing commit latency.

Real-World Examples

Scanning from S3: DuckDB issues multiple async read requests for different row groups of a Parquet file. The query processes data as it arrives, instead of waiting for the entire file to download.
Joining two large tables: DuckDB prefetches pages from both tables asynchronously. While the CPU is probing the hash table for table A's data, pages for table B are already being fetched.
Multi-file ingestion: A data pipeline loads 50 CSV files. DuckDB issues reads for multiple files concurrently, overlapping parsing with I/O.
Query result export: Writing a 5GB query result to a CSV file uses async writes, so the query engine doesn't stall on disk flushes.

Configuration and Tuning

Default Settings and Automatic Usage

Here's the good news: you don't need to enable anything. DuckDB uses asynchronous I/O automatically when the underlying file system supports it. The query engine and storage layer are built around async I/O from the ground up.

For local files, DuckDB detects the operating system and uses the appropriate async mechanism (io_uring on Linux, overlapped I/O on Windows). For remote storage, the httpfs extension handles async operations natively.

The `io_thread_count` Parameter

One configuration parameter is directly relevant: io_thread_count. This controls the number of threads dedicated to handling I/O operations. The default is 4.

What does this actually do? It limits the number of concurrent I/O requests. If io_thread_count is 4, DuckDB will have at most 4 I/O operations in flight at any given time. This prevents the system from overwhelming the storage device or exhausting memory with too many outstanding requests.

For local SSDs, the default of 4 is usually fine. For high-latency remote storage, you might want to increase it—more in-flight requests mean more overlap. But be careful: each in-flight request consumes memory for its buffer. Too many concurrent requests can blow up memory usage.

Adjusting Thread Pool Size

The main thread pool is controlled by the threads setting, which defaults to the number of CPU cores. This affects how many threads are available for processing tasks—including I/O completion tasks.

If you have a machine with 8 cores but you're mostly doing I/O-bound work (scanning files, not heavy computation), you might benefit from setting threads higher than the core count. This gives DuckDB more threads to handle I/O completions and keep the pipeline moving.

Conversely, if you're on a busy machine with other workloads, you might reduce threads to avoid oversubscription.

When to Tune (and When Not To)

Don't tune if you're on local storage with moderate query sizes. The defaults are well-chosen for most workloads.

Consider tuning if: - You're scanning large files from S3 or other high-latency storage. - You're running many concurrent queries and see I/O bottlenecks. - You have abundant memory and want to increase in-flight I/O for more overlap.

Be careful with: - Increasing io_thread_count too much on local SSDs—you may not see gains and could increase memory pressure. - Setting threads much higher than core count—you might cause CPU thrashing.

Key Takeaway: DuckDB's async I/O works out of the box. Tuning is optional and usually only matters for high-latency storage or extreme workloads.

Limitations and Considerations

When Asynchronous I/O Doesn't Help

Async I/O is not a magic bullet. If your storage is a fast local NVMe drive with sub-millisecond latency, the overlap gains are minimal. Your query might be CPU-bound anyway, in which case async I/O just adds scheduling overhead.

Similarly, if your workload is inherently sequential—you need row group N+1's data before you can process row group N—async I/O won't help. But most analytical queries don't have this constraint, which is why DuckDB is designed the way it is.

Overhead and Complexity

Asynchronous I/O adds complexity. Managing in-flight requests, tracking completions, and scheduling work items all have overhead. For small queries or tiny files, this overhead can exceed the benefits.

DuckDB mitigates this by only using async I/O when it makes sense. Small reads (e.g., reading the file header) are done synchronously. Async is used for bulk data reads where the overlap benefit is clear.

Platform-Specific APIs

The implementation details vary by platform. On Linux, DuckDB can use io_uring—a high-performance async I/O interface. But io_uring requires a recent kernel (5.1+). On older kernels, DuckDB falls back to a thread-based approach where "async" reads are actually done by a pool of threads.

On Windows, DuckDB uses overlapped I/O with I/O completion ports. On macOS, the options are more limited—DuckDB may fall back to a thread-based approach.

The performance characteristics differ across these implementations. io_uring is generally the fastest because it minimizes system call overhead. Thread-based approaches work but have more context-switching overhead.

Common Misconceptions

"Async I/O means queries run faster on any storage." Not necessarily. On low-latency local storage, gains are modest. The biggest wins are on high-latency storage.

"Async I/O uses more threads." Not really. DuckDB uses the same thread pool for processing and I/O completion. The io_thread_count setting controls in-flight requests, not dedicated threads (in the thread-based fallback, it does create I/O threads, but that's an implementation detail).

"I need to write my queries differently." No. Async I/O is transparent. Your SQL is identical whether DuckDB uses sync or async I/O.

The Evolution of Asynchronous I/O in DuckDB

Early Days and Initial Design

DuckDB's initial design (2019–2020) focused on getting the core query engine right—vectorized execution, columnar storage, and the optimizer. Early I/O was mostly synchronous, which was fine for the in-memory analytics use case DuckDB was targeting.

The FileSystem Abstraction and Remote Storage

The introduction of the httpfs extension (2021) was a turning point. Supporting S3 required handling high-latency, high-bandwidth remote storage. The FileSystem abstraction was extended with async interfaces, and the query engine was modified to use them for bulk reads.

The DuckDB Labs blog post "DuckDB Works on S3" (June 2021) demonstrated the potential: scanning a 10GB Parquet file from S3 with a 40% reduction in time compared to synchronous reads.

Recent Optimizations and Future Directions

Async I/O is an active development area. Recent work has focused on:

Better integration with io_uring on Linux for lower overhead.
Improved prefetching algorithms that adapt to query patterns.
Reducing memory overhead for in-flight I/O buffers.
Better handling of mixed workloads (some queries async, others sync).

Future directions likely include more sophisticated I/O scheduling (prioritizing reads that are blocking query progress), better integration with DuckDB's new storage format, and continued improvements to remote storage performance.

Conclusion

Key Takeaways

Async I/O is built into DuckDB's core. It's not an add-on or a feature you enable—it's how the engine works.
The "work, thread, work" model is the key. DuckDB issues I/O requests, schedules completion tasks on its thread pool, and overlaps I/O with computation.
The biggest wins are on high-latency storage. S3, GCS, NFS, and other remote/network storage benefit most from async I/O.
It's transparent. Your SQL doesn't change. DuckDB decides when to use async I/O based on the storage backend and query pattern.
Tuning is optional. The defaults work well. Adjust io_thread_count and threads only when you have specific bottlenecks.
It's an active area of development. DuckDB continues to improve its I/O subsystem, with better platform support and smarter scheduling on the horizon.

FAQ

What is asynchronous I/O in DuckDB?

Asynchronous I/O in DuckDB is a mechanism where the database issues multiple read or write requests without blocking on each one. Instead of waiting for a request to complete before issuing the next, DuckDB issues several requests concurrently, then processes data as it arrives. This overlaps I/O wait time with CPU work, improving throughput on high-latency storage.

How does DuckDB benefit from asynchronous I/O?

The main benefit is performance on high-latency storage. When reading from S3, NFS, or other remote storage, the round-trip time per request is high. Async I/O lets DuckDB issue many requests at once, keeping the storage system busy while the CPU processes data that's already arrived. Benchmarks show 2–3x improvements on such storage.

Is asynchronous I/O enabled by default in DuckDB?

Yes. DuckDB uses asynchronous I/O automatically when the underlying file system supports it. For local files, this depends on the OS (using io_uring on Linux, overlapped I/O on Windows). For remote storage via extensions like httpfs, async I/O is built into the extension. There's no global toggle you need to flip.

Can I configure the number of I/O threads in DuckDB?

Yes. The io_thread_count setting controls the maximum number of concurrent I/O requests. The default is 4. You can increase it for high-latency storage to allow more in-flight requests, but be mindful of memory usage—each in-flight request holds a buffer.

Does asynchronous I/O work with all file formats?

Yes. DuckDB's async I/O is implemented at the file system level, not the format level. Whether you're reading Parquet, CSV, JSON, or DuckDB's native format, the underlying reads can be asynchronous. The scan operators for each format are designed to issue async reads and process completions.

What are the limitations of asynchronous I/O in DuckDB?

Async I/O doesn't help when storage latency is already very low (e.g., fast local NVMe). It also doesn't help for inherently sequential workloads where each chunk depends on the previous one. There's also overhead—managing in-flight requests and scheduling completions takes CPU time, which can dominate for tiny queries.

How does DuckDB handle I/O completion?

When an async I/O request completes, DuckDB schedules a completion task on its thread pool. Any idle thread picks up the task and performs the "work": decompression, parsing, decoding, or whatever transformation is needed. This is handled by the work-stealing scheduler, which distributes tasks across threads efficiently.

Is asynchronous I/O used in DuckDB's WAL (Write-Ahead Log)?

Yes. DuckDB uses async I/O for WAL flushes. This allows transactions to commit faster because the database doesn't block on disk writes—the flush happens in the background while other work continues. This is particularly beneficial under high write throughput.

Can I disable asynchronous I/O in DuckDB?

There's no direct setting to disable async I/O entirely. However, you can set io_thread_count to 1, which effectively limits the system to one in-flight request at a time. This doesn't force synchronous I/O, but it removes the concurrency benefit. For most users, there's no reason to disable it.

Where can I learn more about DuckDB's asynchronous I/O implementation?

The best resources are the DuckDB source code (particularly src/include/duckdb/common/file_system.hpp and the storage/scan operators), the official documentation, and DuckDB Labs blog posts. The GitHub repository has active discussions on I/O-related issues, and the DuckDB Discord/community forums are good places to ask questions.

Ready to speed up your analytical queries? Dive into DuckDB's documentation and start leveraging asynchronous I/O today. For more insights, explore our other guides on DuckDB performance optimization.

In This Article