[EPIC] A collection of tickets for improving sorting larger than memory datasets / spilling sorts

@2010YOUY01

There is a lot of work to support plans like SELECT * .. ORDER BY x,y,z when that data doesn't all fit in RAM.

I believe the core usecase is "compaction" where multiple small files are resorted/merged/written as larger fiiles

FYI @2010YOUY01 and @Kontinuation I was trying to find some other organizing ticket for improvements in this area and I could not, so I filed this one

more generic: [Epic] A collection of items related to processing larger than memory datasets (via spilling, externalized algorithm, etc) #14077
Make DiskManagerBuilder to construct DiskManagers #15319

@comphead

I also think that by collecting the related items we may be able to find some more review capacity (as I think this is an important capability for Spark / Comet. FYI @comphead / @andygrove / @kazuyukitanimura )

I filed some more tickets about ways to improve performance with external spill files

I added #15323

@alamb

@alamb Thank you for summarizing, I'm also interested in this topic and may have more time to join the game in May, but I will keep an eye on the progress.

Add resource limits to datafusion config.

I saw while debugging some performance issue in AggregateExec I see that we keep all spilled files open (RefCountedTempFile as it keep tempfile which hold File).

and also when merging we read at least 1 batch from every spill file:

datafusion/datafusion/physical-plan/src/aggregates/row_hash.rs

Lines 1059 to 1062 in 7317198

    
           for spill in self.spill_state.spills.drain(..) { 
        
               let stream = self.spill_state.spill_manager.read_spill_as_stream(spill)?; 
        
               streams.push(stream); 
        
           }

so If I have a lot of spill files or if every batch is really huge (contains very large lists - like result for array_agg on large dataset) we have all of this in memory.

so If I have a lot of spill files or if every batch is really huge (contains very large lists - like result for array_agg on large dataset) we have all of this in memory.

I think this is the same underlying cause as

Reduce number of tokio blocking threads in SortExec spill #15323

I think most of this epic is done and DataFusion now has a very solid spilling sort implementation 🎉

To make the current state clear, I moved the issues that are not currently completed to a new epic and will close this one

[EPIC] Additional improvements to larger than memory / spilling sorts #17593

Navigation Menu

Repository navigation

[EPIC] A collection of tickets for improving sorting larger than memory datasets / spilling sorts #15271

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

	for spill in self.spill_state.spills.drain(..) {
	let stream = self.spill_state.spill_manager.read_spill_as_stream(spill)?;
	streams.push(stream);
	}

[EPIC] A collection of tickets for improving sorting larger than memory datasets / spilling sorts #15271

Description

Activity

alamb commented on Mar 17, 2025

alamb commented on Mar 19, 2025

andygrove commented on Mar 19, 2025

xudong963 commented on Mar 20, 2025

rluvaton commented on Apr 2, 2025

alamb commented on Apr 3, 2025

alamb commented on Sep 16, 2025

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions