duckdb / duckdb/duckdb-python

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

Ouverte
#617 2 commentaires 0 réactions 0 personnes assignées Voir sur GitHub
needs triage
Langage dominant
Python
Étoiles
186
Forks
113
Merge moyen
13 h 29 min
PR mergées (30 j)
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

Guide de contribution

Ouvrir le guide de contribution

Piste de recherche

Exécutez la reproduction Python fournie avec duckdb 1.5.5 et comparez le RSS lors de la création du reader pour les requêtes VARCHAR étroites et larges. Commencez par con.sql(q).to_arrow_reader(1000) et son chemin de STREAM_RESULT vers ArrowArrayStream ; le travail est terminé lorsque les résultats VARCHAR volumineux ne mettent plus en tampon le résultat complet avant le premier batch, tandis que la reproduction consomme toujours toutes les lignes.

Rédigé par le modèle d'indexation à partir du texte de l'issue.

Évaluation

Stack technique
python
Domaine
backend, databases
Type d'issue
Bug
Difficulté
4/5
Temps estimé
3-5 jours
Activité
Active
Clarté
Plutôt claire
Accessibilité débutants
52/100

Recevez les nouvelles issues par e-mail

Un résumé court des issues GitHub adaptées aux débutants.