3 canonical patterns: downward, upward, concatenated path

Recursive CTEs are used in 3 patterns that cover ~95% of real cases. This capsule shows them with complete code, expected output, and typical use cases.

The 3 are:

1. Downward traversal — from a parent, all descendants (subtree).
2. Upward traversal — from a node, all ancestors (breadcrumbs).
3. Concatenated path — the full path as a string ("Electronics > Computers > Laptops").

Common setup

Same schema as capsule 02:

CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    parent_id INTEGER REFERENCES categories(id)
);

INSERT INTO categories (id, name, parent_id) VALUES
    (1, 'Electronics', NULL),
    (2, 'Computers', 1),
    (3, 'Laptops', 2),
    (4, 'Desktops', 2),
    (5, 'Smartphones', 1),
    (6, 'iPhone', 5),
    (8, 'Books', NULL),
    (9, 'Fiction', 8);

Pattern 1: downward traversal (subtree)

Use case: given a parent, list all descendants. Example: "all products in Electronics and its subcategories."

WITH RECURSIVE descendants AS (
    -- Base: the starting category
    SELECT id, name, parent_id, 0 AS depth
    FROM categories
    WHERE id = 1  -- Electronics

    UNION ALL

    -- Recursive: children of accumulated rows
    SELECT c.id, c.name, c.parent_id, d.depth + 1
    FROM categories c
    JOIN descendants d ON c.parent_id = d.id
)
SELECT * FROM descendants ORDER BY depth, name;

Output:

 id | name        | parent_id | depth
----+-------------+-----------+-------
  1 | Electronics |    NULL   |   0
  2 | Computers   |     1     |   1
  5 | Smartphones |     1     |   1
  3 | Laptops     |     2     |   2
  4 | Desktops    |     2     |   2
  6 | iPhone      |     5     |   2

Real case: filter products by category including subcategories:

-- Products in Electronics and all its subcategories
WITH RECURSIVE category_tree AS (
    SELECT id FROM categories WHERE id = 1
    UNION ALL
    SELECT c.id FROM categories c JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT p.* FROM products p
WHERE p.category_id IN (SELECT id FROM category_tree);

Returns products in Electronics, Computers, Laptops, Desktops, Smartphones, iPhone.

In SQLAlchemy 2.0

async def get_subtree(session: AsyncSession, root_id: int) -> list[dict]:
    result = await session.execute(text("""
        WITH RECURSIVE descendants AS (
            SELECT id, name, parent_id, 0 AS depth
            FROM categories WHERE id = :root_id
            UNION ALL
            SELECT c.id, c.name, c.parent_id, d.depth + 1
            FROM categories c
            JOIN descendants d ON c.parent_id = d.id
        )
        SELECT id, name, parent_id, depth FROM descendants ORDER BY depth, name
    """), {"root_id": root_id})

    return [dict(r) for r in result.mappings()]

Pattern 2: upward traversal (ancestors / breadcrumbs)

Use case: given a specific node, list all ancestors. Example: UI breadcrumbs ("Home > Electronics > Computers > Laptops > [this page]").

WITH RECURSIVE ancestors AS (
    -- Base: the starting node
    SELECT id, name, parent_id, 0 AS depth
    FROM categories
    WHERE id = 3  -- Laptops

    UNION ALL

    -- Recursive: parent of the accumulated rows
    SELECT c.id, c.name, c.parent_id, a.depth + 1
    FROM categories c
    JOIN ancestors a ON c.id = a.parent_id  -- heads up! inverted
)
SELECT * FROM ancestors ORDER BY depth DESC;  -- DESC to show root first

Output:

 id | name        | parent_id | depth
----+-------------+-----------+-------
  1 | Electronics |    NULL   |   2
  2 | Computers   |     1     |   1
  3 | Laptops     |     2     |   0

Note c.id = a.parent_id (inverted vs downward).

For UI breadcrumbs

SELECT array_agg(name ORDER BY depth DESC) AS breadcrumb
FROM (
    WITH RECURSIVE ancestors AS (
        SELECT id, name, parent_id, 0 AS depth
        FROM categories WHERE id = 3
        UNION ALL
        SELECT c.id, c.name, c.parent_id, a.depth + 1
        FROM categories c JOIN ancestors a ON c.id = a.parent_id
    )
    SELECT * FROM ancestors
) AS path;

Output: ['Electronics', 'Computers', 'Laptops'] — ready for the frontend.

In SQLAlchemy 2.0

async def get_breadcrumb(session: AsyncSession, node_id: int) -> list[str]:
    result = await session.execute(text("""
        WITH RECURSIVE ancestors AS (
            SELECT id, name, parent_id, 0 AS depth
            FROM categories WHERE id = :node_id
            UNION ALL
            SELECT c.id, c.name, c.parent_id, a.depth + 1
            FROM categories c JOIN ancestors a ON c.id = a.parent_id
        )
        SELECT name FROM ancestors ORDER BY depth DESC
    """), {"node_id": node_id})

    return [row[0] for row in result]

Pattern 3: concatenated path

Use case: generate the full path as a string. Example: to show "Electronics > Computers > Laptops" in a single column.

WITH RECURSIVE ancestors AS (
    SELECT id, name, parent_id, name::TEXT AS path, 0 AS depth
    FROM categories
    WHERE id = 3  -- Laptops

    UNION ALL

    SELECT
        c.id,
        c.name,
        c.parent_id,
        c.name || ' > ' || a.path AS path,  -- concatenate
        a.depth + 1
    FROM categories c
    JOIN ancestors a ON c.id = a.parent_id
)
SELECT path FROM ancestors WHERE parent_id IS NULL;  -- only the oldest (root reached)

Output:

            path
-------------------------------
 Electronics > Computers > Laptops

|| is concatenation in SQL. c.name || ' > ' || a.path builds the path as we climb.

Variant: downward

Path from root to a specific node, traversing downward:

WITH RECURSIVE descendants AS (
    SELECT id, name, parent_id, name::TEXT AS path
    FROM categories WHERE parent_id IS NULL
    UNION ALL
    SELECT
        c.id,
        c.name,
        c.parent_id,
        d.path || ' > ' || c.name AS path
    FROM categories c
    JOIN descendants d ON c.parent_id = d.id
)
SELECT path FROM descendants WHERE id = 3;  -- Laptops

Same output Electronics > Computers > Laptops.

For all categories at once

WITH RECURSIVE category_paths AS (
    SELECT id, name, parent_id, name::TEXT AS path
    FROM categories WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, c.name, c.parent_id, p.path || ' > ' || c.name
    FROM categories c JOIN category_paths p ON c.parent_id = p.id
)
SELECT id, path FROM category_paths;

Output:

 id | path
----+---------------------------------
  1 | Electronics
  2 | Electronics > Computers
  3 | Electronics > Computers > Laptops
  4 | Electronics > Computers > Desktops
  5 | Electronics > Smartphones
  6 | Electronics > Smartphones > iPhone
  8 | Books
  9 | Books > Fiction

Useful for autocomplete: "type X to find a category by full path."

Combining patterns: full info of a node

-- Return: the node + breadcrumb + descendants count
WITH
ancestors AS (
    WITH RECURSIVE a AS (
        SELECT id, name, parent_id, 0 AS d FROM categories WHERE id = 3
        UNION ALL
        SELECT c.id, c.name, c.parent_id, a.d + 1
        FROM categories c JOIN a ON c.id = a.parent_id
    )
    SELECT array_agg(name ORDER BY d DESC) AS path FROM a
),
descendants AS (
    WITH RECURSIVE d AS (
        SELECT id FROM categories WHERE id = 3
        UNION ALL
        SELECT c.id FROM categories c JOIN d ON c.parent_id = d.id
    )
    SELECT COUNT(*) - 1 AS count FROM d
)
SELECT
    cat.id,
    cat.name,
    (SELECT path FROM ancestors) AS breadcrumb,
    (SELECT count FROM descendants) AS descendant_count
FROM categories cat
WHERE cat.id = 3;

A single query returns all the info needed for a "category detail page": breadcrumb, count of subcategories, etc.

Real use cases

Comment threads (hierarchy)

-- Schema
CREATE TABLE comments (
    id SERIAL PRIMARY KEY,
    post_id INTEGER,
    parent_comment_id INTEGER REFERENCES comments(id),
    body TEXT,
    created_at TIMESTAMPTZ
);

-- Tree of a comment with its replies
WITH RECURSIVE comment_tree AS (
    SELECT id, body, parent_comment_id, 0 AS depth, ARRAY[created_at]::TIMESTAMPTZ[] AS path
    FROM comments WHERE id = :root_comment_id
    UNION ALL
    SELECT c.id, c.body, c.parent_comment_id, t.depth + 1,
           t.path || c.created_at
    FROM comments c
    JOIN comment_tree t ON c.parent_comment_id = t.id
)
SELECT * FROM comment_tree ORDER BY path;  -- Reddit-style threading

Org chart (employees → manager)

-- Schema: employees(id, name, manager_id)
-- Query: subtree of employees under a manager
WITH RECURSIVE org_tree AS (
    SELECT id, name, manager_id, 0 AS level FROM employees WHERE id = :manager_id
    UNION ALL
    SELECT e.id, e.name, e.manager_id, t.level + 1
    FROM employees e JOIN org_tree t ON e.manager_id = t.id
)
SELECT * FROM org_tree;

URL path navigation

-- Schema: pages(id, slug, parent_page_id)
-- Build full URL: /electronics/computers/laptops
WITH RECURSIVE page_paths AS (
    SELECT id, slug, parent_page_id, '/' || slug AS url FROM pages WHERE parent_page_id IS NULL
    UNION ALL
    SELECT p.id, p.slug, p.parent_page_id, pp.url || '/' || p.slug
    FROM pages p JOIN page_paths pp ON p.parent_page_id = pp.id
)
SELECT id, slug, url FROM page_paths;

Performance considerations

Essential index:

CREATE INDEX idx_categories_parent ON categories(parent_id);

For descendants: the index lets you find children quickly.

Limit depth:

WITH RECURSIVE descendants AS (
    SELECT id, name, parent_id, 0 AS depth FROM categories WHERE id = 1
    UNION ALL
    SELECT c.id, c.name, c.parent_id, d.depth + 1
    FROM categories c JOIN descendants d ON c.parent_id = d.id
    WHERE d.depth < 5  -- limit depth in the recursive case
)
SELECT * FROM descendants;

WHERE d.depth < 5 cuts off when you reach depth 5. Useful for potentially very deep trees.

Materialize for very frequent queries:

If the hierarchy changes rarely and is queried constantly:

CREATE MATERIALIZED VIEW category_paths AS
WITH RECURSIVE paths AS (...)
SELECT id, name, full_path, depth FROM paths;

CREATE UNIQUE INDEX ON category_paths (id);

Refresh with REFRESH MATERIALIZED VIEW CONCURRENTLY when it changes. Capsule 05 covers the more sophisticated "closure table" alternative.

Common traps and mistakes

1. Confusing the join direction.

Downward: JOIN ON c.parent_id = recursive.id (children of the accumulated). Upward: JOIN ON c.id = recursive.parent_id (parent of the accumulated).

Confuse them → infinite loop or empty result.

2. No ORDER BY in the final query.

A recursive CTE returns rows in a non-deterministic order. For ordered output, ORDER BY in the final query.

3. Path concatenation without a TEXT cast.

-- ❌ Type ambiguity in some cases
name || ' > ' || path

An explicit cast in the base case (name::TEXT) removes the ambiguity.

4. LIMIT in the recursive case.

-- ❌ LIMIT doesn't work as you expect inside recursive
SELECT ... FROM categories ... LIMIT 10

LIMIT only in the final query, not inside the recursive CTE.

5. Assuming BFS.

By default, a recursive CTE processes in an order that isn't guaranteed. For strict BFS (level by level), the BREADTH FIRST BY clause (PG 14+, capsule 04).

6. Depth column miscalculation.

-- If you forget to increment depth, everything ends up at the same level visually
SELECT c.id, c.name, c.parent_id, d.depth  -- ❌ doesn't increment

Always d.depth + 1 in the recursive case.

7. Performance with a missing index.

Without an index on parent_id, a sequential scan on every iteration. The index is mandatory.

Exercise: implement the 3 patterns

Setup: the schema and data from the section.

Step 1: downward — given parent_id=1 (Electronics), list them all.

Step 2: upward — given node_id=6 (iPhone), list the ancestors.

Step 3: path — for id=6, return the string Electronics > Smartphones > iPhone.

Step 4: combine — a single query that returns for id=6: id, name, breadcrumb (array), depth from root.

Step 5: wrap in FastAPI endpoints.

@router.get("/categories/{cat_id}/subtree")
async def get_subtree(cat_id: int, db: AsyncSession = Depends(get_db)):
    return await get_descendants_helper(db, cat_id)


@router.get("/categories/{cat_id}/breadcrumb")
async def get_breadcrumb(cat_id: int, db: AsyncSession = Depends(get_db)):
    return await get_ancestors_helper(db, cat_id)


@router.get("/categories/{cat_id}/path")
async def get_path(cat_id: int, db: AsyncSession = Depends(get_db)):
    return await get_full_path_helper(db, cat_id)

WITH ancestors AS (...recursive...),
     descendants AS (...recursive...)
SELECT cat.id, cat.name,
       (SELECT array_agg(name ORDER BY d DESC) FROM ancestors) AS breadcrumb,
       (SELECT COUNT(*) FROM descendants) AS subtree_count
FROM categories cat WHERE cat.id = 6;

Summary and next step

What you learned:

• Pattern 1: downward — JOIN ON c.parent_id = recursive.id. Subtree.
• Pattern 2: upward — JOIN ON c.id = recursive.parent_id. Breadcrumbs.
• Pattern 3: path — concatenate with ||. Hierarchical visualization.
• Combinations with multiple CTEs for rich info.
• Real cases: comment threads, org charts, URL paths.
• Performance: index on parent_id, depth limit, materialize if you query it a lot.

In the next capsule we go to the CYCLE clause (PG 14+) — protection against infinite loops in graphs. And the difference between BREADTH FIRST and DEPTH FIRST in a recursive CTE.

Resources

1. PostgreSQL Docs — WITH RECURSIVE — reference.
2. The Art of PostgreSQL — Hierarchies — advanced patterns.
3. Markus Winand — SQL hierarchies — technical analysis.

Capsule 03 of 08 — Module 8 — Advanced PostgreSQL for Backend Guide