DuckDB's Memory Model: Streaming, Spilling and Limits #

Problem statement #

The reason DuckDB handles datasets larger than memory is not a trick — it is that most analytical operations do not need the whole dataset at once. It reads in batches, keeps only what an operator genuinely requires, and writes to disk when even that does not fit.

The measurements make the behaviour concrete. Joining 13,464,017 points to 4,596 polygons and aggregating the result:

                        time     peak RSS
DuckDB, no limit       61.8 s      305 MB
DuckDB, limit 1 GB     68.7 s      306 MB
GeoPandas             117.5 s    4,739 MB

and on a smaller job, 2,241,395 points against 51 polygons:

DuckDB, no limit        4.27 s      227 MB
DuckDB, limit 500 MB    4.83 s      230 MB
DuckDB, limit 200 MB    4.18 s      227 MB
GeoPandas              15.48 s      988 MB

Setting a limit below the unconstrained usage changed nothing, because the query never needed the memory in the first place.

Quick answer #

Set a limit and a spill directory, and the engine handles the rest:

import duckdb

con = duckdb.connect()
con.execute("set memory_limit = '4GB'")            # default: about 80% of RAM
con.execute("set temp_directory = '/fast/scratch'")  # where it spills
con.execute("set max_temp_directory_size = '50GB'")
con.execute("set threads = 8")                      # each thread has a working set

print(con.execute("""
    select current_setting('memory_limit')   as memory_limit,
           current_setting('threads')        as threads,
           current_setting('temp_directory') as temp_dir
""").df())

If a query exceeds the limit and cannot spill, the error is explicit:

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

Step-by-step solution #

1. Understand which operators are streaming and which are blocking #

Streaming operators process a batch and emit a batch. Memory use is constant regardless of input size:

• projections and filters
• most joins on the probe side
• LIMIT

Blocking operators must see all their input before producing output. Their memory is proportional to the state they hold, not to the input:

• GROUP BY — one entry per group
• ORDER BY — the whole result, unless combined with a limit
• hash join build side — the smaller table
• window functions and DISTINCT

That distinction explains the measurements. Aggregating 13.5 million rows into 251 groups peaked at 305 MB because the group table has 251 entries — the input never accumulated.

2. Set a memory limit, and know what it covers #

memory_limit bounds the buffer manager: hash tables, sort buffers, cached blocks. It does not bound everything — arrow conversions, Python objects and the result set live outside it.

The default is roughly 80% of physical RAM. Lowering it is useful on a shared machine, and it is also a good test: a query that still completes under a tight limit is genuinely streaming.

3. Give it somewhere to spill #

When a blocking operator exceeds the limit, DuckDB writes intermediate state to temp_directory. Without one set, the query fails instead:

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

Put it on the fastest disk available. A sort that spills to a slow network mount is dramatically slower than one that spills to a local SSD.

4. Read the out-of-memory message #

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

Three facts in one line: the allocation that failed, the current usage and the limit. If usage equals the limit, the limit is the constraint. If the limit is large and the error still appears, something outside the buffer manager is consuming memory — usually a very large result being materialised.

5. Reduce the state, not the data #

When a query genuinely needs too much memory, the fix is almost always to make the operator state smaller:

• GROUP BY with millions of groups — is the grouping key too fine? Rounding coordinates to a coarser grid reduces groups by orders of magnitude.
• ORDER BY on a full result — add a LIMIT so the sort keeps only the top rows.
• A hash join whose build side is huge — swap the sides, or filter the build side first.
• DISTINCT over many columns — distinct on fewer columns, or approximate with approx_count_distinct.

6. Watch the thread count #

Threads multiply working sets: each has its own buffers for sorting and hashing. On a machine with 24 threads and a modest limit, reducing threads can be what makes a query fit.

con.execute("set threads = 4")

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

Code examples #

Example 1 — profiling a query's memory behaviour #

import duckdb
import resource
import time


def profile_query(sql, memory_limit=None, temp_dir=None, threads=None):
    """Peak RSS and time, with the settings that produced them."""
    con = duckdb.connect()
    con.execute("set enable_progress_bar = false")
    if memory_limit:
        con.execute(f"set memory_limit = '{memory_limit}'")
    if temp_dir:
        con.execute(f"set temp_directory = '{temp_dir}'")
    if threads:
        con.execute(f"set threads = {threads}")

    started = time.perf_counter()
    try:
        rows = con.execute(sql).fetchall()
        status = f"{len(rows):,} rows"
    except duckdb.OutOfMemoryException as exc:
        status = f"OOM: {str(exc).splitlines()[0]}"
    elapsed = time.perf_counter() - started
    peak = resource.getrusage(resource.RUSAGE_SELF).ru_maxrss / 1024

    print(f"limit {str(memory_limit or 'default'):>8}  threads {threads or 'default':>7}  "
          f"{elapsed:6.2f}s  peak RSS {peak:7,.0f} MB  {status}")
    return elapsed, peak

>>> for limit in (None, "1GB"):
...     profile_query(GLOBAL_JOIN_SQL, memory_limit=limit)
limit  default  threads default   61.77s  peak RSS     305 MB  251 rows
limit      1GB  threads default   68.66s  peak RSS     306 MB  251 rows

>>> for limit in (None, "500MB", "200MB"):
...     profile_query(US_JOIN_SQL, memory_limit=limit)
limit  default  threads default    4.27s  peak RSS     227 MB  51 rows
limit    500MB  threads default    4.83s  peak RSS     230 MB  51 rows
limit    200MB  threads default    4.18s  peak RSS     227 MB  51 rows

Flat across every limit is the signature of a streaming query. A query whose time climbs steeply as the limit falls is spilling, and one that fails is blocking on state it cannot shrink.

Example 2 — finding the blocking operator #

def explain_memory(con, sql):
    """Which operators in this plan will hold state?"""
    plan = con.execute("explain " + sql).fetchall()[0][1]
    blocking = {
        "HASH_GROUP_BY": "one entry per group",
        "ORDER_BY": "the whole result, unless combined with a LIMIT",
        "HASH_JOIN": "the build side in memory",
        "WINDOW": "a partition at a time",
        "DISTINCT": "one entry per distinct combination",
        "SPATIAL_JOIN": "a spatial structure over one side",
    }
    print(plan)
    print("\nblocking operators in this plan:")
    found = False
    for operator, note in blocking.items():
        if operator in plan.upper():
            print(f"  {operator:16} holds {note}")
            found = True
    if not found:
        print("  none — this query streams and its memory is constant")

Example 3 — shrinking the state rather than the data #

def group_cardinality(con, source, key_expression):
    """How many groups will this GROUP BY create? That is the memory."""
    groups = con.execute(f"""
        select count(*) from (
            select {key_expression} from {source} group by 1)
    """).fetchone()[0]
    rows = con.execute(f"select count(*) from {source}").fetchone()[0]
    print(f"{rows:,} rows → {groups:,} groups "
          f"({100 * groups / rows:.2f}% — memory is proportional to the groups)")
    if groups > rows * 0.1:
        print("  ! a group per ten rows means the key is nearly unique; "
              "coarsen it or expect the hash table to hold most of the data")
    return groups

>>> group_cardinality(con, SOURCE, "floor(lon / 0.02), floor(lat / 0.02)")
13,464,017 rows → 6,414,445 groups (47.64% — memory is proportional to the groups)
  ! a group per ten rows means the key is nearly unique; coarsen it or expect
    the hash table to hold most of the data

>>> group_cardinality(con, SOURCE, "floor(lon), floor(lat)")
13,464,017 rows → 23,422 groups (0.17% — memory is proportional to the groups)

A 0.02° grid produces 6.4 million groups and a hash table holding nearly half the dataset. A 1° grid produces 23,422 and holds almost nothing. Same query shape, two entirely different memory profiles.

Explanation #

Why the memory is proportional to the output, not the input #

An aggregation maintains one accumulator per group. Reading a batch updates accumulators and discards the batch, so at any moment the engine holds a small number of batches plus the group table.

That is why the 13.5-million-row job peaked at 305 MB: 251 groups plus buffers. It is also why the group-cardinality check above is the single most useful diagnostic for an aggregation that will not fit — the number of groups is the memory.

Why GeoPandas needs so much more #

GeoPandas materialises: a GeoDataFrame of 13.5 million shapely geometries, then a joined frame containing both sides' columns for every matched pair, then the grouped result.

Measured, that peaked at 4,739 MB against DuckDB's 305 MB — 15.5×. The difference is not implementation efficiency, it is that one library holds the intermediate result and the other does not.

Why spilling is a feature and not a failure #

A query that spills is slower and it finishes. A query that cannot spill and exceeds the limit raises OutOfMemoryException and produces nothing.

Setting temp_directory therefore converts a class of hard failures into a class of slow successes, which is almost always the trade you want in a batch job. The cost is disk I/O, which is why the directory should be on the fastest local disk available.

Why a lower limit sometimes runs faster #

In the measurements, the 200 MB run finished in 4.18 s and the unconstrained one in 4.27 s. That is noise, not an effect — but a lower limit genuinely can help on a busy machine, because a smaller buffer pool leaves more page cache for the operating system and reduces pressure that would otherwise cause swapping.

The practical reading: on a shared machine, set a limit well below physical RAM. The query will rarely be slower and the machine will stay responsive.

Edge cases or notes #

• memory_limit covers the buffer manager, not Python objects or the result you materialise.
• .df() on a huge result allocates outside the limit — that is a common source of an OOM the engine did not cause.
• Threads multiply working sets. Reducing threads is a legitimate memory fix.
• temp_directory must exist and be writable, or spilling fails.
• max_temp_directory_size defaults to 90% of the free space on that filesystem.
• PRAGMA database_size reports the database's own usage, not the query's.
• An ORDER BY without LIMIT sorts everything. Add the limit if you only want the top rows.
• approx_count_distinct uses far less memory than exact count(distinct) on high-cardinality columns.

Internal links #

• DuckDB spatial explained: a spatial database that is just a file — the engine
• Fixing DuckDB out of memory on a large spatial query — the failure and its fixes
• How to aggregate millions of points into a grid with DuckDB — where group cardinality matters
• Columnar or row storage: why DuckDB is fast on wide tables — vectorised execution
• Fixing memory errors in GeoPandas with large files — the other engine's version
• Fixing a batch job whose memory grows — the pipeline-level problem
• How to process a large GeoPackage in chunks — chunking by hand
• How to benchmark DuckDB against GeoPandas honestly — measuring memory as well as time

FAQ #

How much memory does DuckDB need? #

Far less than an in-memory library, and it depends on the operator state rather than the input. A 13.5-million-row join and aggregate peaked at 305 MB; GeoPandas needed 4,739 MB for the same result.

What does memory_limit actually limit? #

The buffer manager — hash tables, sort buffers, cached blocks. Result sets converted to pandas and Python objects are outside it.

Why did setting a lower limit not slow my query down? #

Because the query was already streaming. Measured, the same join ran in 4.27 s unconstrained and 4.18 s with a 200 MB limit, because it never needed more.

What happens when a query exceeds the limit? #

It spills to temp_directory if one is set, and raises Out of Memory Error if not. Setting a spill directory turns hard failures into slow successes.

Which operations use the most memory? #

Blocking ones: GROUP BY (one entry per group), ORDER BY without a limit, the build side of a hash join, and DISTINCT. Filters and projections stream.

How do I make a GROUP BY fit? #

Reduce the number of groups. A 0.02° grid over 13.5 million points produced 6.4 million groups; a 1° grid produced 23,422 — the same query shape with a hundredth of the memory.