pg_cte_force — Controlling CTE materialization in PostgreSQL

 Starting with PostgreSQL 12, the behavior of Common Table Expressions (CTEs) changed significantly.

Before PostgreSQL 12, CTEs acted as an optimization fence and were always materialized.

Since PostgreSQL 12, the planner can inline side-effect-free CTEs when appropriate. By default, a CTE referenced only once can be folded into the parent query, while a CTE referenced multiple times is normally materialized. PostgreSQL also provides the MATERIALIZED and NOT MATERIALIZED clauses to explicitly control this behavior.

This is generally a great improvement for query optimization.

But what happens when you have to deal with legacy applications?

I often work with applications originally developed for PostgreSQL versions earlier than 12, where queries were written assuming the old CTE behavior.

Sometimes things become even more complicated because SQL queries are generated by frameworks or applications that cannot easily be modified to add the MATERIALIZED clause.

So I decided to write a small PostgreSQL extension:

👉 pg_cte_force

The idea is simple: provide a way to control CTE materialization at the PostgreSQL configuration level, without modifying the application SQL.

Once the extension is installed, a new GUC is available:

pg_cte_force.mode

It supports three modes:

🔹 pg_cte_force.mode = 'default'

Use the standard PostgreSQL behavior.

🔹 pg_cte_force.mode = 'materialized'

Force CTEs to be materialized, which can be useful when dealing with legacy applications designed around the pre-PostgreSQL 12 behavior.

🔹 pg_cte_force.mode = 'not_materialized'

Force CTE inlining, even when a CTE is referenced multiple times.

The goal is not to replace PostgreSQL's planner decisions, but to provide DBAs with an additional tool when working with applications where changing the generated SQL is difficult or simply not possible.

I hope it can be useful to other PostgreSQL DBAs dealing with similar legacy environments.

Source code and installation instructions:

🔗 https://github.com/datainfsrl/pg_cte_force

Feedback, tests and suggestions are very welcome!

Enjoy!

Enrico