Is your feature request related to a problem or challenge?

Breaking rationale from #14975 (comment) into its own ticket:

For memory-limit queries, executors might write temporary results into the disk to reduce memory load. It's important to have a configuration option to limit the max disk usage, in case some query would bloat the disk and cause other issues.

DuckDB provides a similar configuration:

max_temp_directory_size | The maximum amount of data stored inside the 'temp_directory' (when set) (e.g., 1GB)

Describe the solution you'd like

Provide a way to limit the total disk usage of queries that spill to disk

Describe alternatives you've considered

Add a configuration option max_temp_directory_size to disk manager (default to 100GB), if the limit is reached for all spilled files, an error will be returned.

Additional context

No response