[Bug] BinaryView/StringView columns are spilled without GC and results in enormous spill files

[bharath-techie]

Describe the bug

Running clickbench queries q12 and q33 with low memory [ 4 GB ] and single target partition , found that spill file was taking more than 100 GB and resources exhaustion error was thrown.

SELECT URL, COUNT(*) AS c FROM hits GROUP BY URL ORDER BY c DESC LIMIT 10;

On further investigation , found that for each sliced record batch , we write the entire original array's buffer as the string view array was not GC'd.

To Reproduce

Take one clickbench partitioned parquet file ~120 mb :

RUST_LOG=datafusion_physical_plan=debug ./datafusion-cli -m 40m --disk-spill-path /home/ec2-user/spilldir --disk-limit 75g

SET datafusion.execution.listing_table_ignore_subdirectory = false;
SET datafusion.execution.target_partitions=1;
SET datafusion.execution.parquet.binary_as_string=true;
CREATE EXTERNAL TABLE hits 
STORED AS PARQUET 
LOCATION '/home/ec2-user/hits_0.parquet';
SELECT "URL", COUNT(*) AS c FROM hits GROUP BY "URL" ORDER BY c DESC LIMIT 10;


[2025-12-19T17:31:48Z DEBUG datafusion_physical_plan::spill::in_progress_spill_file] [SPILL_FILE]
 Finished spill file: path="/var/folders/xx/b0vrbhv96tl6y9l2xzwr3twh0000gq/T/.tmpfswXa6/.tmpKujQH8", size=820.56 MB, total_spilled_bytes=820.56 MB, total_spill_files=1

Resources exhausted: Additional allocation failed for GroupedHashAggregateStream[0] (count(1)) with top memory consumers (across reservations) as:
  GroupedHashAggregateStream[0] (count(1))#5(can spill: true) consumed 16.4 KB, peak 39.5 MB,
  DataFusion-Cli#4(can spill: false) consumed 0.0 B, peak 0.0 B,
  TopK[0]#6(can spill: false) consumed 0.0 B, peak 0.0 B.
Error: Failed to allocate additional 60.3 MB for GroupedHashAggregateStream[0] (count(1)) with 0.0 B already 
allocated for this reservation - 40.0 MB remain available for the total pool

size=820.56 MB, total_spilled_bytes=820.56 MB, total_spill_files=1 820 MB was getting written to spill file.

Expected behavior

After GC'ing the stringView arrays , writing the compacted record batch resulted in

2025-12-19T17:33:34Z DEBUG datafusion_physical_plan::spill::in_progress_spill_file] [SPILL_FILE] Finished spill file: path="/var/folders/xx/b0vrbhv96tl6y9l2xzwr3twh0000gq/T/.tmprkn7C5/.tmptxEvdP", size=33.43 MB, total_spilled_bytes=33.43 MB, total_spill_files=1
+--------------------------------------------------------------------------+-------+
| URL                                                                      | c     |
+--------------------------------------------------------------------------+-------+
| http://irr.ru/index.php?showalbum/login-leniya7777294,938303130          | 58976 |
| http://komme%2F27.0.1453.116                                             | 29585 |
| https://produkty%2Fproduct                                               | 11464 |
| http://irr.ru/index.php?showalbum/login-kapusta-advert2668]=0&order_by=0 | 10480 |
| http://irr.ru/index.php?showalbum/login-kapustic/product_name            | 10128 |
| http://irr.ru/index.php                                                  | 7758  |
| https://produkty%2F                                                      | 6649  |
| http://irr.ru/index.php?showalbum/login                                  | 6141  |
| https://produkty/kurortmag                                               | 5764  |
| https://produkty%2Fpulove.ru/album/login                                 | 5495  |
+--------------------------------------------------------------------------+-------+
10 row(s) fetched. 
Elapsed 0.223 seconds.

size=33.43 MB, total_spilled_bytes=33.43 MB, total_spill_files=1 33.4 MB was written to spill file.

Need to write GC'd string view arrays to spill files. [ Unless there are cases where this will not efficient ? ]

Additional context

No response

[bharath-techie]

From past issues / reviews tagging relevant folks : @2010YOUY01 @ding-young @alamb

I'm trying to figure out few things - will love to get some feedback from community :

• Are there scenarios/flows where we GC and spill ? I haven't tested external sort for example
• Is there a way to figure out that the incoming array is already GC'd ?
• Also , does GC'ing always before spill makes sense ? are there scenarios where GC-ing is inefficient.

I was testing this issue with this commit that performs GC during spill for reference - bharath-techie@f441237

[alamb]

Are there scenarios/flows where we GC and spill ? I haven't tested external sort for example
Is there a way to figure out that the incoming array is already GC'd ?

Internally, the operations that do take/filter do the GC'ing internally -- specifically this the coalescer in arrow has a bunch of heuristics about when to GC a string view: https://docs.rs/arrow/latest/arrow/compute/struct.BatchCoalescer.html

Specifically I think this is the heuristic: https://github.com/apache/arrow-rs/blob/240cbf4f838387445b0209db4b14dbb277b05a12/arrow-select/src/coalesce/byte_view.rs#L286-L295

Also , does GC'ing always before spill makes sense ? are there scenarios where GC-ing is inefficient.

GCing will copy all the referenced strings so if there isn't a large amount of "dead" data it may be faster just to write it all and read it all back.

When writing to a spill file, I do think it would make sense to GC all the strings if the load factor is small (see heuristic above)

On further investigation , found that for each sliced record batch , we write the entire original array's buffer as the string view array was not GC'd.

Yeah, this sounds non ideal to me

I was testing this issue with this commit that performs GC during spill for reference - bharath-techie@f441237

That change looks reasonable to me

[adriangb]

I think it would be reasonable to always GC while spilling, at least if some heuristic matches.

[haohuaijin]

I think it would be reasonable to always GC while spilling, at least if some heuristic matches.

agree
I have encountered a similar problem like this issue when using Arrow Flight to send StringViewArray.

[2010YOUY01]

I agree that we should always GC when spilling.

Avoid doing so if the batch is already compact is a neat idea, however this optimization might be a bit challenging, I was imaging this can be stored as a property (we already have column level properties, but don't have batch level properties yet), and figure out how to propagate this property in relevant operators.

[EeshanBembi]

Hey @bharath-techie
I've opened PR #19444 to address this issue. The fix adds garbage collection for StringView/BinaryView arrays before spilling to disk, which reduces spill file sizes by ~96% (820MB → 33MB) as reported.

The implementation:

• Performs GC on StringView/BinaryView columns in InProgressSpillFile::append_batch() before writing
• Skips GC for small arrays (<10 rows) and when no buffers need compaction(10 rows is an arbitrary number and can be changed)
• Includes comprehensive tests including a specific reproduction of this ClickBench issue(which could be removed/modified)

The approach aligns with @alamb's suggestion to GC during spill when the waste ratio is high. Currently using a simple heuristic (any buffers present + >10 rows), but this could be refined in follow-up PRs to use more sophisticated waste ratio calculations similar to Arrow's BatchCoalescer.

[bharath-techie]

Thanks @EeshanBembi for raising the PR. :)