Diagnosing DuckDB Memory Spills and External Sort Diagnostics

Recognizable Condition

You run a DuckDB query that previously completed in seconds, and it either fails with an 'Out of Memory' error or crawls to a halt while disk I/O spikes in the temp directory. In DuckDB, this pattern almost always indicates that a sort, hash join, or window operation has spilled to external storage because the per-query memory budget was exceeded. The query may still succeed, but runtime increases dramatically, sometimes by orders of magnitude, as DuckDB swaps rows to files in the configured temp_directory.

Cause/Diagnostic Table

Ordered Checks

1. Run PRAGMA memory_limit; to see the current per-connection budget. No special permissions are required; run this from the DuckDB CLI or any connection context.

2. Run PRAGMA memory_fraction; to check the fraction of memory_limit each connection may use. In DuckDB 1.0+ the default is 0.8; in older versions it was 1.0.

3. Run EXPLAIN ANALYZE SELECT * FROM t ORDER BY x; and look for 'External Merge Sort' or 'External Hash Join' in the plan nodes. This confirms an operator spilled externally rather than fitting in memory.

4. Monitor the temp directory size growth during query execution: du -sh /tmp/duckdb_temp (run in a separate shell or before/after the query). Spill volume correlates with the cost of external operators.

5. Profile per-operator memory peaks with PRAGMA enable_profiling='json'; run the query, then examine the JSON profile for peak memory per node. Disable profiling in production after diagnosis as it adds overhead.

Fixes Tied to Findings

1. Increase memory_limit: SET memory_limit='8GB'; If the working set is known, set the limit at least 2x the expected peak to provide headroom. Caution: setting above physical RAM + swap may trigger the OS OOM killer.

2. Reduce memory_fraction on shared hosts: SET memory_fraction=0.5; This caps each connection to half of memory_limit, protecting concurrent workloads. Note: memory_fraction applies to memory_limit, not physical RAM directly.

3. Rewrite the query to avoid massive single sorts: add LIMIT, partition window functions, or pre-aggregate before sorting. For example, replace SELECT * FROM large_table ORDER BY col; with SELECT * FROM large_table ORDER BY col LIMIT 1000; when downstream consumers only need a subset.

4. Prefer streaming-friendly operators when memory allows: HASH JOIN can be more efficient than NESTED LOOP for large builds, but verify with EXPLAIN ANALYZE first. If memory is constrained, consider breaking the join into smaller partitions.

5. Set temp_directory to fast storage: SET temp_directory='/tmp/nvme'; If spilling is unavoidable, placing temp files on local NVMe or SSD avoids the catastrophic latency of network-mounted directories (NFS, S3FUSE).

Escalation Criteria

• Spills persist after memory_limit >= 2x estimated working set
• Temp I/O saturates disk (>80% utilization) causing latency spikes
• Concurrent workloads starve each other despite fraction tuning
• Query plan shows unavoidable external operators (e.g., DISTINCT on 10B rows) -> consider materialized views or external sort utilities outside DuckDB

Version-Sensitive Behavior

DuckDB 0.10+ introduced improved per-thread memory accounting; 1.0+ set the default memory_fraction to 0.8 (was 1.0). Older versions may not report 'External' in EXPLAIN ANALYZE for all operator types. The temp_directory pragma moved from PRAGMA to SET in 0.9. Check your version with PRAGMA version; before tuning.

Practical Verification

1. Create a controlled test table: CREATE TABLE t AS SELECT range * 1.0 AS x FROM range(1e8);

2. Set a tight limit: SET memory_limit='500MB';

3. Run and explain: EXPLAIN ANALYZE SELECT * FROM t ORDER BY x; Verify that the plan shows 'External Merge Sort' and that temp files are created.

4. Monitor memory: SELECT duckdb_memory(); before and after the query to confirm peak usage respects the limit.

5. Measure spill volume: du -sh /tmp/duckdb_temp during query execution; compare against the 500MB limit.

6. Compare runtime with memory_limit='2GB' vs '500MB' to quantify the spill penalty.

Limitations & Checks

Setting memory_limit above physical RAM + swap can cause the OS OOM killer to terminate the DuckDB process. memory_fraction applies to memory_limit, not physical RAM; on containers with cgroups, set memory_limit explicitly to the cgroup limit. External sorting uses temp files in temp_directory; network-mounted temp (NFS/S3) degrades performance catastrophically. PRAGMA enable_profiling adds overhead; disable in production after diagnosis. Concurrent transactions each reserve memory; connection pooling without query queuing can exceed memory_limit silently.