Module 8: Recursive CTEs + Final Project

Module 8: Recursive CTEs + Final Project

Next →

You've reached the close of the guide and of the Data Layer sub-track (guides #12-#14). This module is double: it covers recursive CTEs (recursive Common Table Expressions) in the first half — the last SQL-specific skill you need — and the final integrating project in the second half — a refactor of the Blog API applying every feature in the guide.

Recursive CTEs are the tool for hierarchies and graphs: nested categories, comment threads, org charts, social graphs. Almost every backend dev needs them at some point. Most people avoid them by writing loops of queries in Python (the N+1 queries anti-pattern for tree traversal). Covering them removes that anti-pattern and gives you elegant queries for problems that look complex.

The final project integrates EVERYTHING: JSONB metadata, FTS, partitioning, MVs, advisory locks, extensions, recursive CTEs. You refactor the Blog API from guide #8 (PostgreSQL & SQLAlchemy) applying all 6 patterns in a single codebase. When you finish you have a portfolio-worthy PR with before/after benchmarks and ADRs per decision.

Where are we? Where are we headed?

What you already know (modules 1 to 7):

  • Deep JSONB + GIN indexing (module 1).
  • Full-Text Search with tsvector + pg_trgm (modules 2-3).
  • Declarative partitioning (module 4).
  • Materialized views (module 5).
  • Advisory locks + savepoints (module 6).
  • Useful extensions (module 7).

What you're going to build this time:

Two things. First, recursive CTEs with clear anatomy, 3 canonical patterns (downward traversal, upward, concatenated path), the CYCLE clause for graphs, and the "closure table" alternative for stable hierarchies. Second, the final integrating project: a refactor of the Blog API with 6 components that apply every feature in the guide.

Why this module comes here:

  1. Recursive CTEs are the last SQL-specific skill a senior backend dev needs. After this, going deeper into SQL is DBA territory.
  2. The final project requires having seen every previous module. It's the natural close.
  3. It closes out the complete Data Layer sub-track (guides #12 + #13 + #14).

Professional objective

By the end of this module you'll be able to:

Recursive CTEs:

  • Distinguish a basic CTE (WITH name AS (...)) from a recursive CTE (WITH RECURSIVE name AS (...)).
  • Articulate the anatomy: base case (anchor) + UNION ALL + recursive case.
  • Implement 3 canonical patterns: downward traversal, upward, concatenated path.
  • Use the CYCLE clause (PG 14+) to avoid loops in graphs.
  • Decide between a recursive CTE and a closure table for very stable hierarchies.
  • Express recursive CTEs in SQLAlchemy with select().cte(recursive=True).

Final project:

  • Refactor the Blog API with 6 integrated components (JSONB, FTS, partitioning, MVs, recursive categories, advisory locks).
  • Produce before/after benchmarks with real numbers.
  • Document decisions with mini-ADRs (Architecture Decision Records).
  • Deliver a portfolio-worthy PR that demonstrates integrated mastery.

Why does this module matter?

Recursive CTEs remove a frequent anti-pattern: N+1 queries for hierarchy traversal. Typical case:

# ❌ Anti-pattern: loop with queries
async def get_subcategories_recursive(parent_id):
    children = await fetch_children(parent_id)
    for child in children:
        child.subcategories = await get_subcategories_recursive(child.id)
    return children
# For a 5-level tree with 100 categories: 100+ queries
-- ✅ Solution: 1 query with a recursive CTE
WITH RECURSIVE subcategories AS (
    SELECT id, name, parent_id, 0 AS depth
    FROM categories WHERE id = :parent_id
    UNION ALL
    SELECT c.id, c.name, c.parent_id, s.depth + 1
    FROM categories c
    JOIN subcategories s ON c.parent_id = s.id
)
SELECT * FROM subcategories;
-- 1 query, all descendants

100x fewer queries, latency 50-100x better.

The final project demonstrates integrated mastery. In senior interviews, "I know JSONB" and separately "I know partitioning" is basic. Showing a PR where you applied 6 features together in a real system, with benchmarks and documented decisions, is what demonstrates real experience.

In the real senior backend dev role, this module is the conceptual close of "what a dev needs to know about PostgreSQL." After this, going deeper into replication, high availability, etc. is DBA work. The complete Data Layer sub-track (#12 + #13 + #14) covers the reasonable ceiling.

For senior interviews, the final project is the strongest deliverable you'll be able to link. "Come, look at this PR where I refactored a Blog API with 6 advanced PostgreSQL patterns, documented benchmarks, ADRs per decision."

Module map

CapsuleTopicFocus
01Module introductionYou are here. Mental frame, map.
02Recursive CTEs: anatomy and simple caseNested categories. Base case + UNION ALL + recursive.
033 canonical patternsDownward, upward, concatenated path. Complete code.
04CYCLE clause + graphsDetect/avoid infinite loops. PG 14+.
05Alternative: closure tableTrade-off for stable hierarchies.
06Final project — phase 1Setup, baseline, 3 components (JSONB, FTS, MV).
07Final project — phase 23 remaining components (partitioning, advisory lock, categories).
08Final documentation + wrap-upBENCHMARKS.md, ARCHITECTURE.md, close of the guide.

Structure: first half CTEs (02-05), second half the final project (06-08).

The final project — overview

You're going to refactor an existing Blog API. If you have the code from guide #8 (PostgreSQL & SQLAlchemy from the Backend Python path), use that. Otherwise, a minimal scaffold is provided.

Blog base schema:

users (id, email, name)
categories (id, name, parent_id)  -- hierarchical
posts (id, author_id, category_id, title, body, created_at, metadata)
comments (id, post_id, user_id, body, created_at)
tags (id, name)
post_tags (post_id, tag_id)

Refactor: 6 integrating components

  1. JSONB metadata on posts with a GIN index (modules 1-2).
  2. Spanish FTS over title + body with a generated column and pg_trgm fallback (module 3).
  3. Partitioning of comments by month with a zero-downtime migration (module 4).
  4. Materialized view top_posts_weekly with CONCURRENTLY refresh (module 5).
  5. Recursive categories with a recursive CTE (module 8).
  6. Advisory lock for the FTS re-indexing job (module 6).

Plus module 7 extensions (citext for emails, pg_trgm for fuzzy search).

Deliverables:

  • Complete PR with Alembic migrations, updated FastAPI/SQLAlchemy code.
  • BENCHMARKS.md with before/after of the key queries.
  • ARCHITECTURE.md with ADRs per decision.
  • Automated tests.
  • Updated README with reproducible instructions.

Time estimate

Capsules 02-05 (recursive CTEs): ~30-45 min of reading + exercises.

Capsules 06-08 (project): the capsules lay out the plan; the implementation takes 4-8 additional hours on your own time.

Suggested distribution:

  • Capsules 02-05: 1 session.
  • Capsule 06 + phase 1 implementation: 1-2 sessions (3-4 hrs).
  • Capsule 07 + phase 2 implementation: 1-2 sessions (3-4 hrs).
  • Capsule 08 + documentation: 1 session (2-3 hrs).

Total: ~10-15 hrs spread out. The project is worth the investment — it's the strongest deliverable you'll produce in the sub-track.

Self-assessment question

Before starting this module, can you answer?

  • What is a recursive CTE and what is it for?
  • What is the anatomy: base case, recursive case, UNION ALL?
  • How do you avoid an infinite loop in a recursive CTE over a graph?
  • When is a closure table better than a recursive CTE?
  • Which patterns from the guide would you apply to refactor a Blog API?

If you hesitate on more than two, the module is well calibrated for you.

We start in the next capsule

We start with capsule 02: recursive CTEs — anatomy and simple case. You'll see the classic problem (a loop of queries for tree traversal) and the elegant solution (1 query with WITH RECURSIVE). The case is blog categories with subcategories — the same one you'll apply in the final project.

Before moving on, make sure you have PostgreSQL 14+ (for the CYCLE clause) and SQLAlchemy 2.0+.

Resources for the module

  1. PostgreSQL Docs — WITH Queries — official reference.
  2. SQLAlchemy 2.0 — Recursive CTEs — reference.
  3. The Art of PostgreSQL — CTEs — dedicated chapter.
  4. Markus Winand — SQL hierarchies — analysis of approaches.
  5. Closure Table pattern — advanced alternative.

Module 8 — Advanced PostgreSQL for Backend Guide

← PreviousMini-project: refactoring a user system with extensionsNext →Recursive CTEs: anatomy and simple case