Out-of-Core Processing

Out-of-core processing executes work on data that does not fit in available RAM by keeping only the needed portion in memory and accessing the rest from storage. A large input already on disk does not necessarily require another disk copy. Additional writes arise when intermediate state must spill, or when the job writes its output.

Streaming operators and blocking operators

Some work can be done a chunk at a time: filtering rows, projecting columns, or maintaining a fixed-size running total. A scan that sums a large file can keep a bounded input buffer and accumulator without spilling the input. This assumes the execution path consumes results incrementally; collecting all output rows in memory would add a separate memory requirement.

Other operations must see enough of the input before they can emit anything — grouping, joining, sorting, and windowed aggregation. These are the blocking operators, and they are where memory goes. Out-of-core support means the engine handles them by spilling intermediate state to disk rather than failing: DuckDB creates a temporary directory for this, configurable through a setting, and documents larger-than-memory support for exactly this set of operators.

Where it still breaks

• Several blocking operators in one query. The documentation warns that when multiple blocking operators appear together, an out-of-memory exception is still possible.
• Aggregates that cannot spill. Functions that accumulate whole collections — DuckDB names list() and string_agg(), and notes that PIVOT uses list() internally — cannot be offloaded to disk. Aggregates that sort internally carry the same risk.
• Disk space and speed become the limit. Spilling turns a memory problem into an I/O problem: the temporary directory needs room, and the query runs at the speed of that device. Fast local storage and a network volume are not interchangeable here.
• The performance cliff is abrupt. A query that fits in memory and one that spills by a little can differ by a large factor, which makes capacity planning less linear than it looks.

Why it changes an architecture decision

Input size alone does not decide whether a cluster is needed. Determine the working set after any applicable partition pruning and column projection, which may remove many bytes or very few. Then check whether intermediate state spills and whether the complete workload meets its deadline on one machine.

Checking is cheap: run the real query on one machine, watch memory and temporary-directory usage, and look at where time goes. The result is a number that either supports distribution or removes the need for it. That decision is worked through in Do You Actually Need a Cluster?, and the single-node tooling in single-node analytics with DuckDB, Polars, and Arrow.

References

• DuckDB Documentation, How to Tune Workloads