🔧 Fix DuckDB & analytic SQL Performance & scale DuckDB for Spatial Data · step 19 of 20 Updated

Fixing DuckDB Out of Memory on a Large Spatial Query

Problem statement 

duckdb.OutOfMemoryException: Out of Memory Error: could not allocate block of
size 256.0 KiB (143.5 MiB/143.0 MiB used)

The message is more informative than most: it names the allocation that failed, the current usage and the limit. In this case the limit was 143 MB and the query wanted one more block.

The surprise is that it happens at all, because DuckDB streams. Measured, a 13,464,017-point join and aggregate peaked at 305 MB — and completed unchanged with memory_limit set to 1 GB. So an out-of-memory error usually means the query contains a blocking operator whose state is genuinely large, not that the data is.

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.5 million points on a 0.02° grid produced 6,414,445 groups, a hash table holding nearly half the dataset. The same query on a 1° grid produced 23,422.

Table breaking a DuckDB out-of-memory message into allocation, usage and limit.
The commonest cause of the second case is materialising a huge result with .df().

Step-by-step solution

1. Set a temp directory, first

Without one, a query that exceeds the limit fails. With one, it spills to disk and finishes more slowly:

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 1);

Measured on 13,464,017 points:

group key                     groups    share of rows
floor(lon/0.02), floor(lat/0.02)   6,414,445    47.6%
floor(lon/0.1),  floor(lat/0.1)      961,781     7.1%
floor(lon/0.5),  floor(lat/0.5)       71,921     0.5%
floor(lon),      floor(lat)           23,422     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.

Six ordered fixes for a DuckDB out-of-memory error.
Setting a temp directory is close to free insurance in any batch job.

Code examples

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 1)"
        ).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")

Example 2 — proving a query streams 

def streams_under(con_factory, sql, limits=("2GB", "1GB", "500MB", "200MB")):
    """A query that survives a tight limit is genuinely streaming."""
    import resource
    import time

    for limit in limits:
        con = con_factory()
        con.execute(f"set memory_limit = '{limit}'")
        started = time.perf_counter()
        try:
            con.execute(sql).fetchall()
            status = "ok"
        except Exception as exc:
            status = type(exc).__name__
        elapsed = time.perf_counter() - started
        peak = resource.getrusage(resource.RUSAGE_SELF).ru_maxrss / 1024
        print(f"{limit:>7}  {elapsed:6.2f}s  peak {peak:7,.0f} MB  {status}")
        con.close()
   2GB    4.31s  peak     228 MB  ok
   1GB    4.29s  peak     228 MB  ok
 500MB    4.83s  peak     230 MB  ok
 200MB    4.18s  peak     227 MB  ok

Flat across every limit 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.

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

Explanation

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 a 13.5-million-row aggregate into 251 groups peaks at 305 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.5 million points produced 6.4 million groups — 47.6% of the rows — while a 1° grid produced 23,422. 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 a 1 GB limit perfectly and then blow up the process when its 40-million-row result is converted. 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.

Setting temp_directory is therefore close to free insurance in any batch job. 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.

Two panels listing what DuckDB memory_limit covers and what it does not.
Aggregate in SQL or COPY to a file rather than materialising a huge result.

Edge cases or notes

  • Set temp_directory before you need it. Without one, a spill is 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 — (143.5 MiB/143.0 MiB used). 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.5 million points produced 6.4 million groups; a 1° grid produced 23,422. 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. Without it a spilling query raises; with it, the query writes intermediate state to disk and completes more slowly.

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.