CTEs: naming steps, materialization, and writing in WITH

A common table expression (WITH name AS (...)) gives a subquery a name so the main query, and later CTEs, can refer to it. It is mainly a readability tool: a five-step report reads top to bottom instead of inside out.

WITH recent AS (
  SELECT product_id, qty
  FROM order_lines
  WHERE shipped_on >= date '2024-06-01'
),
per_product AS (
  SELECT product_id, sum(qty) AS units
  FROM recent GROUP BY product_id
)
SELECT p.name, pp.units
FROM per_product pp
JOIN products p ON p.id = pp.product_id
ORDER BY pp.units DESC, p.name
LIMIT 10;

Inlined or materialized?

Before PostgreSQL 12 every CTE was an optimization fence: computed once, in full, stored, and then scanned. Since 12, a CTE that is referenced once, is not recursive and has no side effects is inlined into the main query like an ordinary subquery, so conditions from outside can be pushed into it. You can override the choice with AS MATERIALIZED or AS NOT MATERIALIZED.

The two plans below are for the same query on 100,000 orders. Inlined, the id = 42 condition reaches the table and uses the primary key. Materialized, the CTE scans the table, keeps every row over 50, and only then filters for id = 42.

1CREATE TABLE orders (id int PRIMARY KEY, customer_id int NOT NULL, amount numeric NOT NULL);2INSERT INTO orders SELECT g, g % 1000, g % 97 FROM generate_series(1, 100000) AS g;3ANALYZE orders;

1EXPLAIN (COSTS OFF)2WITH big AS (3  SELECT * FROM orders WHERE amount > 504)5SELECT * FROM big WHERE id = 42;

1CREATE TABLE orders (id int PRIMARY KEY, customer_id int NOT NULL, amount numeric NOT NULL);2INSERT INTO orders SELECT g, g % 1000, g % 97 FROM generate_series(1, 100000) AS g;3ANALYZE orders;

1EXPLAIN (COSTS OFF)2WITH big AS MATERIALIZED (3  SELECT * FROM orders WHERE amount > 504)5SELECT * FROM big WHERE id = 42;

Materializing is still useful when a CTE is expensive and referenced several times (then it is the default), or when you deliberately want to stop the planner from merging a step it estimates badly.

Data-modifying CTEs

In PostgreSQL a CTE can be an INSERT, UPDATE or DELETE with RETURNING, and the main statement consumes the returned rows. Moving rows between tables becomes one statement, and therefore atomic.

One rule surprises people: every part of the statement runs against the same snapshot, taken before any of it executes. The main query does not see the CTE's changes in the table; it sees them only through what RETURNING produced. The example moves two sessions, yet its main query still counts three rows in sessions.

1CREATE TABLE sessions (id int PRIMARY KEY, user_id int NOT NULL, expires_at date NOT NULL);2CREATE TABLE sessions_archive (id int PRIMARY KEY, user_id int NOT NULL, expires_at date NOT NULL);3INSERT INTO sessions VALUES (1, 7, '2024-01-01'), (2, 8, '2030-01-01'), (3, 7, '2024-02-01');

1WITH moved AS (2  DELETE FROM sessions3  WHERE expires_at < date '2025-01-01'4  RETURNING *5),6archived AS (7  INSERT INTO sessions_archive8  SELECT * FROM moved9  RETURNING id10)11SELECT (SELECT string_agg(id::text, ',' ORDER BY id) FROM archived) AS archived_ids,12       (SELECT count(*) FROM sessions) AS sessions_seen_by_main_query;

Common mistakes

• Assuming a CTE is always computed once and cached (true before 12, not now) or always inlined (not when it is referenced twice).
• Reading the modified table in the main query of a data-modifying statement and expecting the new values; read RETURNING instead.
• Copying rows to an archive with INSERT ... SELECT and deleting them in a separate statement with a slightly different WHERE, so a row is lost or kept twice.

What interviewers ask

• Is a CTE an optimization fence in PostgreSQL? Not since 12 for a single-reference, side-effect-free CTE; MATERIALIZED restores the fence.
• How would you archive and delete old rows atomically? WITH moved AS (DELETE ... RETURNING *) INSERT INTO archive SELECT * FROM moved.
• CTE or temporary table? A CTE lives for one statement; a temp table persists for the session, can be indexed and analyzed.