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

[zzl221000]

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:

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?

• Yes, I have

Did you include all relevant configuration to reproduce the issue?

• Yes, I have

[paultiq]

Looks like it's fixed in dev, possibly from #495

duckdb 1.6.0.dev379
A: 1 varchar x 50KB/row (~800MB)         reader_created=0.22GB rows=16000
B: 20 varchar x 2.5KB/row (~800MB)       reader_created=0.24GB rows=16000
C: 250KB rows (~3.8GB)                   reader_created=0.62GB rows=16000
D: 2.5KB rows (~3.8GB)                   reader_created=0.16GB rows=1600000

duckdb 1.5.5
A: 1 varchar x 50KB/row (~800MB)         reader_created=0.84GB rows=16000
B: 20 varchar x 2.5KB/row (~800MB)       reader_created=0.29GB rows=16000
C: 250KB rows (~3.8GB)                   reader_created=3.83GB rows=16000
D: 2.5KB rows (~3.8GB)                   reader_created=0.20GB rows=1600000

[zzl221000]

@paultiq Thanks for the heads-up.
My project depends on zipfs, and there's no 1.6 release of the extension yet — so the version mismatch blocks me from upgrading to dev. Happy to verify the fix once it's in a stable release, and I'll close this issue then.