Recursive CTEs in Postgres for Hierarchical Data Queries

Recursive CTEs are the right tool for hierarchical data in Postgres because of how the engine actually executes them, and understanding the anchor/recursive split, the working-table model, and cycle/depth tracking gives you everything you need to write correct, efficient queries for org charts, category trees, and similar structures.

Why hierarchical data breaks ordinary SQL

Org charts, category trees, file systems, threaded comments, bill-of-materials lists, dependency graphs, all of them share the same shape: a parent, some children, and children of children, going down as far as the data allows.

The trouble starts because SQL was never built to walk chains of unknown length. It's a set-based, declarative language, and it has no built-in loop for traversing parent-child chains of unknown depth.

Before recursive CTEs existed, people worked around that gap in a few predictable ways, and all of them had a cost. One option was letting the application do the looping: fire a query for the root, then a query for its children, then a query for their children, and so on. This is the classic N+1 problem, and it gets ugly fast Recursive CTEs in PostgreSQL for Hierarchical Mobile App Data. One benchmark on 50,000 comments spread across 500 posts found around 120 queries needed per post Recursive CTEs in PostgreSQL for Hierarchical Mobile App Data. That's the cost of doing this over a network instead of a loopback socket, and it can turn a snappy feature into a support ticket.

Another workaround: stack up hardcoded CTEs, one per level, each one selecting from the last. It works, technically, right up until someone adds a sixth level of category nesting and the query silently stops covering it. Brittle by design, since it can't handle a depth it wasn't written for.

A third path was pushing the looping logic into procedural code, pl/pgsql or an application-layer function. That solves the "unknown depth" problem, but it also drags traversal logic out of the query layer and into something a developer has to maintain by hand.

Recursive CTEs replace all three approaches with one query and one round trip https://oneuptime.com/blog/post/2026-01-22-postgresql-recursive-cte-queries/view.

None of this is new or experimental. The SQL:1999 standard introduced recursive CTEs decades ago, every major relational database implements them today, and Postgres has supported them since version 8.4. What follows is how the engine actually runs one, because once that mechanism clicks, writing a correct org-chart or category-tree query stops being guesswork. What changes with a recursive CTE is one query, one round trip, complete tree (the same benchmark shows 12ms locally and 92ms at 80ms RTT), that is a 100× improvement from query strategy, not infrastructure Recursive CTEs in PostgreSQL for Hierarchical Mobile App Data.

How PostgreSQL executes a recursive CTE: the working-table model

The anchor member runs once and produces the starting result set, R0, such as the root employee or the top-level product. The recursive member references the CTE by its own name, and at each pass, that self-reference resolves only to the previous generation of rows, not the entire accumulated result so far.

Postgres runs this through what's usually called the working-table model, and it goes in a fixed sequence. First, the anchor runs once, and its output, R0, gets stored in a working table. Second, the recursive member runs using that working table as its input, which produces a new batch, R1. That batch replaces the working table, and it also gets appended to the growing output. Third, that step repeats until the recursive member returns an empty set.

Why does this matter beyond trivia? Because it means the engine is doing breadth-wise iteration, generation by generation, not classic function-call recursion with a call stack. There's no stack to blow out. Postgres can chew through a large adjacency list without choking on depth. But it also means nothing carries forward automatically. If you need a depth counter, a path, or a running total to survive from one generation to the next, you have to carry it yourself, as an actual column, in both the anchor and the recursive member.

The syntax that wraps all of this looks like:

WITH RECURSIVE cte_name (col1, col2, ...) AS (
 -- anchor member
 SELECT ...
 UNION ALL
 -- recursive member
 SELECT ...
 FROM table
 JOIN cte_name ON ...
)
SELECT * FROM cte_name;

The RECURSIVE keyword goes on the WITH clause itself, not on the individual named query inside it. It's a signal to the planner, up front, that at least one of the named queries in this block refers to itself. The two required parts. Step 4: return UNION ALL of R0 through Rn. WITH RECURSIVE cte_name (col1, col2, …) AS ( anchor SELECT … UNION ALL recursive SELECT … FROM table JOIN cte_name ON …) SELECT * FROM cte_name. The column list in the CTE header must match what both members return.

Building the org-chart query step by step

The classic employees table has an id, a name, a manager_id that points back to another row's id, and a title. A small, realistic dataset makes the mechanics obvious. Alice Chen is CEO, with no manager, the root of the tree. Bob Smith is CTO and reports to manager_id 1. Carol Davis is CFO, also reporting to manager_id 1. David Lee, Engineering Manager, reports to manager_id 2, and Eve Johnson, Product Manager, also reports to manager_id 2. Henry Miller, a Developer, reports to manager_id 6. Ivy Taylor, Financial Analyst, reports to manager_id 3.

Query one answers a common question: who reports up to a given manager, at any depth? The anchor picks out the target manager by id. The recursive member joins the employees table back to the working table on e.manager_id = ot.id, pulling in the next layer down each pass. Add a level column, starting at 1 in the anchor and incrementing by one in the recursive step, and you get a clean depth marker for free.

Mapping that back to the working-table model makes it click immediately. R0 is Bob Smith. R1 is everyone with manager_id = 2, David and Eve https://oneuptime.com/blog/post/2026-01-22-postgresql-recursive-cte-queries/view. R2 is everyone reporting to David or Eve, which brings in Frank and Grace https://oneuptime.com/blog/post/2026-01-22-postgresql-recursive-cte-queries/view. R3 brings in Henry. R4 comes back empty, and the recursion stops.

Query two runs the opposite direction: given a leaf employee, walk upward to find the path to the CEO. The anchor now starts at the leaf, say Henry Miller. The recursive member flips the join around, matching e.id = mp.manager_id, climbing toward the root instead of away from it. Stop the recursion by filtering WHERE manager_id IS NULL. The final string reads "Alice Chen > Bob Smith > David Lee > Frank Brown > Henry Miller," a complete lineage in one row.

One more thing this schema buys quietly: the foreign key on manager_id. Because manager_id references employees(id), the database itself refuses to let you create an orphaned employee pointing at nobody, or a circular reporting loop where someone ends up managing their own manager. Compare that to a denormalized model where the application has to check paths by hand; the adjacency list looks a lot more trustworthy by default. Frank Brown, Senior Developer, manager_id = 4. Grace Wilson, Senior Developer, manager_id = 4. The result can be shown with indentation using repeat(' ', level - 1) || name for a visual org chart.

Carrying depth, paths, and accumulated values through the recursion

Go back to the core mechanic: each pass of the recursion only sees the previous generation's rows. Anything you want to track across the whole climb, depth, a path, a running total, has to ride along as an explicit column in both the anchor and the recursive member. Nothing persists on its own.

The depth counter is the simplest version of this. The anchor sets 1 AS level (or 0 AS depth, depending on preference), and the recursive step just adds one each time, ot.level + 1. That number is useful well beyond pretty formatting: it drives indentation, it lets you cap a query to "only three levels down," and it gives you a clean sort key.

A path array does more work for the same idea. The anchor sets ARRAY[id] AS path, and each recursive step appends the current row's id, ot.path || e.id. Sorting by that path column produces a correct hierarchical ordering without reaching for a window function, and, as the next section covers, that same array doubles as a memory of every node already visited, which stops infinite loops.

Quantities can accumulate the same way, and a bill-of-materials table shows why that matters. Picture a parts table and an assemblies table that records parent_id, child_id, and a quantity for each subassembly. The anchor sets quantity = 1 for the top-level finished product. The final SELECT runs SUM(quantity * unit_cost) to get a total cost across every nested component, all in one query. Carry a path column through that same query and ordering by it lays components out in correct assembly order too.

There's also a plain path string, useful anywhere you want a readable lineage rather than an array of ids: the anchor sets name::text AS path, and each recursive step concatenates the current name onto the front. Breadcrumb navigation, audit trails, category display strings, all the same pattern.

Column names have to match exactly between the anchor and the recursive member. Name it path in one and path_arr in the other, and Postgres won't quietly figure out what you meant, it'll just throw an error that looks a lot more confusing than the mistake actually was.

Cycle detection and termination guards

Postgres does not cap recursion depth on its own. If the data has a cycle, employee A reporting to B who somehow reports back to A, or just a corrupted parent_id, the recursive member will happily run forever, chewing through memory and CPU until something outside the query kills the session.

There are three ways to guard against that, running from simplest to most thorough. The blunt option is a depth limit: add WHERE c.depth < 50 (or whatever ceiling makes sense) inside the recursive member Recursive CTEs in PostgreSQL for Hierarchical Mobile App Data. It's crude, but for something like an org chart or a category tree, where the real-world depth is known and bounded, it is a perfectly good safety net Recursive CTEs in PostgreSQL for Hierarchical Mobile App Data.

The sharper option reuses the path array from the section above: WHERE NOT e.id = ANY(ot.path). Because the path array already tracks every node visited so far, this check stops the recursion the instant it's about to revisit a node, which is the correct fix for genuine graphs where cycles are structurally possible, not just a bounded tree.

Postgres 14 also added a SQL-standard CYCLE clause that does this declaratively, letting the engine rewrite the query internally instead of you writing the WHERE clause by hand. It reads more cleanly, and anyone on Postgres 14 or newer should default to it. A graph traversal example makes the visited-check pattern concrete: finding every path from node A to node G, using WHERE NOT e.to_node = ANY(p.path) to block revisits, returns a result set of distinct paths along with a total_weight for each, correctly excluding any path that would loop back on itself.

UNION ALL is faster for true trees, where a foreign key on parent_id makes cycles structurally impossible. Plain UNION deduplicates on every iteration, which costs more but protects against duplicate rows in a graph that doesn't have an explicit visited-node check. The rule of thumb: if the schema enforces a tree, foreign key on parent_id, no self-references, use UNION ALL with no cycle guard for the best performance, and keep a depth limit around only as a defensive backstop in case the data ever gets weird. Alongside CYCLE, Postgres 14 also introduced a SEARCH clause, which controls whether the traversal proceeds depth-first or breadth-first declaratively, since both landed in the same release. UNION vs. UNION ALL.

Performance: what makes a recursive CTE fast or slow

Start with the single biggest lever: indexing. Without an index on the join column, something like CREATE INDEX idx_comments_parent ON comments(parent_id), the recursive join falls back to sequential scans on every single iteration, which is a significant inefficiency. It's also worth indexing whatever column the anchor filters on, a post_id and created_at composite, for example, since that's the very first thing the query has to touch.

Filter early in the anchor. A narrow anchor result set means iteration zero starts with fewer rows in the working table, and that reduction carries through every generation after it. A broad, unfiltered anchor can't be fixed downstream, no clever recursive step rescues a query that started too wide. In the same spirit, avoid SELECT * in either member. Wide rows get carried through the working table on every single pass, so unnecessary columns don't just cost a little, they cost a little multiplied by however many levels deep the recursion goes.

Materialization behavior changed across Postgres versions. Recursive CTEs are different: they always stay materialized, because the working-table mechanism requires it to produce correct results, and that's expected behavior, not a missed optimization. On Postgres 11 and earlier, every CTE was a hard optimization fence regardless of type, so anyone still running an old version and chasing performance should consider rewriting as a subquery instead. For anything new, Postgres 14 or later is the sensible baseline.

Pagination deserves a specific warning. OFFSET makes Postgres compute rows it's just going to throw away before it gets to the page you actually asked for. Cursor-based pagination, keyed off the path or id of the last row returned, avoids that waste, and it needs to be part of the design from day one. Bolting it on after the fact usually means changing the API contract, which is a much bigger job than adding it up front.

Plenty of teams avoid recursive CTEs altogether because they've heard, somewhere, that recursion in SQL is slow https://oneuptime.com/blog/post/2026-01-22-postgresql-recursive-cte-queries/view. On a well-indexed tree, Postgres's planner handles them efficiently, and the benchmark numbers earlier in this piece back that up. The right move is to benchmark the actual query before reaching for a more elaborate hierarchy model to solve a problem an index would have fixed. A short mental checklist covers most of it: define the base case clearly, keep each iteration's row count as small as possible, index the join columns, skip unnecessary columns, and filter early in the anchor. A 12ms query becomes a 400ms query on a modest dataset Recursive CTEs in PostgreSQL for Hierarchical Mobile App Data. In Postgres 12+, simple (non-recursive), side-effect-free CTEs referenced only once are inlined by default (the planner treats them like subqueries) Recursive CTEs in PostgreSQL for Hierarchical Mobile App Data.

Choosing between adjacency list, ltree, materialized path, and nested sets

The adjacency list, a parent_id column sitting right on the table itself, is what every example in this piece has used. Its biggest strength is write cost: adding a node means inserting one row with the correct parent_id, no rebalancing, no rewriting of paths elsewhere in the tree. That referential integrity through the foreign key, no orphaned nodes, no circular references, comes as a side effect of the schema, not something the application has to police. It's also fully portable, since every relational database understands a self-referencing foreign key. This is the right choice for anything with frequent structural changes: comment threads, task lists, folder structures, places where the shape of the tree shifts constantly.

The ltree extension takes a different approach, storing the full ancestor path as a label directly on the row, and backing it with a GiST index so ancestor and descendant lookups run fast without any recursion at all. The tradeoff is that the data is denormalized: nothing in the database enforces that a path stays valid as the tree changes, that responsibility sits entirely with the application. For a tree that barely moves, product categories updated once a quarter, a document taxonomy that's basically fixed, ltree is often simpler to query and faster to read than a recursive CTE.

Materialized path, a plain string column, solves a similar problem without a dedicated extension: subtree lookups run as a pattern match with LIKE, best for read-heavy, rarely mutated trees. It's a reasonable middle ground when a full extension feels like overkill but a plain adjacency list isn't giving fast enough reads.

Nested sets round out the options, encoding tree position through left and right numeric bounds instead of parent pointers or path strings, which makes certain subtree queries fast to read but makes every insert or move potentially expensive, since neighboring bounds may need renumbering.

A frequently-edited comment tree wants the cheap writes of an adjacency list. A rarely-touched category tree wants the fast, recursion-free reads of ltree or a materialized path. The honest way to choose is to ask how often the tree's shape actually changes, and let that answer drive the schema, rather than picking whichever model sounds the most sophisticated on paper. There are four main models for hierarchical data in PostgreSQL, per the research brief. In the same benchmark context, materialized path plus a LIKE query runs 18ms locally and 98ms at 80ms RTT, faster than N+1 but slower than a well-indexed recursive CTE at 12ms and 92ms Recursive CTEs in PostgreSQL for Hierarchical Mobile App Data.