Rekursive CTEs sind das Standard-Werkzeug für Hierarchien dynamischer Tiefe — Mitarbeiter-Manager-Bäume, Kategorie-Trees, Verzeichnis-Strukturen, sogar einfache Graph-Traversals. Mit WITH RECURSIVE referenziert ein CTE sich selbst, und Postgres baut das Resultat schrittweise auf. Hier das Pattern mit Beispielen.
Beispiel-Schema
CREATE TABLE employees (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text NOT NULL,
manager_id bigint REFERENCES employees(id)
);
INSERT INTO employees (name, manager_id) VALUES
('Anna', NULL), -- 1, CEO
('Bernd', 1), -- 2, unter Anna
('Carla', 1), -- 3, unter Anna
('David', 2), -- 4, unter Bernd
('Eva', 2), -- 5, unter Bernd
('Felix', 4), -- 6, unter David
('Greta', 4), -- 7, unter David
('Hans', 3); -- 8, unter CarlaHierarchie:
Anna (1, CEO)
├── Bernd (2)
│ ├── David (4)
│ │ ├── Felix (6)
│ │ └── Greta (7)
│ └── Eva (5)
└── Carla (3)
└── Hans (8)Grundform — alle Vorgesetzten finden
Aufgabe: ausgehend von Felix, alle Vorgesetzten bis zur CEO finden.
WITH RECURSIVE chain AS (
-- Anker: Start-Zeile
SELECT id, name, manager_id, 1 AS level
FROM employees
WHERE name = 'Felix'
UNION ALL
-- Rekursion: jeden Manager des bisher Gefundenen einbeziehen
SELECT e.id, e.name, e.manager_id, chain.level + 1
FROM employees e
INNER JOIN chain ON chain.manager_id = e.id
)
SELECT level, name FROM chain
ORDER BY level;Output:
level | name
-------+-------
1 | Felix
2 | David
3 | Bernd
4 | AnnaVier Ebenen — von Felix bis Anna. level zählt automatisch hoch.
Wie ein rekursiver CTE funktioniert
Ein rekursiver CTE besteht immer aus zwei Teilen, mit UNION ALL (oder UNION) verbunden:
| Teil | Was er tut |
|---|---|
| Anker (non-recursive term) | Liefert die initialen Zeilen. Wird einmal ausgewertet. |
| Rekursionsschritt (recursive term) | Verweist auf den CTE-Namen selbst. Liefert weitere Zeilen basierend auf den bisher gesammelten. |
Postgres-Auswertung:
- Anker auswerten → erste Iteration
- Rekursionsschritt mit den Anker-Resultaten ausführen → zweite Iteration
- Rekursionsschritt erneut, jetzt mit den Resultaten der zweiten Iteration → dritte Iteration
- Wiederholen, bis der Rekursionsschritt keine neuen Zeilen mehr liefert
- Alle gesammelten Zeilen vereinigen
Den Baum nach unten — alle Untergebenen
Die andere Richtung: ausgehend von David, alle Untergebenen rekursiv:
WITH RECURSIVE subordinates AS (
-- Anker: David selbst
SELECT id, name, manager_id, 0 AS depth
FROM employees
WHERE name = 'David'
UNION ALL
-- Rekursion: alle, die einen aus dem CTE als Manager haben
SELECT e.id, e.name, e.manager_id, s.depth + 1
FROM employees e
INNER JOIN subordinates s ON s.id = e.manager_id
)
SELECT depth, name FROM subordinates
ORDER BY depth, name;Output:
depth | name
-------+-------
0 | David
1 | Felix
1 | GretaBei tieferen Bäumen würde depth weiter wachsen. Klassisches Pattern für Org-Charts oder Kategorie-Bäume.
Mit Pfad — wie ist der Weg?
Praktisch: pro Zeile den Pfad von der Wurzel bis zu sich selbst zeigen:
WITH RECURSIVE tree AS (
SELECT
id,
name,
manager_id,
ARRAY[name]::text[] AS path,
1 AS depth
FROM employees
WHERE manager_id IS NULL -- Wurzel: CEO
UNION ALL
SELECT
e.id,
e.name,
e.manager_id,
tree.path || e.name, -- Pfad erweitern
tree.depth + 1
FROM employees e
INNER JOIN tree ON tree.id = e.manager_id
)
SELECT depth, array_to_string(path, ' → ') AS path FROM tree
ORDER BY path;Output:
depth | path
-------+------------------------------
1 | Anna
2 | Anna → Bernd
3 | Anna → Bernd → David
4 | Anna → Bernd → David → Felix
4 | Anna → Bernd → David → Greta
3 | Anna → Bernd → Eva
2 | Anna → Carla
3 | Anna → Carla → HansSehr nützlich für UI-Darstellungen, Breadcrumbs, oder Excel-artige Reports.
Cycle-Detection
Was passiert, wenn die Daten einen Zyklus enthalten (jemand ist sein eigener Manager über Umwege)? Ohne Schutz: Endlos-Schleife — Postgres läuft, bis der Speicher voll ist.
PG 14+ bringt eingebaute Cycle-Detection:
WITH RECURSIVE chain AS (
SELECT id, name, manager_id
FROM employees
WHERE name = 'Felix'
UNION ALL
SELECT e.id, e.name, e.manager_id
FROM employees e
JOIN chain ON chain.manager_id = e.id
) CYCLE id SET is_cycle USING path
SELECT * FROM chain WHERE NOT is_cycle;CYCLE id sagt: prüfe pro Zeile, ob id schon im Pfad war. Wenn ja, markiere is_cycle = true und höre auf, weiter zu rekursieren.
Vor PG 14 musste man Cycle-Detection manuell bauen — mit einem Pfad-Array und Prüfung im JOIN:
WITH RECURSIVE chain AS (
SELECT id, name, manager_id, ARRAY[id] AS visited
FROM employees WHERE name = 'Felix'
UNION ALL
SELECT e.id, e.name, e.manager_id, chain.visited || e.id
FROM employees e
JOIN chain ON chain.manager_id = e.id
WHERE NOT (e.id = ANY(chain.visited)) -- Schutz vor Zyklus
)
SELECT name FROM chain;Praxis-Beispiele
Datei-Struktur traversieren
CREATE TABLE files (
id bigint PRIMARY KEY,
parent_id bigint REFERENCES files(id),
name text NOT NULL,
size bigint
);
WITH RECURSIVE dir_tree AS (
SELECT id, parent_id, name, size, name AS path
FROM files
WHERE parent_id IS NULL -- Root
UNION ALL
SELECT f.id, f.parent_id, f.name, f.size,
dt.path || '/' || f.name
FROM files f
JOIN dir_tree dt ON dt.id = f.parent_id
)
SELECT path, size FROM dir_tree
ORDER BY path;Kategorie-Baum mit Anzahl Produkte
WITH RECURSIVE category_tree AS (
SELECT id, parent_id, name, 0 AS depth
FROM categories
WHERE parent_id IS NULL
UNION ALL
SELECT c.id, c.parent_id, c.name, ct.depth + 1
FROM categories c
JOIN category_tree ct ON ct.id = c.parent_id
)
SELECT
REPEAT(' ', ct.depth) || ct.name AS indented_name,
count(p.id) AS product_count
FROM category_tree ct
LEFT JOIN products p ON p.category_id = ct.id
GROUP BY ct.id, ct.name, ct.depth
ORDER BY ct.depth, ct.name;REPEAT(' ', depth) baut visuelle Einrückung — sieht im psql wie ein Tree aus.
Number-Sequenzen generieren
WITH RECURSIVE numbers AS (
SELECT 1 AS n
UNION ALL
SELECT n + 1 FROM numbers WHERE n < 10
)
SELECT * FROM numbers;Output: 1 bis 10. Akademisches Beispiel — generate_series(1, 10) ist im Alltag idiomatischer und schneller. Aber das Pattern „inkrementell aufbauen" ist die Idee dahinter.
Besonderheiten
UNION ALL ist meist richtiger als UNION.
UNION deduppliziert das Resultat — bei rekursiven CTEs meist unnötig, kostet aber Performance (Sort + DISTINCT). UNION ALL ist der Default. Nur bei expliziter Deduplikations-Anforderung lohnt sich UNION.
Endlos-Rekursion ohne Cycle-Schutz.
Wenn die Daten einen Zyklus enthalten (Felix → Bernd → Felix), läuft die CTE unendlich. Ab PG 14 mit CYCLE-Klausel automatisch geschützt; vorher manuell mit Pfad-Array. Bei externen Daten immer aufpassen.
Performance: Indexe auf Join-Spalten.
Im Rekursionsschritt wird über manager_id (oder parent_id) gejoined. Ohne Index auf dieser Spalte läuft Postgres in einen Sequential Scan pro Iteration — bei tiefen Bäumen merklich. Mit Index: schnell.
SEARCH BREADTH/DEPTH für Sortierung (PG 14+).
WITH RECURSIVE tree AS (...) SEARCH BREADTH FIRST BY id SET ord fügt eine Spalte hinzu, die einen sinnvollen Sort-Key liefert. BREADTH FIRST = Ebene für Ebene, DEPTH FIRST = vom ersten Knoten alles unter ihm zuerst. Vor PG 14 mit selbst gebautem Pfad-Array.
Rekursive CTEs sind IMMER materialisiert.
MATERIALIZED/NOT MATERIALIZED werden bei rekursiven CTEs ignoriert — Rekursion erfordert Materialisierung der Zwischenresultate. Optimizer-Tuning fokussiert daher auf den Anker und den Rekursionsschritt selbst.
Komplette Hierarchien laden = oft die falsche Frage.
Bei großen Bäumen ist's selten sinnvoll, alle Knoten in einer Query zu laden. Patterns wie „Materialized Path" (Pfad als String pro Knoten) oder ltree-Extension können bei sehr großen Hierarchien performanter sein. Rekursive CTEs glänzen bei kleinen-mittleren Bäumen oder gezielten Sub-Trees.