DuckDB's Memory Model: Streaming, Spilling and Limits
On this page
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,472,195 GeoNames points to 4,596 provinces and counting them per country (Example 1):
time peak RSS
DuckDB, no limit 55.8 s 287 MB
DuckDB, limit 1 GB 56.7 s 282 MB
GeoPandas 121.8 s 23,813 MB
and on a smaller job, 2,241,404 US points against 51 states:
DuckDB, no limit 3.77 s 181 MB
DuckDB, limit 500 MB 4.05 s 181 MB
DuckDB, limit 200 MB 3.81 s 181 MB
GeoPandas 13.83 s 932 MB
The GeoPandas rows are the same joins measured on the benchmark page, grouped by province there, on the same data.
Setting a limit 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 = 'duckdb_spill'") # where it spills; default '.tmp'
con.execute("set max_temp_directory_size = '50GB'")
con.execute("set threads = 8") # each thread has a working setprint(con.execute("""
select current_setting('memory_limit') as memory_limit,
current_setting('threads') as threads,
current_setting('temp_directory') as temp_dir
""").df()) memory_limit threads temp_dir
0 3.7 GiB 8 duckdb_spill
'4GB' is four billion bytes, which DuckDB reports back in binary units as 3.7 GiB.
If a query exceeds the limit and cannot spill, the error is explicit — this one is from Example 1, a fine-grid GROUP BY given 100 MB:
Out of Memory Error: could not allocate block of size 256.0 KiB (95.4 MiB/95.3 MiB used)
Streaming and blocking operators, limits and spilling
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 groupORDER 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. Joining 13.5 million rows to 4,596 polygons and aggregating them into 251 country groups peaked at 287 MB because the group table has that many 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. An in-memory connection defaults to .tmp in the working directory; setting it to an empty string turns spilling off, and then the query fails instead:
con.execute("set temp_directory = 'duckdb_spill'")
con.execute("set max_temp_directory_size = '100GB'")
print(con.execute("select current_setting('max_temp_directory_size')").fetchone()[0])93.1 GiB
The same unit conversion again: 100 GB is 93.1 GiB. Left alone, the setting reads 90% of available disk space.
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 (95.4 MiB/95.3 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 BYwith millions of groups — is the grouping key too fine? Rounding coordinates to a coarser grid reduces groups by orders of magnitude.ORDER BYon a full result — add aLIMITso the sort keeps only the top rows.- A hash join whose build side is huge — swap the sides, or filter the build side first.
DISTINCTover many columns — distinct on fewer columns, or approximate withapprox_count_distinct.
6. Watch the thread count
Threads multiply working sets: each has its own buffers for sorting and hashing. Measured in Example 1 on a 24-thread machine, the fine-grid GROUP BY failed under a 100 MB limit with the default thread count and completed in 0.34 s with threads = 4.
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.
Profiling a query's memory on GeoNames
The data used on this page
The points are GeoNames' full allCountries dump — every feature, about 13.5 million, rebuilt daily at download.geonames.org, so counts drift a little between runs — kept both as the 1.79 GB text file and as places.parquet (also linked as geonames.parquet) with name, latitude, longitude, feature class, country and population. The polygons are Natural Earth's 1:10m admin-1 states and provinces, 4,596 of them, as provinces.shp.
import os
import zipfile
import duckdb
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)
fetch("https://download.geonames.org/export/dump/allCountries.zip", "allCountries.zip") # about 420 MB
if not os.path.exists("allCountries.txt"):
zipfile.ZipFile("allCountries.zip").extract("allCountries.txt")
if not os.path.exists("places.parquet"):
duckdb.sql("""
copy (select column01 as name, column04 as lat, column05 as lon, column06 as feature_class,
column08 as country, column14 as population
from read_csv('allCountries.txt', delim = '\\t', header = false, quote = '',
all_varchar = false, sample_size = -1))
to 'places.parquet' (format parquet)""")
if not os.path.exists("geonames.parquet"):
os.link("places.parquet", "geonames.parquet")
fetch("https://naciscdn.org/naturalearth/10m/cultural/ne_10m_admin_1_states_provinces.zip", "provinces.zip")
if not os.path.exists("provinces.shp"):
with zipfile.ZipFile("provinces.zip") as z:
for member in z.namelist():
if member.startswith("ne_10m_admin_1_states_provinces."):
open("provinces" + os.path.splitext(member)[1], "wb").write(z.read(member))
n = duckdb.sql("select count(*) from read_parquet('places.parquet')").fetchone()[0]
print(f"{n:,} GeoNames points; places.parquet {os.path.getsize('places.parquet') / 1e6:.0f} MB, "
f"allCountries.txt {os.path.getsize('allCountries.txt') / 1e9:.2f} GB")13,472,195 GeoNames points; places.parquet 306 MB, allCountries.txt 1.79 GB
The smaller job uses the 2.24 million GeoNames points in the United States against the 51 US states (with the District of Columbia) cut from the same provinces file.
import geopandas as gpd
if not os.path.exists("us_points.parquet"):
duckdb.sql("copy (select * from read_parquet('places.parquet') where country = 'US') "
"to 'us_points.parquet' (format parquet)")
if not os.path.exists("us_states.gpkg"):
provinces = gpd.read_file("provinces.shp")
provinces[provinces.adm0_a3 == "USA"][["name", "geometry"]].to_file("us_states.gpkg")
n_points = duckdb.sql("select count(*) from read_parquet('us_points.parquet')").fetchone()[0]
print(f"US points: {n_points:,}; US states: {len(gpd.read_file('us_states.gpkg'))}")US points: 2,241,404; US states: 51
Example 1 — profiling a query's memory behaviour
Each measurement runs in a fresh process. The peak resident size a process reports (ru_maxrss, or VmHWM in /proc/self/status on Linux) only ever rises, so two queries measured in one process report the larger of the two for both.
import json
import subprocess
import sys
PROBE = """
import duckdb, json, sys, time
sql, settings = sys.argv[1], json.loads(sys.argv[2])
con = duckdb.connect()
con.execute("install spatial; load spatial; set enable_progress_bar = false")
for name, value in settings.items():
con.execute(f"set {name} = '{value}'")
started = time.perf_counter()
try:
rows = con.execute(sql).fetchall()
status = f"{len(rows):,} rows"
except duckdb.OutOfMemoryException as exc:
status = "OOM: " + str(exc).splitlines()[0].replace("Out of Memory Error: ", "")
elapsed = time.perf_counter() - started
peak_kb = next(line for line in open("/proc/self/status") if line.startswith("VmHWM")).split()[1]
print(json.dumps({"secs": elapsed, "peak_mb": int(peak_kb) / 1024, "status": status}))
"""
def profile_query(sql, **settings):
"""Time and peak RSS of one query in a fresh process, with the settings that produced them."""
out = subprocess.run([sys.executable, "-c", PROBE, sql, json.dumps(settings)],
capture_output=True, text=True, check=True)
result = json.loads(out.stdout.strip().splitlines()[-1])
label = ", ".join(f"{k} {v or repr(v)}" for k, v in settings.items()) or "defaults"
print(f"{label:46} {result['secs']:6.2f}s peak RSS {result['peak_mb']:6,.0f} MB {result['status']}")
return resultThree queries: every GeoNames point joined to the province that contains it and counted per country, the US points against the US states, and a 0.02° grid count with its result reduced to a single row so the fetch does not dominate.
GLOBAL_JOIN_SQL = """
select a.admin, count(*) from read_parquet('places.parquet') p
join st_read('provinces.shp') a on st_intersects(st_point(p.lon, p.lat), a.geom)
group by 1"""
US_JOIN_SQL = """
select a.name, count(*) from read_parquet('us_points.parquet') p
join st_read('us_states.gpkg') a on st_intersects(st_point(p.lon, p.lat), a.geom)
group by 1"""
FINE_GROUPS = """
select floor(lon / 0.02), floor(lat / 0.02), count(*) from read_parquet('places.parquet') group by 1, 2"""
FINE_GRID = f"select count(*) from ({FINE_GROUPS})"
print("global join, 13.5 million points x 4,596 provinces")
for limit in (None, "1GB"):
profile_query(GLOBAL_JOIN_SQL, **({"memory_limit": limit} if limit else {}))
print("\nUS join, 2.24 million points x 51 states")
for limit in (None, "500MB", "200MB"):
profile_query(US_JOIN_SQL, **({"memory_limit": limit} if limit else {}))global join, 13.5 million points x 4,596 provinces
defaults 55.83s peak RSS 287 MB 251 rows
memory_limit 1GB 56.68s peak RSS 282 MB 251 rows
US join, 2.24 million points x 51 states
defaults 3.77s peak RSS 181 MB 51 rows
memory_limit 500MB 4.05s peak RSS 181 MB 51 rows
memory_limit 200MB 3.81s peak RSS 181 MB 51 rows
Flat across every limit is the signature of a streaming query. The fine grid is different — it holds a hash table of millions of groups:
profile_query(FINE_GRID)
profile_query(FINE_GRID, memory_limit="200MB")
profile_query(FINE_GRID, memory_limit="100MB")
profile_query(FINE_GRID, memory_limit="100MB", threads=4)
profile_query(FINE_GRID, memory_limit="100MB", threads=4, temp_directory="")
profile_query(FINE_GROUPS, memory_limit="200MB") # fetch every group into Pythondefaults 0.18s peak RSS 526 MB 1 rows
memory_limit 200MB 0.23s peak RSS 431 MB 1 rows
memory_limit 100MB 0.02s peak RSS 172 MB OOM: could not allocate block of size 256.0 KiB (95.3 MiB/95.3 MiB used)
memory_limit 100MB, threads 4 0.34s peak RSS 260 MB 1 rows
memory_limit 100MB, threads 4, temp_directory '' 0.06s peak RSS 201 MB OOM: could not allocate block of size 256.0 KiB (95.1 MiB/95.3 MiB used)
memory_limit 200MB 1.71s peak RSS 1,412 MB 6,416,842 rows
Read down the column: the default run holds the whole hash table; 200 MB still finishes; 100 MB fails with 24 threads and succeeds with 4; the same run with spilling switched off fails; and fetching the 6.4 million groups into Python pushes the process to 1,412 MB against its 200 MB limit, because a result handed to Python is outside the buffer manager.
Peak RSS is the whole process, not the buffer pool: Python, DuckDB and the spatial extension account for about 200 MB before any state exists — the 1° grid in Example 3, with almost nothing to hold, peaked at 202 MB. That is why the 200 MB run could report 431 MB without breaking its limit.
Example 2 — finding the blocking operator
def explain_memory(con, sql, show_plan=False):
"""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",
}
if show_plan:
print(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")con = duckdb.connect()
con.execute("install spatial; load spatial;")
for label, sql in (("US join", US_JOIN_SQL), ("fine grid", FINE_GRID),
("filter only", "select name from read_parquet('places.parquet') where population > 1000000")):
print(label)
explain_memory(con, sql)US join
HASH_GROUP_BY holds one entry per group
SPATIAL_JOIN holds a spatial structure over one side
fine grid
HASH_GROUP_BY holds one entry per group
filter only
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 all)
""").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(" ! more than one group per ten rows means the key is nearly unique; "
"coarsen it or expect the hash table to hold most of the data")
return groupsSOURCE = "read_parquet('places.parquet')"
for size in (0.02, 0.1, 0.5, 1):
print(f"{size}° grid: ", end="")
group_cardinality(con, SOURCE, f"floor(lon / {size}), floor(lat / {size})")
profile_query(f"select count(*) from (select floor(lon / {size}), floor(lat / {size}), count(*) "
f"from {SOURCE} group by 1, 2)")0.02° grid: 13,472,195 rows → 6,416,842 groups (47.63% — memory is proportional to the groups)
! more than one group per ten rows means the key is nearly unique; coarsen it or expect the hash table to hold most of the data
defaults 0.19s peak RSS 522 MB 1 rows
0.1° grid: 13,472,195 rows → 961,927 groups (7.14% — memory is proportional to the groups)
defaults 0.09s peak RSS 271 MB 1 rows
0.5° grid: 13,472,195 rows → 71,990 groups (0.53% — memory is proportional to the groups)
defaults 0.06s peak RSS 202 MB 1 rows
1° grid: 13,472,195 rows → 23,460 groups (0.17% — memory is proportional to the groups)
defaults 0.07s peak RSS 202 MB 1 rows
A 0.02° grid produces 6.4 million groups — one for every two rows — and the process peaks at 522 MB. At 0.5° and coarser, the peak is the 202 MB the process needs anyway: the hash table has become too small to register. Same query shape, two entirely different memory profiles.
Memory follows the output, not the input
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 join peaked at 287 MB: 251 groups plus buffers, on top of the process's own 200 MB or so. 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 on the benchmark page, that peaked at 23,813 MB against DuckDB's 291 MB — 82×. 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 did not slow it down
In the measurements, the US join took 3.77 s unconstrained, 4.05 s at 500 MB and 3.81 s at 200 MB. That spread is noise: the query's state was far below every limit, so the limit never came into play. A limit only costs time once the state reaches it and the engine starts spilling — and it costs a failure, not time, once the state cannot be spilled or split.
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.
What the limit covers, threads and temp space
memory_limitcovers 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
threadsis a legitimate memory fix. temp_directorymust exist and be writable, or spilling fails.max_temp_directory_sizedefaults to 90% of the free space on that filesystem.PRAGMA database_sizereports the database's own usage, not the query's.- An
ORDER BYwithoutLIMITsorts everything. Add the limit if you only want the top rows. approx_count_distinctuses far less memory than exactcount(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
- stop_times.txt is too big to load comfortably — a real CSV where DuckDB's sniffer guesses wrong
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 287 MB; GeoPandas needed 23,813 MB for the same join.
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 3.77 s unconstrained and 3.81 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 and peaked at 522 MB; a 1° grid produced 23,460 and peaked at 202 MB, which is what the process needs with no state at all.