Feature: support spilling large materialized CTEs to temporary tables #16858
Description
Currently materialized CTEs reside fully in memory with no disk spilling capability. This means that if the CTE you’re materializing is larger than your configured query memory limit you will get a memory limit exceeded error.
The only current option to work around this while retaining the performance benefits of materialized CTEs is to pull the CTE into a create transient table … which is created before you run the query.
It would be ideal to have a setting that specifies a memory ratio threshold above which materialized CTEs are converted into temporary tables.
It could be called something like materialized_cte_spilling_memory_ratio to be consistent with the existing aggregate_spilling_memory_ratio and join_spilling_memory_ratio settings.