Avoid concatenating record batches in joins to alleviate memory pressure

[maxburke]

Is your feature request related to a problem or challenge?

Many of the joins (such as HashJoin) concatenate input record batches into a single record batch for ease of index accounting, but this causes huge memory pressure issues when the datasets are large.

Describe the solution you'd like

If the joins were able to preserve the original record batches without concatenation, this would help alleviate OOMs on large queries.

Describe alternatives you've considered

No response

Additional context

No response

[2010YOUY01]

I can confirm concat_batches is not memory-efficient -- it holds the input batches in memory while building the large output batch, the consequence would be 2X memory usage comparing with all input batches memory size.

Ideally we can make concat_batches more memory efficient, by building a in-progress builder for output, and append input batches to it one by one. After one input batch is finished, eagerly drop it.

Because the concatenated array inside HJ is both simpler and faster, so I prefer to keep this design, and we could improve concat_batches instead.

[maxburke]

Whether or not the core of concat_batches is efficient, it will always double the memory consumed as long as it takes batches by reference.

I understand the desire to not complicate the joins, but also realize the implication on performance of duplicating a large amount of input data.

Making concat_batches more efficient also does not solve the problem of overflowing offsets when operating on large numbers of input records; in my case the input has 110m rows and it's erroring out when trying to create a single StringArray.

[2010YOUY01]

Whether or not the core of concat_batches is efficient, it will always double the memory consumed as long as it takes batches by reference.

I think they don't have to take arg by reference. I'll give it a try shortly. If it doesn't work out, we can continue with this approach.

Making concat_batches more efficient also does not solve the problem of overflowing offsets when operating on large numbers of input records; in my case the input has 110m rows and it's erroring out when trying to create a single StringArray.

StringViewArray perhaps? They won't have the offset overflow issue, and also is more efficient in most cases.

To consistently solve the StringArray offset overflow issue, we would need to avoid concatenation in all operators, which requires fixing at least the sort and window operators as well.

An alternative is to convert StringArray to StringViewArray in places that require concatenation. Or provide better error messages when there is large StringArray concatenation and the offsets might overflow, to suggest users to use StringViewArray/LargeStringArray instead.

There is likely some tradeoff here, and I’m not fully convinced yet that we should avoid concatenation entirely.

[maxburke]

FWIW this issue was the tracking issue created for my PR that does this job: #23032

Which we are running in production and has lead to an 8-fold reduction in peak RSS during the same point in the query where we were previously OOM'ing.

[2010YOUY01]

Do you have a reproducer or explanation for the 8x RSS drop? I’d expect concat_batches to account for only ~2x, so the extra gap would be very helpful to understand. Thanks!