Streaming result buffers the entire result in memory when rows contain large VARCHAR values
- 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
Assessment
This issue has not been assessed yet.