Where the engine materializes a CTE, it becomes an optimization fence (dialect note; P08 crossover).

The perf-side face of cte_readability_materialization (which owns the readability story): a materialized CTE is a wall the planner won't see through — filters, projections (a SELECT * CTE materializes every column), and join conditions all stop at it. Sometimes the wall is the point (computed once, reused twice), which is why engines expose it as a keyword. Know which side your engine defaults to (EXPLAIN shows it), and reach for the keyword only on evidence.

WITH
  t AS (
    SELECT
      day,
      n
FROM
  tallies
  )
SELECT
  n
FROM
  t
WHERE
  day = 7;

SELECT
  n
FROM
  tallies
WHERE
  day = 7;

Both return the one day's tally, but where an engine materializes the CTE it becomes an optimization fence — the outer WHERE day = 7 can't push inside, so the CTE scans every row into a temp result, then filters one; DuckDB inlines and pushes the filter into the scan.

EXPLAIN the CTE boundary; MATERIALIZED/NOT keyword where offered; narrow the CTE's columns regardless.

DuckDB / Postgres 12+ inline by default (MATERIALIZED forces the fence); old Postgres always materialized.