Arrow pycapsules use 2x the memory of fetch_arrow_table

[paultiq]

What happens?

DuckDBPyRelation::ToArrowCapsule consumes 2x the memory of ToArrowTableInternal. It fully materializes the results before constructing the arrow table, not leveraging a PhysicalArrowCollector.

edit 1: the ADBC driver has the same behavior: ...adbc... cur.execute(query).fetch_arrow_table() consumes more than 2x the memory of conn.sql(query).fetch_arrow_table().

Some rough test results (edit: for a 1GB Arrow Table):

Test	Time	Peak	Table Size
Unchanged - ExecuteOrThrow();	5.818s	2.36GB	1.08GB
Streaming - ExecuteOrThrow(true);	4.682s	1.22GB	1.08GB
PhysicalArrowCollector + Stream (code not shown)	3.937s	1.23GB	1.08GB
FetchArrowTable (see ToArrowTableInternal)	4.447s	1.17GB	1.08GB

Streaming

The simplest change is changing the materialization to streaming - ExecuteOrThrow(true);, this alone halves the memory consumption.

duckdb-python/src/duckdb_py/pyrelation.cpp

Line 967 in bbd4389

ExecuteOrThrow();

PhysicalArrowCollector

A more complete change would be to refactor ToArrowCapsule and ToArrowTableInternal to both use common PhysicalArrowCollector code. A light wrapper to construct an ArrowArrayStream would be needed in DuckDBPyResult::FetchArrowCapsule.

Related optimization - ToPolars

I noticed this when looking at ToPolars memory consumption in duckdb/duckdb#19356. If the above issue is solved, then Polars dataframe construction can be done directly without the ToArrowTableInternal step at all.

A simple change like the below would significantly improve memory and performance:

duckdb-python/src/duckdb_py/pyrelation.cpp

Lines 976 to 979 in bbd4389

	if (!lazy) {
	auto arrow = ToArrowTableInternal(batch_size, true);
	return py::cast<PolarsDataFrame>(pybind11::module_::import("polars").attr("DataFrame")(arrow));
	}

=>

		auto polars_module = pybind11::module_::import("polars");
		return py::cast<PolarsDataFrame>(polars_module.attr("DataFrame")(*this));

To Reproduce

import os
import time
from resource import getrusage, RUSAGE_SELF
import duckdb
import pyarrow as pa

query = "SELECT r, r||'foo' AS foo FROM range(100000000) t(r) WHERE r % 2 = 0"

start = time.perf_counter()
rel = duckdb.sql(query)
table = pa.table(rel)  # Direct import from Arrow C Stream capsule
elapsed = time.perf_counter() - start

peak_gb = getrusage(RUSAGE_SELF).ru_maxrss * 1024 / (1024**3)
table_gb = table.nbytes / (1024**3)

print(f"Time: {elapsed:.3f}s  Peak: {peak_gb:.2f}GB  Table: {table_gb:.2f}GB")

OS:

Linux

DuckDB Package Version:

1.4.1, 1.5.0+dev110

Python Version:

3.13

Full Name:

Paul T

Affiliation:

Iqmo

What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.

I have tested with a source build

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

[kylebarron]

I think this is related to what we plan to change in DataFusion: apache/datafusion-python#1206, apache/datafusion-python#1222, to ensure that the PyCapsule stream isn't fully materialized before returning

[paultiq]

... ensure that the PyCapsule stream isn't fully materialized before returning

My original issue here was to point out that the current implementation is inefficient: That if rel.__arrow_c_stream__() is going to fully materialize the data, it should do so more efficiently and not use 2x the memory compared to rel.fetch_arrow_table().

You're asking, I think, a slightly different question: Why is ToArrowCapsule materializing at all anyway? Why doesn't it stream like rel.arrow() / rel.fetch_record_batch()?

Do I understand you correctly?

[kylebarron]

Ah yes I see. We're talking about slightly different things. Either way would be an improvement on the current behavior

[evertlammerts]

#342 makes sure that PhysicalArrowCollector is used when consuming a relation as an arrow stream. There's no more conversion between duckdb and arrow on that path.

@paultiq How did you test PhysicalArrowCollector + Stream? PhysicalArrowCollector eagerly materializes, and by the time we call GetResult() everything is already materialized... I think. Did you make changes to core for this? In my tests I see double resident memory size for rel.pl() / pl.DataFrame(rel).

Re ToPolars, this is now also going through PhysicalArrowCollector so it benefits as well. I'm not changing it to skip ToArrowTableInternal, since that does (for better or worse) column name deduplication.

Quick benchmark

Test script:

"""
Usage: /usr/bin/time -l python sandbox/bench_polars.py <test_name>

Tests: polars_eager, polars_capsule, arrow_table, to_arrow
"""
import sys
import duckdb

query = "SELECT r, r||'foo' AS foo FROM range(100000000) t(r) WHERE r % 2 = 0"

tests = {
    "polars_eager": lambda rel: rel.pl(),
    "polars_capsule": lambda rel: __import__("polars").DataFrame(rel),
    "arrow_table": lambda rel: __import__("pyarrow").table(rel),
    "to_arrow": lambda rel: rel.to_arrow_table(),
}

if __name__ == "__main__":
    name = sys.argv[1] if len(sys.argv) > 1 else None
    if name not in tests:
        print(f"Usage: {sys.argv[0]} <{'|'.join(tests)}>")
        sys.exit(1)
    result = tests[name](duckdb.sql(query))

Results:

System: Apple M3, 32 GB RAM
Dataset size: ~1.08GB

polars_eager: RSS 2.12GB, time 2.43s

        4.05 real         2.06 user         0.37 sys
          2124349440  maximum resident set size
                   0  average shared memory size
                   0  average unshared data size
                   0  average unshared stack size
              131026  page reclaims
                  75  page faults
                   0  swaps
                   0  block input operations
                   0  block output operations
                   0  messages sent
                   0  messages received
                   0  signals received
                 503  voluntary context switches
                1805  involuntary context switches
         37941992208  instructions retired
          8093974037  cycles elapsed
          1758566976  peak memory footprint

polars_capsule: RSS 2.07GB, time 3.95s

        2.22 real         3.62 user         0.33 sys
          2070528000  maximum resident set size
                   0  average shared memory size
                   0  average unshared data size
                   0  average unshared stack size
              127783  page reclaims
                  40  page faults
                   0  swaps
                   0  block input operations
                   0  block output operations
                   0  messages sent
                   0  messages received
                   0  signals received
                   0  voluntary context switches
                2556  involuntary context switches
         75449361263  instructions retired
         13356278193  cycles elapsed
          1795988160  peak memory footprint

arrow_table: RSS 0.98GB, time 4.64s

       10.68 real         4.23 user         0.41 sys
           977649664  maximum resident set size
                   0  average shared memory size
                   0  average unshared data size
                   0  average unshared stack size
               65822  page reclaims
                 254  page faults
                   0  swaps
                   0  block input operations
                   0  block output operations
                   0  messages sent
                   0  messages received
                   0  signals received
                 596  voluntary context switches
                2354  involuntary context switches
         80803148524  instructions retired
         15362501815  cycles elapsed
           605787136  peak memory footprint

to_arrow: RSS 0.79GB, time 1.94s

``` 1.96 real 1.72 user 0.22 sys 788922368 maximum resident set size 0 average shared memory size 0 average unshared data size 0 average unshared stack size 49805 page reclaims 41 page faults 0 swaps 0 block input operations 0 block output operations 0 messages sent 0 messages received 0 signals received 3 voluntary context switches 616 involuntary context switches 32725055522 instructions retired 6395497721 cycles elapsed 479564288 peak memory footprint ```

[paultiq]

How did you test PhysicalArrowCollector + Stream?

It's been a bit since I last looked at this, but I did a bit of surgery (hacking) to make this work:

What I mentioned above isn't "true" streaming. All I meant was materializing everything in the PhysicalArrowCollector and returning results via an ArrowArrayStreamWrapper... so that it's a streaming result to the client's __arrow_c_stream__ rather than returned as a table.

That said, after this, I did implement an experiment with a "true" streaming approach that uses ArrowUtil::FetchChunk(): https://github.com/iqmo-org/bareduckdb/blob/b6154a057c51f48a59d6ab3b13dcd45415e4ab70/src/bareduckdb/core/impl/cpp_helpers.hpp#L558C12-L558C44

[evertlammerts]

Ah, got it. I'd have expected memory to double then, which is what I'm seeing as well.

ArrowUtil::FetchChunk() makes a lot of sense. Going on my list. Thanks!