Categories with subcategories. Comment threads. An org chart. Folders. Sooner or later every application needs a tree, and relational tables are flat. How should you store it, and how do you query "everything under this node" without a loop that runs one query per level?
There are four classic models. With recursive CTEs now supported everywhere, the simplest one is usually the right default.
1. Adjacency list: each row points to its parent
CREATE TABLE category (
id intPRIMARY KEY,
parent_id intREFERENCES category (id), -- NULL = root
name text NOT NULL
);
CREATE INDEX ON category (parent_id);
It's easy to understand and easy to change: moving a subtree is a single UPDATE of one parent_id. The traditional complaint was that you couldn't fetch a whole subtree in one query. Recursive CTEs solve that:
WITHRECURSIVE subtree AS (
SELECT id, parent_id, name, 1AS depth
FROM category
WHERE id =42-- start nodeUNIONALLSELECT c.id, c.parent_id, c.name, s.depth +1FROM category c
JOIN subtree s ON c.parent_id = s.id
)
subtree depth;
A WHERE condition on the right-hand table throws away exactly the rows the LEFT JOIN kept. Where each condition belongs, plus anti-joins, counting and fan-out traps.
This works in PostgreSQL, MySQL 8+, MariaDB 10.2+, SQLite 3.8.3+ and Oracle. SQL Server uses the same query without the RECURSIVE keyword. Swap the join to c.id = s.parent_id to walk up to the ancestors (breadcrumbs).
Guard against cycles. Bad data (A → B → A) makes the recursion run until it hits a limit. PostgreSQL 14+ has a built-in clause:
WITHRECURSIVE subtree AS (...)
CYCLE id SET is_cycle USING path
SELECT*FROM subtree WHERENOT is_cycle;
Elsewhere, track a path column and stop when an id repeats, or cap depth. SQL Server stops at 100 levels by default (OPTION (MAXRECURSION n)).
2. Materialized path: store the whole ancestry
id | path
1 | 1
4 | 1/4
9 | 1/4/9
A subtree is a prefix search, which a B-tree index handles well:
SELECT*FROM category WHERE path LIKE'1/4/%';
Reads are very fast and depth comes from counting separators. Moving a subtree means rewriting the path of every descendant. PostgreSQL's ltree extension provides a proper type with GiST indexes and operators (path <@ '1.4'). SQL Server's hierarchyid type is the same idea built in.
It's a good fit for trees that are read much more than they're restructured, like categories or file paths.
3. Nested sets: left and right numbers
Each node stores lft and rgt values from a depth-first walk. A subtree is everything between them:
SELECT child.*FROM category parent
JOIN category child ON child.lft BETWEEN parent.lft AND parent.rgt
WHERE parent.id =4;
Reading subtrees is very fast, but inserting or moving one node renumbers about half the table, and concurrent writes contend heavily. It was popular before recursive CTEs existed. Today it's only worth it for large, nearly static trees.
4. Closure table: store every ancestor–descendant pair
Every node has a row for itself (depth 0) plus one for each ancestor. Subtrees, ancestors and "direct children only" are all simple indexed joins:
SELECT c.*FROM category c
JOIN category_closure cc ON cc.descendant = c.id
WHERE cc.ancestor =4;
It handles deep trees and multiple queries well, and it supports nodes with several parents (a DAG). The cost is storage (O(n × depth) rows) and more work on insert and move, usually handled by triggers or application code.
Which should you use?
Need
Pick
General purpose, frequent edits
Adjacency list + recursive CTE
Read-heavy, path-like data (categories, URLs, files)
Materialized path (ltree, hierarchyid)
Fast ancestor and descendant queries, DAGs
Closure table
Huge, almost-never-changing tree
Nested sets
Start with the adjacency list. It's normalized, has no data to keep in sync, and with an index on parent_id a recursive CTE handles trees of tens of thousands of nodes in milliseconds. Add a materialized path or closure table next to it later if profiling shows subtree reads are the bottleneck.
Don't do this
Fetching one level at a time in application code (a query per node). That's the N+1 problem with extra steps.
A fixed number of columns (level1, level2, level3). Trees always grow one level deeper than you planned for.
Storing children as a comma-separated list in the parent row. It can't be indexed, joined or constrained.
Checklist
Default to parent_id plus a recursive CTE, with an index on parent_id.
Protect recursive queries from cycles.
Use a materialized path for read-heavy, path-shaped hierarchies.
Use a closure table for fast queries in both directions or for DAGs.
Usually not: store bytes in object storage and metadata in the database, with pre-signed URLs. When storing blobs in the database does make sense, and how to do it safely.
0.1 isn't exact in binary, so sums drift and cents go missing. Use DECIMAL or integer minor units, store the currency, and make rounding an explicit business rule.