Design DuckDB Spill Storage as a Resource Budget

Version scope: DuckDB 2.0. The SQL in this article was run on DuckDB 2.0.0 against small synthetic fixtures.

A dataset fitting on disk does not mean every query over it fits within the machine's working resources. A small final answer can require a large join, sort or grouping stage. Analytical capacity planning should include temporary storage and concurrent jobs, not only source file size.

DuckDB supports larger-than-memory processing by spilling supported work to temporary storage. Its documentation describes configurable temporary directories and limitations involving blocking operators and combinations of operators. Support for spill is not a guarantee that an arbitrary query completes under any memory limit. DuckDB workload tuning

Define the job's operating envelope

Start with four budgets: process memory, temporary disk capacity, disk throughput and maximum concurrent jobs. Add an execution deadline so that a query does not consume resources indefinitely simply because it still makes progress.

Give the spill directory an explicit location on storage with understood performance and lifecycle. A container's writable layer, a shared volume and local scratch storage have different capacity and failure characteristics. Check which path is actually mounted in the running worker, not just the path in the deployment template.

Example settings below are illustrative. They are not universal recommendations; select limits from your environment's budget.

SET memory_limit = '2GB';
SET temp_directory = '/scratch/report-job-42';
SET threads = 2;

The operating system or container limit must leave headroom beyond the database setting. Other allocations, the host application and concurrent components still consume memory. Measure process-level usage rather than treating one database setting as a complete isolation mechanism.

Build a capacity experiment around the shape of the query

Use synthetic inputs that reproduce the relevant distribution: key skew, number of groups, join selectivity and row width. A uniform dataset with the same row count may create very different intermediate state from the workload you need to support.

Run one job first. Capture elapsed time, peak process memory, temporary storage high-water mark and result correctness. Then increase admitted concurrency in a controlled test. Stop before exhausting a shared production volume. The goal is to find a sustainable operating envelope, not to provoke a host-wide outage.

A useful admission rule can be conservative. If one class of job routinely consumes most of the temporary volume, serialize that class or provision a separate worker pool. Queueing work visibly is often easier to operate than allowing many jobs to fail together.

Specify failure handling before the disk fills

What should happen when scratch capacity is unavailable? The job should return a clear failure, preserve enough metadata to diagnose it and avoid publishing partial results as final. Use a run identifier and promote outputs only after validation succeeds.

Do not delete an active spill directory to recover space. Cleanup must distinguish live jobs from abandoned runs and follow the engine's lifecycle requirements. Apply retention to job logs as well as data; SQL text and error details can contain sensitive values.

Test a cancelled job and a worker restart. Confirm that the next run starts from a known state and that old temporary files do not silently consume the new budget. Exercise these scenarios in an isolated environment.

Write down the tradeoff

Spill can turn an otherwise impossible calculation into a feasible one, but disk work may increase latency. More threads may also increase resource pressure. The right configuration follows the service objective: predictable completion, interactive response or economical batch throughput.

Publish a capacity envelope with measured evidence when available. Until then, describe these values as test settings rather than capacity guarantees. A reliable analytical service admits only the work it can finish within its declared resource budget.

Memory and scratch storage are separate finite resources. The output is released only after the job succeeds and passes validation.