duckdb / duckdb/duckdb-python

Streaming result buffers the entire result in memory when rows contain large VARCHAR values

Open
#617 2 comments 0 reactions 0 assignees View on GitHub
needs triage
Dominant language
Python
Stars
187
Forks
112
Avg merge
13h 29m
Merged PRs (30d)
17

Description

### What happens?

Consuming a streaming result via `con.sql(q).to_arrow_reader(batch_size)` (STREAM_RESULT -> ArrowArrayStream) buffers the **entire result in memory at reader creation** when the rows contain large VARCHAR values: RSS jumps before the first batch is consumed, and only drops as the result is consumed. The same total number of bytes with narrow rows streams normally. `streaming_buffer_size`, `memory_limit`, `threads` and `preserve_insertion_order` do not bound it.

### To Reproduce

Requires `pip install duckdb==1.5.5 psutil`:

```python
import gc
import duckdb
import psutil

def rss_gb():
return psutil.Process().memory_info().rss / 2**30

def probe(label, query):
con = duckdb.connect()
rel = con.sql(query)
reader = rel.to_arrow_reader(1000) # batch_size = 1000
created = rss_gb() # RSS right after reader creation, before consuming anything
rows = sum(batch.num_rows for batch in reader)
print(f"{label:40s} reader_created={created:.2f}GB rows={rows}")
del reader, rel, con
gc.collect()

print("duckdb", duckdb.__version__)
n = 16000
# A/B: same total bytes (~800MB), same row count; only the number of VARCHAR values differs
probe("A: 1 varchar x 50KB/row (~800MB)",
f"SELECT i, repeat('x', 50000) AS s FROM range({n}) tbl(i)")
cols = ", ".join(f"repeat('x', 2500) AS s{i}" for i in range(20))
probe("B: 20 varchar x 2.5KB/row (~800MB)",
f"SELECT i, {cols} FROM range({n}) tbl(i)")
# C/D: same total bytes (~3.8GB), different row width
probe("C: 250KB rows (~3.8GB)",
"SELECT i, repeat('x', 250000) AS s FROM range(16000) tbl(i)")
probe("D: 2.5KB rows (~3.8GB)",
"SELECT i, repeat('x', 2500) AS s FROM range(1600000) tbl(i)")
```

Output (Windows 11 x64; duckdb 1.5.5, also reproduced on 1.4.3):

```
duckdb 1.5.5
A: 1 varchar x 50KB/row (~800MB) reader_created=0.81GB rows=16000
B: 20 varchar x 2.5KB/row (~800MB) reader_created=0.25GB rows=16000
C: 250KB rows (~3.8GB) reader_created=3.79GB rows=16000
D: 2.5KB rows (~3.8GB) reader_created=0.16GB rows=1600000
```

A buffers the whole ~800MB result at creation; B has identical total bytes but streams. Likewise C buffers ~3.8GB while D streams.

Adding `SET streaming_buffer_size='1MiB'`, `SET threads=1`, `SET preserve_insertion_order=false` or `SET memory_limit='1GB'` does not change A or C (C peaks at ~4.27GB, above the 1GB memory limit).

### OS:

Windows 11 x64 (repro); production also observed on Linux

### DuckDB Package Version:

v1.5.5 (also reproduced on v1.4.3)

### Python Version:

3.12

### Full Name:

Jim Zhang

### Affiliation:

Winhc

### What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.

I have tested with a stable release

### Did you include all relevant data sets for reproducing the issue?

Yes

### Did you include all code required to reproduce the issue?

- [x] Yes, I have

### Did you include all relevant configuration to reproduce the issue?

- [x] Yes, I have

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.