Support spilling in TopK queries #15538

Is your feature request related to a problem or challenge?

When running sort queries with large LIMIT and small memory limit, it can go out of memory

select *
from tbl
order by c1
limit 1000000000

It's possible to enable spilling capability inside TopK data structure to let such queries complete.

Describe the solution you'd like

Approach 1

Add back fetch field back to ExternalSorter to support external TopK queries, which is removed in #15525 (because it's unused now)
This approach can be slightly faster, but requires to add a configuration option to switch to ExternalSorter path for large LIMIT, instead of the default TopK path. (or let optimizer figure out when to switch automatically, though it's also tricky)

Approach 2

Add spilling capability inside TopK executor. When the memory limit is reached, it can fallback to out-of-core execution without introducing a new configuration.

Describe alternatives you've considered

No response

Additional context

The sort + limit query is usually run with a small LIMIT count, so it's mostly memory-efficient.
@alamb is referring to this issue as a sort of exploratory idea, so perhaps someone with real usage knows better how to get it implemented 🤔