DuckDB Spatial Analytical SQL for GIS

Tuning temp_directory and Spill Throughput

A query that spills is not failing — it is trading memory for I/O, and whether that trade is a mild slowdown or a hundredfold one is decided entirely by where the spill goes. This walkthrough, part of the in-memory vs disk storage reference, covers choosing a spill target, bounding it so a runaway query cannot fill a volume, and telling the difference between healthy spilling and thrashing.

Root-Cause Analysis: why spilling turns from a slowdown into a cliff

  • The target is a network mount. Every spill write and read becomes a network round trip, and a query that spills continuously makes a great many of them. This single mistake accounts for most reports of spilling being catastrophic rather than merely slow.
  • The target is a container overlay filesystem. Overlay filesystems add copy-on-write behaviour that is fine for image layers and poor for large sequential writes, and the default temporary directory inside a container is usually one.
  • No target is set at all. Without a spill directory the engine has nowhere to go, so exceeding the memory limit is a hard failure rather than a degradation. That is occasionally what you want in a test and rarely what you want in production.
  • The volume fills. An unbounded spill can consume every byte on a shared volume, taking down whatever else uses it. max_temp_directory_size is the only thing preventing that.
  • The query re-reads its spill repeatedly. A hash join whose build side spills reads it back once; a sort that spills reads its runs back during the merge. A query that reads spill many times over is thrashing, and the fix is a smaller working set rather than a faster disk.

The distinguishing question is whether spill volume grows while the query progresses or grows without it. The first is normal; the second is thrashing, and no storage device fixes it.

What a spilling query costs, by target Local NVMe is 2–4x, SATA SSD 5–10x, spinning disk 20–50x, a network mount 50–200x, and an overlay filesystem is unpredictable. SPILL TARGET COST VS IN-MEMORY VERDICT local NVMe ~2–4× the target to aim for local SATA SSD ~5–10× acceptable spinning disk ~20–50× tolerable for batch only a network mount ~50–200× never container overlay FS unpredictable worse than uniformly slow

The gap between the first row and the fourth is larger than most tuning ever recovers.

Deterministic Configuration 

INSTALL spatial; LOAD spatial;

-- The ceiling. Leave the host room: setting this near total RAM means the OS
-- runs out first and kills the process, which is worse than spilling.
SET memory_limit = '8GB';

-- Local, fast, and explicitly named. Never /tmp inside a container unless you
-- have checked what /tmp actually is there.
SET temp_directory = '/nvme/duckdb_spill';

-- The blast radius. Without this a runaway query can fill the volume and
-- take down whatever else shares it.
SET max_temp_directory_size = '200GB';

Optimized Execution Pattern

The pattern is to configure the target deliberately and then to size the working set so spilling is occasional rather than continuous — because the target only bounds how expensive spilling is, not how often it happens.

-- ANTI-PATTERN: no target, so exceeding the limit is a hard failure; and no
-- bound, so if a target were set it could fill the volume.
SET memory_limit = '8GB';
-- (nothing else)
-- PATTERN: a fast local target, an explicit bound, and a memory limit that
-- leaves the host room to breathe.
SET memory_limit = '8GB';
SET temp_directory = '/nvme/duckdb_spill';
SET max_temp_directory_size = '200GB';

-- Then reduce the working set so spilling stays occasional: project early,
-- filter before the join, and make the smaller relation the build side.
SELECT z.zone_id, count(*)
FROM (SELECT geom, zone_hint FROM incidents WHERE occurred_at >= DATE '2024-01-01') i
JOIN zones z ON z.geom && i.geom
WHERE ST_Intersects(z.geom, i.geom)
GROUP BY z.zone_id;

The projection in the subquery is doing more than it appears: every column carried into a join is carried into the spill as well, so dropping unused columns before the join reduces both the memory and the bytes written if it spills anyway.

Four levers on spill volume Project early, lower threads, choose the build side, and reduce precision — roughly in that order of return. LEVER WHAT IT REDUCES TYPICAL RETURN project unused columns away working set and spilled bytes largest, on wide tables lower the thread count concurrent working sets fastest fix mid-flight smaller relation as build side the hash table large on skewed joins reduce geometry precision every vertex large when geometry dominates

Three of the four cost nothing but a rewrite. The fourth costs a stated tolerance.

Sizing the volume

The question “how much spill space do I need” has an answer that is easier than it looks: at least as much as the largest working set you expect to exceed the memory limit by, plus headroom. For a hash join, that is roughly the build side after projection. For a sort, it is roughly the whole input, because a sort has to materialise everything before it can merge.

The sort case is the one that surprises people, and it is why a COPY ... ORDER BY over a large table needs spill space comparable to the dataset rather than to the memory limit. That is a real requirement rather than a symptom of bad tuning, and planning for it is what stops a nightly write failing on the one night the input grew.

Diagnostic Queries & Plan Validation

Spill is observable while it happens, and the shape of the observation is what distinguishes healthy from pathological.

-- From a second connection, during a long query. Sample it repeatedly: the
-- trend matters more than any single reading.
SELECT count(*) AS files, sum(size) / 1e9 AS spill_gb
FROM duckdb_temporary_files();

Spill that grows to a plateau and then falls is a join or a sort doing exactly what it should. Spill that grows continuously while the query makes no progress is thrashing, which means the working set is far larger than the limit rather than slightly larger — and the fix is to reduce it, not to provide more disk.

Three spill profiles A rise-and-fall is healthy; a stepped rise is a multi-stage query; a continuous rise with no output is thrashing. PROFILE MEANS ACTION rises to a plateau, then falls a join or sort, working normally none rises in steps a multi-stage query none, if each step completes rises continuously, no output thrashing reduce the working set

Only the third row is a problem, and a faster disk does not solve it.

Spilling inside a container

Containers get this wrong by default in two ways at once, and both are invisible until a job fails. The default temporary directory is usually on the overlay filesystem, whose copy-on-write behaviour is poor for large sequential writes; and the memory limit, if left at the DuckDB default, is derived from the host rather than from the cgroup, so the engine believes it has memory the container will not let it use.

The fix for both is explicit: mount a volume on fast local storage and point temp_directory at it, and set memory_limit from the container’s limit rather than letting it be inferred. Neither is difficult and both are easy to omit, which is why a pipeline that works on a laptop and fails in a scheduler is so often failing for one of these two reasons rather than for anything about the query.

Geometry Validation & Fallback Routing

Where the working set genuinely cannot be reduced, the fallback is to partition the query so each piece fits, which converts an unbounded spill into a bounded sequence.

-- One partition at a time. Each piece fits, each is restartable, and the
-- spill for each is bounded by that partition rather than by the dataset.
CREATE OR REPLACE TABLE result AS
SELECT * FROM (
    SELECT z.zone_id, count(*) AS n
    FROM incidents i JOIN zones z ON z.geom && i.geom
    WHERE ST_Intersects(z.geom, i.geom) AND z.region = 'west'
    GROUP BY z.zone_id
);
-- repeat per region and UNION ALL, rather than running all regions at once

Frequently Asked Questions

Is spilling bad?

No — a query that spills and finishes is strictly better than one that is killed. Spilling becomes a problem only when the target is slow, when the volume fills, or when the working set exceeds the limit so far that the engine thrashes. The first two are configuration and the third is a query problem.

Where should temp_directory point?

Fast local storage, ideally NVMe, and never a network mount. The gap between local NVMe and a network mount is roughly two orders of magnitude on a spilling query, which is larger than almost any tuning will recover.

What happens if I do not set it?

Exceeding the memory limit becomes a hard error rather than a degradation. That is occasionally useful in a test — it fails loudly instead of getting silently slow — and in production it converts a recoverable situation into a failed job.

How much spill space do I need?

At least the largest working set you expect to exceed the limit by, plus headroom. For a sort that means space comparable to the dataset, because a sort materialises everything before merging — which is the requirement that most often catches a nightly write out.

My query spills continuously and never finishes. What now?

That is thrashing rather than spilling, and it means the working set exceeds the limit by a large multiple. A faster disk does not fix it. Reduce what enters the operation — project columns away, filter earlier, partition the query — or raise the limit if the host genuinely has the memory.

Why does the same job work locally and fail in a container?

Two defaults, both wrong there. The temporary directory usually lands on the overlay filesystem, which is poor for large sequential writes; and the memory limit, if inferred, comes from the host rather than the cgroup, so the engine believes it has memory the container will not grant.

Up: In-Memory vs Disk Storage