PostgreSQL 12: Explicitly mark CTEs with MATERIALIZE
With PostgreSQL 12, CTE behavior changes. Before, CTEs used to be an optimization fence. We've used this sometimes to avoid a poor choice of plans. In PostgreSQL 12, CTEs are not being materialized or used as an optimization fence by default anymore.
In order to keep the same characteristics on PostgreSQL 12, we'll need to mark CTEs with MATERIALIZE
option.
We might want to review whether or not we need to retain the behavior. If we depend on that behavior though, we'll have to become explicit about it when using PostgreSQL 12.
For a transitioning period, we'll have to support both PostgreSQL 11 and 12 (until %14.0 is released). Since the MATERIALIZE
keyword has only been introduced with PG12, this is not backwards-compatible and we'd have to branch out here.
cc @gl-database