DuckDB Files, Memory Limits, and Analytical Workflows
Use DuckDB's file readers to query analytical data directly, but make the input set and query contract reproducible. Select the needed columns and rows, inspect the plan, and understand which operators require substantial intermediate state. A configured memory limit and support for spilling do not prove that every query will complete within a desired process-memory boundary.
Start with a known input manifest and expected result. Then investigate performance using representative data, query shape, memory settings and temporary-storage conditions. The example below verifies a tiny Parquet path, not a larger-than-memory workload or a general sizing recommendation.
TL;DR
- Query Parquet, CSV, and JSON files directly with DuckDB, but make the input set reproducible with an explicit file list or manifest.
- Select only the columns and rows you need, and inspect the plan.
- Joins, sorts, and aggregations can need large intermediate state even when the result is tiny.
- A configured memory limit and spilling support don't guarantee every query completes within a process-memory budget.
- Test representative data, query shapes, and temporary-storage conditions before committing to sizes.
Quick Example
The example was written against DuckDB 2.0. It creates three synthetic rows, writes its own temporary Parquet file, queries a filtered aggregate, and checks the result. It reads no external dataset and changes no shared database.
The result contains team a with four and team b with seven. The row with amount three is excluded by the filter. Setting a 64 MB limit was exercised, but this tiny input did not demonstrate spilling, a process-RSS ceiling, an out-of-memory boundary, or analytical throughput. Temporary files belonged only to the example's newly created directory.
Core Concepts
A file query still has an input contract
DuckDB documents reading and writing Parquet and supports projection and filter pushdown. Projection can limit columns read, while filter-based skipping depends on the file and available metadata. Do not infer an exact amount of IO saved without inspecting the actual data and plan. DuckDB Parquet
Record which files the query includes and which schema assumptions apply. A broad glob can pick up a newly arrived file or an unintended output, changing the result without changing SQL. Use a manifest or another explicit input-selection policy when a run must be reproducible.
Distinguish schema drift from data changes. A renamed column, changed type or altered meaning can affect correctness even if the reader accepts the file. Validate the contract that downstream analysis depends on rather than equating a successful scan with correct interpretation.
Intermediate state can dominate memory
DuckDB's workload guide describes larger-than-memory processing through temporary-disk spilling and identifies grouping, joins, sorting and windows as memory-intensive blocking operations. It also documents limitations, including combinations and aggregate states that can still cause out-of-memory failures. Workload tuning
Investigate the shape and cardinality of intermediate results. A small final output can follow a very large join or grouping state. Reducing displayed rows at the end does not necessarily bound the work performed earlier. Verify the plan before treating a final LIMIT as a memory-control strategy.
This matrix is a proposed investigation plan. Only the stated three-row round trip and result check were executed.
Configuration should be observable
DuckDB documents querying configuration with current_setting or duckdb_settings. Record the relevant values used by the actual connection rather than assuming a setting from another session applies. Also record version and execution environment when interpreting a plan or measurement. Configuration reference
Treat memory and thread settings as experimental inputs. A setting that helps one workload may slow another or leave a different memory-intensive operator unresolved. Change a suspected cause deliberately and retain before-and-after evidence instead of adjusting several limits until a single run happens to finish.
Best Practices
Build a small correctness fixture before tuning the large workload. Include nulls, boundaries, duplicate keys and schema cases relevant to the analysis. Compare expected totals and selected records so a faster query does not silently change the business question.
Separate raw inputs, intermediate outputs and final artifacts. Avoid writing results into a directory globbed as raw input on the next run. Store the query, input identity, DuckDB version and important settings beside the result metadata. A filename alone does not describe how an analytical artifact was produced.
Inspect EXPLAIN when investigating the plan, and recognize that EXPLAIN ANALYZE executes and profiles the query. Use an authorized representative environment for expensive work. The article's fixture runs a small query but does not present a measured plan-performance comparison.
Plan temporary storage for the intended workload. Verify capacity, access and failure behavior, and consider the sensitivity of intermediate data. Spilling can help the engine process larger work, but a full or inaccessible temporary volume can introduce another failure boundary. No disk-pressure or spill test occurred here.
Use representative scale and distributions when measuring. A three-row file cannot establish the behavior of many files, skewed joins, high-cardinality groups or concurrent analytical sessions. Keep each measured claim tied to its dataset, version, hardware and query settings.
Reverse a “just raise the memory limit” response when the query creates unintended multiplicative joins or reads the wrong files. Correcting the data or plan can be more important than giving erroneous work more resources. Conversely, legitimate large intermediate state may require a different query strategy or resource arrangement after correctness is established.
Common Mistakes
Treating file inputs as immutable
Assuming a small result means small intermediate state
Reporting a memory setting as usage
Assuming spilling covers every query
FAQ
Does a memory limit guarantee a query fits in memory?
No. A configured limit and spilling support don't guarantee that every query completes within it; DuckDB documents operator limitations, and process memory includes more than the setting governs. Test representative queries and temporary-storage conditions before committing to a size.
How do I check whether a query spills to disk?
Run it on representative data with the memory limit and temporary directory you plan to use, inspect the plan, and watch temporary-storage use during the run. A tiny example like the one above cannot show spill behavior.
What makes a pipeline repeatable?
Declared input identity, schema meaning, query, settings, version and verified outputs. A moving glob and undocumented environment can change a result even when the SQL text stays the same.