Streaming result buffers the entire result in memory when rows contain large VARCHAR values
- Lenguaje dominante
- Python
- Estrellas
- 187
- Forks
- 112
- Merge medio
- 13 h 29 min
- PR fusionados (30 d)
- 17
Descripción
### 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
Guía de contribución
Línea de trabajo
Ejecute la reproducción de Python proporcionada con duckdb 1.5.5 y compare el RSS durante la creación del reader para las consultas con VARCHAR estrecho y ancho. Comience en con.sql(q).to_arrow_reader(1000) y su ruta de STREAM_RESULT a ArrowArrayStream; el trabajo estará terminado cuando los resultados VARCHAR grandes ya no almacenen en búfer el resultado completo antes del primer batch, mientras la reproducción siga consumiendo todas las filas.
Escrito por el modelo de indexación a partir del texto del issue.
Evaluación
- Stack tecnológico
- python
- Área
- backend, databases
- Tipo de issue
- Error
- Dificultad
- 4/5
- Tiempo estimado
- 3-5 días
- Estado de actividad
- Activo
- Claridad
- Bastante claro
- Aptitud para principiantes
- 52/100