duckdb / duckdb/duckdb-python

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

Abierto
#617 2 comentarios 0 reacciones 0 asignados Ver en GitHub
needs triage
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

Abrir la 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

Recibe los nuevos issues en tu correo

Un resumen breve de issues de GitHub para principiantes.