Larger-than-memory processing

Overview

Many query operations — joins, sorts, grouped aggregations, window functions — need to materialize intermediate state that can be much larger than the input data, and larger still than the memory available on the machine running the query. An engine without larger-than-memory support simply fails with an out-of-memory error once that state doesn't fit; an out-of-core engine instead spills the excess to disk and continues, at the cost of extra I/O.

Why It's Hard

The difficulty isn't just "write some data to disk" — it's doing so without a catastrophic performance cliff, and without needing to know ahead of time how much memory a query will require. Good out-of-core designs, like radix partitioning for hash-based operators, degrade gradually as more data spills, rather than collapsing in performance the moment memory runs out.

DuckDB's Larger-Than-Memory Support

DuckDB has out-of-core support for the operators most likely to blow up memory: grouping, joining, sorting, and windowing. When a hash table built for a GROUP BY or a join doesn't fit in memory, DuckDB partitions and spills data to a temporary directory on disk rather than throwing an out-of-memory error, using radix partitioning to keep the performance degradation gradual instead of a sudden cliff. This works both for on-disk databases and for DuckDB running fully in-memory, since spilling always writes to a temp directory on disk regardless of where the source database lives.

There are limits: a handful of aggregate functions, like list() and string_agg(), don't support spilling and must hold their full state in memory, and queries with multiple simultaneous blocking operators can still exhaust memory in some cases. You can configure spilling behavior with SET memory_limit and SET temp_directory.

Copy code
SET memory_limit = '4GB';
SET temp_directory = '/fast/local/disk/tmp';