Repository navigation
[Bug] BinaryView/StringView columns are spilled without GC and results in enormous spill files #19414
Description
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
Activity
bharath-techie commentedon Dec 19, 2025
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 :
I was testing this issue with this commit that performs GC during spill for reference - bharath-techie@f441237
alamb commentedon Dec 19, 2025
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
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)
Yeah, this sounds non ideal to me
That change looks reasonable to me
adriangb commentedon Dec 20, 2025
I think it would be reasonable to always GC while spilling, at least if some heuristic matches.
haohuaijin commentedon Dec 20, 2025
agree
I have encountered a similar problem like this issue when using Arrow Flight to send StringViewArray.
Fix massive spill files for StringView/BinaryView columns
Fix massive spill files for StringView/BinaryView columns
2010YOUY01 commentedon Dec 22, 2025
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.
Fix massive spill files for StringView/BinaryView columns
Fix massive spill files for StringView/BinaryView columns
EeshanBembi commentedon Dec 22, 2025
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:
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 commentedon Dec 22, 2025
Thanks @EeshanBembi for raising the PR. :)
Fix massive spill files for StringView/BinaryView columns
Fix massive spill files for StringView/BinaryView columns
Fix massive spill files for StringView/BinaryView columns
Fix massive spill files for StringView/BinaryView columns
Fix massive spill files for StringView/BinaryView columns II (#21633)
Fix massive spill files for StringView/BinaryView columns II (apache#…