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

Cooper Okay so DuckDB quietly posted a twenty-one minute blog post today and I have been thinking about it all morning.

Miles Same. I read it twice. The title is 'Work, Thread, Work' which is — honestly, respect.

Cooper That's a good title. So the short version: starting in v2.0, this fall, DuckDB gets async reads for Parquet and C S V. And the reason it matters is that DuckDB is not just a local tool anymore.

Miles Right, and that's the actual crux of the post. The original design assumption was local SSD — low latency, high bandwidth, synchronous I/O is fine. You push down filters, you only read what you need, bottleneck is somewhere else. The I/O path just wasn't the problem.

Cooper And then they shipped DuckLake, they shipped Quack in May — the client-server protocol — and suddenly you have people running DuckDB against S3 on EC2. Which is a completely different world.

Miles Completely different. Because in that setup, a synchronous read BLOCKS the worker thread. The thread is just sitting there waiting for an HTTP response. You've got a sixty-four vCPU machine and the threads are mostly idle, waiting on the network.

Cooper That's such a painful image. Like, all that compute, just… waiting.

Miles So the fix is two separate thread pools. You have REGULAR threads — one per CPU, doing actual work, decoding, joins, all of that. And then you have ASYNC threads, whose whole job is to keep I/O in flight. The default is four times your system thread count, capped at two hundred and fifty-six total. And ASYNC threads are almost entirely blocked waiting on HTTP — CPU utilization is near zero — so you can have a ton of them and it doesn't cost you much.

Miles And then there's the read-ahead queue, which is the clever part. Instead of scheduling a fetch when a worker actually needs the data — which is already too late — any regular worker that comes looking for scan work first tops up the queue. It schedules fetch tasks for jobs ahead of what's currently being decoded. So by the time the worker finishes decoding job N, job N-plus-one is already sitting in memory.

Cooper The part I find genuinely interesting is the memory governance. Prefetching is great until you've got a fast network and a slow decoder, and suddenly you're accumulating gigabytes of prefetched data you haven't touched yet.

Miles And their answer is the read_ahead_depth config. Default is negative one — unlimited but bounded by the same memory manager that's already juggling joins and sorts and window operators. So if something else is eating memory, the queue just shrinks. Gracefully. You don't want a separate memory accounting system for I/O — you want it in the same pool so there's one thing making the tradeoff.

Cooper The benchmark they ran is TPC-H Query Six at SF100 — six hundred million rows in the lineitem table alone, data on S3, compute on an EC2 r7i.16xlarge, same region as the bucket. They explicitly disabled the external file cache so every run goes straight to S3.

Miles That's the right setup. You want the case where synchronous I/O genuinely can't saturate the available bandwidth, and EC2 to S3 same-region is exactly that scenario.

Cooper So this is the thing that's been quietly limiting DuckDB on data lake workloads, and v2.0 just… fixes it by default. You don't configure anything. Async is on.

Miles For Parquet and seekable UTF-8 C S V, yeah. JSON and DuckDB's native format are still coming. The post is pretty upfront about that.

Cooper Honestly that's fine. Parquet is where the data lake lives. And this connects to what we said back in ep 758 about durable execution — the append-only log, the countdown, the fact that a scan task can resume on any worker. It's the same pattern: you make state explicit and portable so the system can survive interruptions and reschedule work freely. DuckDB just did that for I/O.

Miles Yeah. The mechanism is sound, the benchmark setup is honest about what it's testing, and the graceful degradation under memory pressure is the kind of detail that tells you the people who built this have actually run it in production.

Cooper Dev preview builds are out now if you want to try it before the v2.0 release in the fall. DuckDB dot org has the link — I'll drop it in the show notes.

Miles And set read_ahead_depth to zero if something looks weird. Good first debugging step.

Cooper Miles, 'Work, Thread, Work' is a better episode title than anything we've come up with in eight months of doing this. I'm a little annoyed about it.

Miles We should just start stealing database blog post titles. Nobody would notice.

Cooper Nobody is listening anyway. Alright, that's the one — go read the post, it's worth the twenty-one minutes.