DuckDB promises analytics over data larger than memory on a laptop. Then a query over a folder of Parquet files dies with:
Out of Memory Error: failed to allocate data of size 256.0 MiB (14.8 GiB/15.0 GiB used)
Or the operating system kills the process. DuckDB can process data bigger than RAM: it spills to disk. But a few settings and query patterns decide whether that actually happens.
How DuckDB uses memory
- By default DuckDB may use 80% of physical RAM (
memory_limit). - When sorts, hash joins and aggregations outgrow it, DuckDB spills intermediate data to a temporary directory and carries on, more slowly.
- Some allocations sit outside the buffer manager and some operators can't spill (yet), so the process can end up using more than
memory_limit, which is when the OS steps in.
Fix 1: set the limits explicitly
SET memory_limit = '8GB'; -- leave room for everything else, often 50–60% of RAM
SET temp_directory = '/fast-ssd/duckdb_tmp';
SET max_temp_directory_size = '200GB';
Counter-intuitively, lowering memory_limit often fixes OOM kills: it makes DuckDB spill earlier and leaves headroom for memory it can't track.
With an in-memory database (duckdb.connect() without a file), check that a temp directory is configured, so spilling has somewhere to go. Opening a database file gives DuckDB a default location (<file>.tmp).
Fix 2: turn off insertion-order preservation
By default DuckDB keeps result rows in the same order they were read, which forces some operators to buffer data. If you don't need that (you have your own ORDER BY, or order doesn't matter):