🔧 Fix DuckDB & analytic SQL Performance & scale DuckDB for Spatial Data · step 19 Notebook By · Updated

Fixing DuckDB Out of Memory on a Large Spatial Query

On this page

Problem statement

duckdb.OutOfMemoryException: Out of Memory Error: failed to allocate data of
size 256.0 KiB (136.1 MiB/136.3 MiB used)

The message is more informative than most: it names the allocation that failed, the current usage and the limit. Here the limit was set as 143MB, which DuckDB reports in binary units as 136.3 MiB, and the query wanted one more block. It came from a GROUP BY on a fine grid, with spilling switched off (Example 4).

The surprise is that it happens at all, because DuckDB streams. Measured, joining all 13.47 million GeoNames points to 4,596 provinces and counting them per country peaked at about 245 MB, with or without a 1 GB memory_limit. So an out-of-memory error usually means the query contains a blocking operator whose state is genuinely large, not that the data is — or that spilling is unavailable.

Quick answer

Give it somewhere to spill, then reduce the state:

con.execute("set temp_directory = '/fast/scratch'")     # spill instead of failing
con.execute("set max_temp_directory_size = '100GB'")
con.execute("set memory_limit = '8GB'")
con.execute("set threads = 4")                           # fewer working sets

Then find the operator that is holding memory:

plan = con.execute("explain " + sql).fetchall()[0][1].upper()
for operator in ("HASH_GROUP_BY", "ORDER_BY", "HASH_JOIN", "WINDOW", "DISTINCT"):
    if operator in plan:
        print(f"{operator} holds state proportional to its output")

The commonest culprit by a distance is a GROUP BY whose key is too fine — measured, grouping 13.47 million points on a 0.02° grid produced 6,416,842 groups, a hash table holding nearly half the dataset. The same query on a 1° grid produced 23,460.

The three facts in a DuckDB out-of-memory message. more informative than mostpartexamplewhat it tells youallocation256.0 KiBthe block that failedusage136.1 MiBwhat is currently heldlimit136.3 MiBthe configured ceiling (143MB)usage below the limit means the memory is going somewhere DuckDB does not manageA process killed with no exception is the OS out-of-memory killer, not DuckDB spatialworkflow.io
The commonest cause of the second case is materialising a huge result with .df().

Spill, check the limit, count groups, bound sorts

1. Make sure it can spill

DuckDB 1.5 spills to a .tmp directory by default, even for an in-memory database. A query that exceeds the limit then writes intermediate state to disk and finishes more slowly: the 0.02° GROUP BY completed under a 143 MB limit in about two seconds. It fails when spilling is off (temp_directory = ''), when the directory is not writable, or when max_temp_directory_size is reached — so point it at a fast local disk with room:

con.execute("set temp_directory = '/fast/scratch'")
con.execute("set max_temp_directory_size = '100GB'")

This converts a class of hard failures into a class of slow successes. Put the directory on a local SSD; spilling to a network mount is dramatically slower.

2. Check whether the limit is the actual constraint

print(con.execute("select current_setting('memory_limit')").fetchone())

The default is roughly 80% of physical RAM. If the error reports a usage figure equal to a limit you did not set, something has set it — a container memory limit, a shared configuration, or an earlier set in the same session.

If the reported limit is large and the query still fails, the memory is being consumed outside the buffer manager. The usual cause is materialising a huge result with .df().

3. Count the groups in a GROUP BY

A hash aggregate holds one entry per group. That is the memory, and it is easy to measure:

select count(*) from (select <your group key> from <source> group by all);

Measured on 13,472,195 points (Example 4):

group key                               groups  share of rows
floor(lon / 0.02), floor(lat / 0.02)   6,416,842        47.63%
floor(lon / 0.1), floor(lat / 0.1)     961,927         7.14%
floor(lon / 0.5), floor(lat / 0.5)      71,990         0.53%
floor(lon / 1), floor(lat / 1)          23,460         0.17%

A group per two rows means the hash table holds essentially the whole dataset. Coarsening the key is not a workaround; it is the correct fix, because a grid that fine was showing sampling noise anyway.

4. Bound an ORDER BY

A sort holds the entire result. If you want the top rows, say so:

select ... order by n desc limit 100;      -- a bounded top-n, not a full sort

DuckDB recognises ORDER BY … LIMIT and keeps only the top rows, which turns an unbounded sort into a fixed-size heap.

5. Swap or shrink the hash join build side

A hash join materialises the build side. If DuckDB chose the larger table for it, or if both are large, filter first:

with a as (select * from big_table where year = 2025),
     b as (select * from other where country = 'GB')
select ... from a join b on ...

For a spatial join, simplifying the polygons reduces the structure the join builds — worthwhile when a few geometries have hundreds of thousands of vertices.

6. Reduce the thread count

Each thread has its own buffers for sorting and hashing, so peak memory scales with parallelism:

con.execute("set threads = 4")

The trade is wall-clock time against peak memory. When the alternative is failure, it is a good trade.

7. Do not materialise a huge result

frame = con.execute("select * from huge").df()          # allocates outside memory_limit

Aggregate in SQL, or write straight to a file:

copy (select ...) to 'out.parquet' (format parquet);

The engine then streams the result to disk and never holds it.

Fixing a DuckDB out-of-memory error, in the order that works. spill first, then shrink the state1check it can spilltemp_directory: on, writable, room2count the groupsthat is the hash table3bound the ORDER BYadd a LIMIT4filter or swap the build sideof a hash join5reduce threadsfewer working sets6write the result, do not materialise itCOPY, not .df()A spilled query is slower and finishes; a failed one did all the work and produced nothing spatialworkflow.io
Spilling is on by default in DuckDB 1.5; make sure nothing has switched it off.

Diagnosing memory on 13.47 million points

The data used on this page

Every feature in the GeoNames allCountries dump as a point with its country code — 13.47 million points — as points.parquet, and Natural Earth's 1:10m admin-1 states and provinces, 4,596 polygons in 251 countries, as GeoParquet. The two joins below are every point against every polygon, counted per country, and the 2.24 million US points against the 51 US states and DC. Memory is measured in a fresh Python process for every run, because a process's peak resident size can only go up. DuckDB 1.5.5.

import os
import zipfile

import duckdb
import geopandas as gpd
import pandas as pd
import requests


def fetch(url, path):
    if not os.path.exists(path):
        with requests.get(url, stream=True, timeout=600) as reply:
            reply.raise_for_status()
            with open(path, "wb") as out:
                for chunk in reply.iter_content(1 << 20):
                    out.write(chunk)


if not os.path.exists("points.parquet"):              # allCountries is about 400 MB zipped
    fetch("https://download.geonames.org/export/dump/allCountries.zip", "allCountries.zip")
    with zipfile.ZipFile("allCountries.zip") as z, z.open("allCountries.txt") as f:
        pd.read_csv(f, sep="\t", header=None, usecols=[4, 5, 8], quoting=3,
                    names=["lat", "lon", "country"], keep_default_na=False).to_parquet("points.parquet")
if not os.path.exists("admin1.parquet"):
    fetch("https://naciscdn.org/naturalearth/10m/cultural/ne_10m_admin_1_states_provinces.zip",
          "ne_10m_admin_1_states_provinces.zip")
    gpd.read_file("ne_10m_admin_1_states_provinces.zip")[["adm0_a3", "name", "geometry"]] \
        .to_parquet("admin1.parquet")

con = duckdb.connect()
con.execute("install spatial; load spatial")
US_JOIN_SQL = """
    select a.name, count(*) from read_parquet('points.parquet') p
    join (select name, geometry from read_parquet('admin1.parquet') where adm0_a3 = 'USA') a
      on st_contains(a.geometry, st_point(p.lon, p.lat))
    where p.country = 'US' group by 1"""
GLOBAL_JOIN_SQL = """
    select a.adm0_a3, count(*) from read_parquet('points.parquet') p
    join read_parquet('admin1.parquet') a on st_contains(a.geometry, st_point(p.lon, p.lat))
    group by 1"""
points, us = con.execute("select count(*), count(*) filter (where country = 'US') "
                         "from read_parquet('points.parquet')").fetchone()
polygons = con.execute("select count(*) from read_parquet('admin1.parquet')").fetchone()[0]
print(f"{points:,} points ({us:,} in the US), {polygons:,} polygons; DuckDB {duckdb.__version__}")
13,472,195 points (2,241,404 in the US), 4,596 polygons; DuckDB 1.5.5

Example 1 — the diagnostic

import duckdb


BLOCKING = {
    "HASH_GROUP_BY": "one entry per group — count your groups",
    "ORDER_BY": "the whole result — add a LIMIT",
    "HASH_JOIN": "the build side — filter or swap it",
    "WINDOW": "a partition at a time — partition more finely",
    "DISTINCT": "one entry per distinct combination",
}


def diagnose_oom(con, sql, group_key=None, source=None):
    print("settings")
    for setting in ("memory_limit", "temp_directory", "max_temp_directory_size",
                    "threads"):
        value = con.execute(f"select current_setting('{setting}')").fetchone()[0]
        flag = "  <- not set; a spilling query will fail" \
            if setting == "temp_directory" and not value else ""
        print(f"  {setting:24} {value}{flag}")

    plan = con.execute("explain " + sql).fetchall()[0][1].upper()
    print("\nblocking operators in the plan")
    found = [op for op in BLOCKING if op in plan]
    for operator in found:
        print(f"  {operator:16} {BLOCKING[operator]}")
    if not found:
        print("  none — the memory is probably going into a materialised result")

    if group_key and source:
        groups = con.execute(
            f"select count(*) from (select {group_key} from {source} group by all)"
        ).fetchone()[0]
        rows = con.execute(f"select count(*) from {source}").fetchone()[0]
        print(f"\ngroup cardinality: {groups:,} groups from {rows:,} rows "
              f"({100 * groups / rows:.2f}%)")
        if groups > rows * 0.1:
            print("  ! the hash table will hold most of the dataset — coarsen the key")


fine = ("select floor(lon / 0.02), floor(lat / 0.02), count(*) "
        "from read_parquet('points.parquet') group by 1, 2")
diagnose_oom(duckdb.connect(), fine, group_key="floor(lon / 0.02), floor(lat / 0.02)",
             source="read_parquet('points.parquet')")
settings
  memory_limit             48.7 GiB
  temp_directory           .tmp
  max_temp_directory_size  90% of available disk space
  threads                  24

blocking operators in the plan
  HASH_GROUP_BY    one entry per group — count your groups

group cardinality: 6,416,842 groups from 13,472,195 rows (47.63%)
  ! the hash table will hold most of the dataset — coarsen the key
Peak memory of four DuckDB runs, each in a fresh process. peak resident memory, one process per runUS join, default limit175 MBUS join, 200 MB limit176 MBglobal join, 1 GB limit246 MB0.02° grid, 143 MB limit1,327 MB — the fetched result0700 MB1400 MBThe joins never needed their limits; the grid query spilled and then fetched 6.4 million rows spatialworkflow.io
A limit bounds DuckDB’s buffers, not the rows you pull into Python.

Example 2 — proving a query streams

import json
import subprocess
import sys
import textwrap

CHILD = textwrap.dedent("""
    import duckdb, json, sys, time
    cfg = json.loads(sys.argv[1])
    con = duckdb.connect()
    con.execute("load spatial")
    for key in ("memory_limit", "temp_directory", "threads"):
        if cfg.get(key) is not None:
            con.execute(f"set {key} = '{cfg[key]}'")
    started = time.perf_counter()
    try:
        status = f"{len(con.execute(cfg['sql']).fetchall()):,} rows"
    except Exception as exc:
        status = f"{type(exc).__name__}: {str(exc).splitlines()[0]}"
    # VmHWM is this process's own peak; ru_maxrss can carry the parent's over on Linux
    hwm = next(l for l in open("/proc/self/status") if l.startswith("VmHWM"))
    print(json.dumps({"seconds": time.perf_counter() - started, "status": status,
                      "peak_mb": int(hwm.split()[1]) / 1024}))
""")


def run_isolated(sql, **settings):
    """Run one query in a fresh process, so the peak memory is this query's alone."""
    done = subprocess.run([sys.executable, "-c", CHILD, json.dumps({"sql": sql, **settings})],
                          capture_output=True, text=True, check=True)
    return json.loads(done.stdout.splitlines()[-1])


def streams_under(sql, limits=(None, "2GB", "1GB", "500MB", "200MB")):
    """A query whose peak stays flat as the limit falls is genuinely streaming."""
    for limit in limits:
        r = run_isolated(sql, memory_limit=limit)
        print(f"{limit or 'default':>7}  {r['seconds']:6.2f}s  peak {r['peak_mb']:5,.0f} MB  {r['status']}")


print(f"load average {os.getloadavg()[0]:.1f}")
streams_under(US_JOIN_SQL)
load average 6.0
default    5.40s  peak   175 MB  51 rows
    2GB    5.47s  peak   175 MB  51 rows
    1GB    5.34s  peak   177 MB  51 rows
  500MB    5.66s  peak   176 MB  51 rows
  200MB    5.51s  peak   176 MB  51 rows

Flat across every limit — about 175 MB whether the limit was the default 48.7 GiB or 200 MB — means the query never needed the memory. A run whose time climbs as the limit falls is spilling; one that fails has state it cannot shrink. Each run is a fresh process: measured in one process, ru_maxrss can only rise, so every run after the first would report the largest peak so far.

Example 3 — writing the result instead of holding it

def aggregate_to_file(con, sql, target, format="parquet"):
    """Never call .df() on something that might be enormous."""
    import os

    con.execute(f"copy ({sql}) to '{target}' (format {format})")
    size = os.path.getsize(target)
    rows = con.execute(f"select count(*) from read_{format}('{target}')").fetchone()[0]
    print(f"{rows:,} rows → {target} ({size / 1e6:,.1f} MB), "
          f"nothing materialised in Python")
    return target


aggregate_to_file(con, "select floor(lon / 0.02) as gx, floor(lat / 0.02) as gy, count(*) as n "
                       "from read_parquet('points.parquet') group by 1, 2", "grid_002.parquet")
6,416,842 rows → grid_002.parquet (32.0 MB), nothing materialised in Python

Example 4 — the measurements quoted on this page

print(f"load average {os.getloadavg()[0]:.1f}")
for limit, spill in (("143MB", ""), ("143MB", None)):
    r = run_isolated(fine, memory_limit=limit, temp_directory=spill)
    print(f"0.02° grid, limit {limit}, temp_directory {'off' if spill == '' else 'default'}: "
          f"{r['seconds']:.1f}s  peak {r['peak_mb']:,.0f} MB  {r['status']}")

for limit in (None, "1GB"):
    r = run_isolated(GLOBAL_JOIN_SQL, memory_limit=limit)
    print(f"global join, limit {limit or 'default'}: {r['seconds']:.1f}s  "
          f"peak {r['peak_mb']:,.0f} MB  {r['status']}")

rows = con.execute("select count(*) from read_parquet('points.parquet')").fetchone()[0]
print(f"\n{'group key':<34}{'groups':>12}{'share of rows':>15}")
for step in (0.02, 0.1, 0.5, 1):
    key = f"floor(lon / {step}), floor(lat / {step})"
    groups = con.execute(f"select count(*) from (select {key} from read_parquet('points.parquet') "
                         "group by all)").fetchone()[0]
    print(f"{key:<34}{groups:>12,}{groups / rows:>14.2%}")
load average 6.1
0.02° grid, limit 143MB, temp_directory off: 0.0s  peak 260 MB  OutOfMemoryException: Out of Memory Error: failed to allocate data of size 256.0 KiB (136.1 MiB/136.3 MiB used)
0.02° grid, limit 143MB, temp_directory default: 2.3s  peak 1,327 MB  6,416,842 rows
global join, limit default: 77.7s  peak 245 MB  249 rows
global join, limit 1GB: 72.6s  peak 246 MB  249 rows

group key                               groups  share of rows
floor(lon / 0.02), floor(lat / 0.02)   6,416,842        47.63%
floor(lon / 0.1), floor(lat / 0.1)     961,927         7.14%
floor(lon / 0.5), floor(lat / 0.5)      71,990         0.53%
floor(lon / 1), floor(lat / 1)          23,460         0.17%

Why grouping is the usual culprit

Why streaming means the memory is about the output

DuckDB reads in batches. A filter or a projection processes a batch and forgets it, so memory is constant however large the input.

Blocking operators break that, and each one holds a different thing: a GROUP BY holds a row per group, a sort holds the whole result, a hash join holds the build side. In every case the state is proportional to something other than the input size — which is why joining 13.47 million points and aggregating them into 249 countries peaks at about 245 MB.

The diagnostic question is therefore never "how big is the data?" but "how big is the state?".

Why grouping is the usual culprit in spatial work

Spatial aggregations frequently group on a coordinate-derived key: a fine grid, a geohash, an H3 cell at a high resolution. Those keys can be nearly unique.

The measured ladder is the whole story: a 0.02° grid over 13.47 million points produced 6.42 million groups — 47.63% of the rows — while a 1° grid produced 23,460. Same query shape, a hash table two orders of magnitude apart.

And the fine grid was not producing a better map: with a median of one point per occupied cell, it was showing sampling noise.

Why the result set is outside the limit

memory_limit governs DuckDB's buffer manager. A pandas DataFrame built by .df() is Python and NumPy memory, allocated by a different allocator that DuckDB does not track.

So a query can respect its limit perfectly and still blow up the process. The 0.02° GROUP BY held to a 143 MB limit by spilling, and its process still peaked at 1,327 MB, because the 6.4 million result rows were fetched into Python. The signal is an out-of-memory error whose reported usage is well below the limit, or a process killed by the operating system rather than an exception.

Why spilling is better than failing

A spilled sort or aggregate is slower — it writes intermediate state to disk and reads it back — and it produces an answer. A failed one produces nothing after having done all the work.

Spilling is therefore worth keeping on in any batch job — DuckDB 1.5 does so by default. The one thing to check is that the directory has room: max_temp_directory_size defaults to 90% of the filesystem's free space, and a large sort can genuinely use tens of gigabytes.

What DuckDB’s memory limit covers and what it does not. memory_limit is not the processinside the limit✓hash tables and sort buffers✓cached data blocks✓join build sides✓spills when it is exceededoutside the limit✕a pandas DataFrame from .df()✕Arrow conversion buffers✕Python objects you keep✕the container’s own memory capAn OOM whose reported usage is well below the limit is memory the engine never allocated spatialworkflow.io
Aggregate in SQL or COPY to a file rather than materialising a huge result.

Spill space, container limits and threads

  • Check spilling is on and has room. DuckDB 1.5 spills to .tmp by default; temp_directory = '' turns a spill into a failure.
  • memory_limit does not cover .df(), Arrow conversion or Python objects.
  • Container memory limits are invisible to DuckDB unless you set memory_limit to match.
  • Threads multiply working sets. Reducing them is a legitimate fix.
  • ORDER BY with LIMIT is a heap, not a sort — much cheaper.
  • approx_count_distinct uses a fraction of the memory of exact count(distinct).
  • A killed process with no exception is the OS out-of-memory killer, not DuckDB.
  • Check the group count before optimising anything else in an aggregation.

FAQ

Why does DuckDB run out of memory if it streams?

Because some operators block: GROUP BY, ORDER BY, the build side of a hash join, DISTINCT. Their state is proportional to their output, not to the input.

What does the error message tell me?

The failed allocation, the current usage and the limit — (136.1 MiB/136.3 MiB used) for a limit set as 143MB. If usage equals the limit, the limit is the constraint; if not, the memory is going somewhere DuckDB does not manage.

How do I stop a GROUP BY from exhausting memory?

Count the groups. A 0.02° spatial grid over 13.47 million points produced 6.42 million groups; a 1° grid produced 23,460. Coarsen the key.

Why does the process get killed with no exception?

That is the operating system's out-of-memory killer, usually because a result was materialised outside DuckDB's limit. Write the result to a file instead of calling .df().

Does setting a temp directory really help?

It is the difference between failing and finishing. DuckDB 1.5 already spills to .tmp by default; with spilling switched off, the same 143 MB query that finished in about two seconds raised an out-of-memory error instead. Set the directory explicitly when the default location is slow, small or not writable.

Should I reduce the thread count?

If peak memory is the problem, yes. Each thread carries its own sort and hash buffers, so fewer threads means a smaller total working set.