WITH RECURSIVE Cycles and Depth Limits: MySQL cte_max_recursion_depth vs PostgreSQL CYCLE

The category tree had 41,000 nodes and, unknown to anyone, one cycle: a nightly merge job had reparented a “Clearance” category under one of its own descendants at 01:12, closing a loop six levels deep. At 02:15 the rollup job that walks the tree ran its usual WITH RECURSIVE query against the MySQL 8.0 primary and died in four seconds with ERROR 3636 (HY000): Recursive query aborted after 1001 iterations. The same query, run by an analyst against the PostgreSQL 15 reporting copy at 09:30, did not die. It spun for 38 minutes, building an ever-longer path through the loop, until statement_timeout finally cancelled it and the connection’s memory footprint unwound.

Two engines, one syntax, opposite failure modes: MySQL has a hard depth governor and PostgreSQL has none. Neither engine detects the cycle for you by default, which is the part that surprises people coming from graph tooling. This article is the operational comparison of WITH RECURSIVE on both: what the syntax differences actually are, how each engine dies on cyclic data, and how to detect or bound cycles before a reparenting bug turns a tree walk into an incident.

How does each engine die on cyclic data?

MySQL dies fast and loud at a configurable depth; PostgreSQL dies slowly and silently at whatever limit you set for query duration. MySQL’s governor is cte_max_recursion_depth, default 1000, counted as iterations of the recursive term; exceeding it raises ERROR 3636 and rolls the statement back. You can raise it per session:

-- MySQL 8.0: raise the governor for a legitimately deep tree
SET SESSION cte_max_recursion_depth = 10000;

-- PostgreSQL: no depth governor exists; the only brakes are
-- statement_timeout and the query's own termination condition
SET LOCAL statement_timeout = '30s';

The asymmetry in blast radius matters operationally. The MySQL failure cost one retry and an error log line. The PostgreSQL failure held a worker backend at 100% CPU and growing memory for 38 minutes, and because the reporting replica shared the host with two other workloads, their p99 latencies tripled for the duration. A depth governor is ugly but it is a governor; on PostgreSQL the equivalent safety net has to be built into the query itself or enforced by a timeout that cannot distinguish a runaway cycle from an honest deep tree.

How do you detect cycles on each engine?

PostgreSQL 14 and later detect cycles natively with the CYCLE clause; on MySQL you carry a path string and check membership yourself, which is slower but works everywhere. The PostgreSQL version marks cyclic rows and stops following them:

-- PostgreSQL 14+: stop at cycles and flag them
WITH RECURSIVE walk AS (
  SELECT id, parent_id, name, ARRAY[id] AS path
  FROM categories
  WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.parent_id, c.name, w.path || c.id
  FROM categories c
  JOIN walk w ON c.parent_id = w.id
)
CYCLE id SET is_cycle USING cycle_path
SELECT * FROM walk WHERE is_cycle;   -- the smoking gun rows

The MySQL equivalent accumulates the path as a string and refuses to revisit a node:

-- MySQL 8.0: manual path tracking, no CYCLE clause exists
WITH RECURSIVE walk AS (
  SELECT id, parent_id, name, CAST(id AS CHAR(200)) AS path
  FROM categories
  WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.parent_id, c.name, CONCAT(w.path, ',', c.id)
  FROM categories c
  JOIN walk w ON c.parent_id = w.id
  WHERE NOT FIND_IN_SET(c.id, w.path)
)
SELECT * FROM walk;

The FIND_IN_SET guard is what would have saved my 02:15 job: the cycle gets pruned instead of iterated, and the rollup completes with a wrong-but-bounded subtree rather than dying outright. It costs a string scan per recursion step, which is real but tolerable at tree sizes; the same path-tracking trick works on PostgreSQL too when you must support versions older than 14. Either way, surface the pruned rows — a WHERE that returns the cycle members is the difference between learning about the loop at 09:35 and learning about it from a customer.

What are the syntax and type rules that differ?

The skeleton is shared — both engines require the RECURSIVE keyword for a self-referencing CTE and both build the result from a non-recursive term plus UNION ALL — but the rules around it are not. Three differences bite in ports. First, MySQL derives the result column types and widths from the non-recursive term alone, so a path column seeded as CHAR(200) without an explicit CAST can silently truncate long paths on deep trees, which is why the example above casts deliberately; PostgreSQL resolves types across both terms and grows text without ceremony. Second, MySQL restricts the recursive reference to a single appearance in the FROM clause of the recursive term — no self-joins of the CTE, no reference inside a subquery — while PostgreSQL is more permissive about shapes like multiple references. Third, PostgreSQL 14 added the SEARCH clause for explicit breadth-first or depth-first ordering, which MySQL lacks entirely; on MySQL you emulate traversal order by carrying a depth counter and sorting by it at the end.

One shared trap deserves a sentence because it wastes hours: the recursion step count on MySQL is also the row-count-per-iteration governor, and error 3636 fires on legitimately deep acyclic data too — a 1,200-deep org chart trips the default 1000 with zero cycles involved. Depth is a data property, and a hardcoded 1000 is a bet about your data that somebody’s reorg will eventually take. The interplay between CTE materialization and how far the optimizer can see into these queries is covered in the CTE materialization comparison, and the plan-reading background in MySQL EXPLAIN vs PostgreSQL EXPLAIN ANALYZE applies directly when a tree walk gets slow rather than broken.

Where does the fix live: query, constraint, or job?

All three, in that order of immediacy. The query gets a cycle guard — CYCLE on PostgreSQL, path membership on MySQL — because a walk that cannot loop forever is a walk that cannot page anyone. The schema gets prevention: there is no declarative “no cycles” constraint on either engine, so the reparenting path goes through a trigger or application check that walks upward before committing a parent change, which on PostgreSQL is a recursive CTE inside a trigger function and on MySQL is the same walk in a BEFORE UPDATE trigger with SIGNAL. And the job gets a cheap audit: a nightly count of rows reachable via cycles, alerting on any value above zero, because the merge job that closed my loop had been running for two years before it produced one. Recursive structures fail on the data, not the syntax, and the syntax is where everyone looks first.

Where MonPG fits when recursion runs away

A recursive query spinning on a cycle looks exactly like a slow query from the outside — rising CPU on one backend, growing runtime, no error until the timeout. MonPG’s PostgreSQL monitoring keeps per-statement runtime history and session-level visibility into long-running queries, so the 38-minute tree walk shows up while it is still cancellable evidence rather than a postmortem anecdote, and the statement_timeout that saved the replica is the kind of guardrail whose firings belong on a dashboard.

MonPG monitors PostgreSQL today; MySQL support is on the roadmap. When it lands, the MySQL signals from this article are the ones to surface: ERROR 3636 occurrences counted per statement digest, and recursion-adjacent sessions flagged before the nightly window. Until then, the MySQL monitoring page tracks that work. The portable lesson stands on its own: give every recursive query a way to stop — a governor, a cycle guard, or a timeout — because the day your tree becomes a graph, the query will not stop on its own.

Related documentation

• MySQL: derived tables, views and CTE optimization
• PostgreSQL: WITH queries and materialization