How do you troubleshoot memory issues when DuckDB processes a dataset larger than available RAM?

DuckDB is designed to handle out-of-core processing, datasets larger than available memory, by spilling intermediate data to disk when necessary, but memory pressure can still cause slowdowns or, in extreme cases, failures if not managed appropriately.

1. Check the configured memory limit - DuckDB's memory_limit setting controls how much RAM it's allowed to use before spilling to disk; an unset or overly generous limit on a memory-constrained machine can cause contention with other processes.
2. Check temp directory configuration and available disk space - spilling requires disk space for temporary files; confirm the configured temp directory has enough free space and reasonably fast I/O, since spilling to a slow disk directly impacts performance.
3. Reduce unnecessary materialization - avoid intermediate steps that force large results to be fully materialized in memory when a streaming or more selective approach would suffice, such as filtering earlier in a query pipeline rather than after a large join.
4. Process data in smaller batches where the workload allows it - for genuinely enormous datasets, explicitly partitioning the work (by date range, by key ranges) and processing each partition separately can keep any single query's working set within a manageable footprint.
5. Check for unnecessarily wide intermediate results - selecting more columns than needed through the middle of a complex query pipeline increases memory pressure for every subsequent operator that touches that intermediate result.

Because DuckDB's out-of-core spilling is a genuine safety net rather than a guarantee of good performance, the more effective long-term fix for a workload that consistently strains available memory is usually reducing the actual working set size (via better filtering, partitioning, or column selection) rather than simply relying on spilling to make an oversized query eventually complete.