Hierarchical Data Storage: Design Patterns for Processing

Trees are everywhere in data you collect from the web. A category menu on an online shop nests five levels deep. A comment thread branches into replies of replies. A crawl produces a site map that is, by definition, a tree of URLs. The moment you try to persist any of these in a relational database, you hit the same awkward question: how do you store a tree in tables that only know rows and columns?

There is no single right answer — there are four well-worn design patterns, each with different trade-offs for reads, writes, and query complexity. This guide walks through all four with runnable SQL, then shows how modern databases (recursive CTEs, PostgreSQL ltree, JSON columns, native graph stores) have shifted the calculus since these patterns were first written down. If you are wrangling hierarchical data storage for a scraped catalog or a crawled dataset, this is the map.

The four patterns at a glance

Every relational approach to trees is a variation on one idea: encode each node's position in the hierarchy so you can reconstruct parent-child relationships with a query. The four classic patterns differ only in what they encode.

Keep this table in mind — by the end you will know exactly when each row is the right pick.

We will use the same running example throughout: a product category tree for an e-commerce store, the kind of structure you routinely extract when you scrape a product catalog. Root is Electronics; under it sit Televisions and Portable Electronics; under those, individual product types.

Adjacency list

The adjacency list is the simplest and most intuitive model: every row stores a pointer to its parent.

CREATE TABLE category (
  id        INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name      VARCHAR(100) NOT NULL,
  parent_id INT UNSIGNED DEFAULT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (parent_id) REFERENCES category (id)
    ON DELETE CASCADE ON UPDATE CASCADE
);

INSERT INTO category (name, parent_id) VALUES
  ('Electronics',        NULL),  -- id 1 (root: no parent)
  ('Televisions',        1),     -- id 2
  ('Portable Electronics', 1),   -- id 3
  ('LCD',                2),      -- id 4
  ('OLED',               2),      -- id 5
  ('MP3 Players',        3),      -- id 6
  ('Headphones',         3);      -- id 7

The foreign key on parent_id gives you real referential integrity, and ON DELETE CASCADE cleans up descendants automatically. A node with no children is a leaf; a node with no parent is the root; everything above a node is its ancestors, everything below its descendants.

Simple operations are trivial:

-- Direct children of "Televisions"
SELECT id, name FROM category WHERE parent_id = 2;

-- All leaf nodes (no children point back to them)
SELECT c.id, c.name FROM category c
LEFT JOIN category child ON child.parent_id = c.id
WHERE child.id IS NULL;

The historical weakness of the adjacency list was fetching a whole subtree of unknown depth. In the past you either self-joined once per level (fine for a fixed 3–4 levels, useless for arbitrary depth) or looped in application code, one query per level.

What changed: recursive CTEs

This is the single biggest reason the old advice is out of date. Every major database now supports recursive common table expressions — MySQL since 8.0, MariaDB since 10.2, PostgreSQL, SQL Server, SQLite, and Oracle all have them. A recursive CTE walks the tree in one statement, to any depth:

WITH RECURSIVE subtree AS (
    SELECT id, name, parent_id, 0 AS depth
    FROM category
    WHERE id = 1                      -- start at the root you want

    UNION ALL

    SELECT c.id, c.name, c.parent_id, s.depth + 1
    FROM category c
    JOIN subtree s ON c.parent_id = s.id
)
SELECT * FROM subtree ORDER BY depth;

The depth column even lets you indent the output into a readable tree. With recursive CTEs available, the adjacency list has become the sensible default for most workloads: it is the easiest to write, keeps referential integrity, makes inserts and moves cheap (update one parent_id), and reads the whole tree in a single query. Reach for something else only when you have measured a real read-performance problem.

Materialized path

The materialized path (also called path enumeration) stores each node's full lineage as a string, usually slash-delimited: /1/3/6/.

CREATE TABLE comment (
  id      INT NOT NULL AUTO_INCREMENT,
  body    VARCHAR(500) NOT NULL,
  path    VARCHAR(255) NOT NULL,
  PRIMARY KEY (id)
);

Comment threads are the canonical use case, so switch examples for a moment. To read an entire thread under comment 1, you match a prefix:

SELECT * FROM comment WHERE path LIKE '/1/%' ORDER BY path;

That single LIKE (with a left-anchored pattern, so an index can be used) pulls a whole subtree without recursion — the pattern's main appeal. Deleting a subtree is just as clean:

DELETE FROM comment WHERE path LIKE '/1/3/%';

The costs: paths are denormalized strings, so the database cannot enforce that they point at real ancestors, and moving a branch means rewriting the path of every descendant. The VARCHAR also caps your practical depth.

What changed: native path types

Rather than hand-rolling slash strings, use a purpose-built type:

• PostgreSQL ltree is a first-class labeled-path type with GiST indexes and operators like @> (is-ancestor) and ~ (lquery pattern match). It is dramatically faster and cleaner than LIKE on a text column.
• Document stores such as MongoDB model path arrays natively and index them well — the original pattern feels far less clumsy there than in a strict relational schema.

Nested set

The nested set model numbers nodes with a left and right value by walking the tree depth-first: you assign an incrementing counter on the way down (lft) and again on the way back up (rgt).

CREATE TABLE tree (
  id     INT NOT NULL AUTO_INCREMENT,
  name   VARCHAR(100) NOT NULL,
  lft    INT NOT NULL,
  rgt    INT NOT NULL,
  PRIMARY KEY (id)
);

A node's descendants are exactly the rows whose lft/rgt fall inside its own range, so a whole subtree comes back with no joins and no recursion:

-- Everything under the node with lft = 2, rgt = 15
SELECT * FROM tree WHERE lft BETWEEN 2 AND 15;

-- Or, relative to a parent row, in one self-join
SELECT child.* FROM tree parent
JOIN tree child ON child.lft BETWEEN parent.lft AND parent.rgt
WHERE parent.id = 3;

Reads are blazing fast, which is why nested sets were beloved for mostly-static, read-heavy trees. The catch is brutal writes: inserting or moving a single node can require renumbering half the table, because every lft/rgt to the right of the change must shift. Like the materialized path, it also gives you no referential integrity.

In 2026, nested sets are a niche choice. Pick them only for trees that are read constantly and almost never change — a fixed taxonomy or a published navigation menu, for example. For anything you re-crawl and update regularly, the write cost is a poor trade.

Closure table

The closure table stores the tree's relationships explicitly, in a second table. The main table holds the nodes; a separate table holds every ancestor-descendant pair, including each node's zero-distance relationship with itself.

CREATE TABLE node (
  id   INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  PRIMARY KEY (id)
);

CREATE TABLE closure (
  ancestor_id   INT NOT NULL,
  descendant_id INT NOT NULL,
  depth         INT NOT NULL,        -- 0 = self, 1 = child, 2 = grandchild ...
  PRIMARY KEY (ancestor_id, descendant_id),
  FOREIGN KEY (ancestor_id)   REFERENCES node(id),
  FOREIGN KEY (descendant_id) REFERENCES node(id)
);

For a node with id 3, you insert (3,3,0) plus one row per descendant. Reading any subtree is then a plain join, with no recursion at all:

-- Node 3 and its entire subtree
SELECT n.* FROM node n
JOIN closure c ON n.id = c.descendant_id
WHERE c.ancestor_id = 3;

-- Descendants only (exclude the node itself)
SELECT n.* FROM node n
JOIN closure c ON n.id = c.descendant_id
WHERE c.ancestor_id = 3 AND c.depth > 0;

The closure table is the most robust of the four: fast reads, cheap inserts (add the new node's rows), foreign keys for integrity, and easy queries for both descendants and ancestors. Its price is storage — the closure table grows faster than the node count — and a bit of bookkeeping to maintain the pairs on every write, usually handled by triggers or an ORM. Bill Karwin's SQL Antipatterns (the 2nd edition, SQL Antipatterns, Volume 1, 2022) champions it for exactly these reasons, and it remains the go-to when you need both fast reads and safe, frequent writes.

Choosing a pattern

There is no universal winner — the "best" model depends on your read/write mix and how deep and dynamic the tree is.

• Default to the adjacency list. With recursive CTEs it now covers most cases with the least code and full referential integrity. Start here.
• Reach for a closure table when reads must be fast and writes are frequent, or when you often query ancestors as well as descendants — think large, actively updated catalogs.
• Use nested sets only for read-heavy, rarely-changing trees where you want subtree reads with no recursion at all.
• Use a materialized path (ideally PostgreSQL ltree, not raw strings) for comment threads and breadcrumb-style lineage where prefix matching is the main query.

When a relational tree is the wrong tool

If your data is less a clean tree and more a tangled graph — products that belong to several categories, "related to" links, recommendation networks — a relational model of any flavor will fight you. Two escape hatches:

• JSON/JSONB columns. For small, self-contained subtrees you read as a unit (a single product's option tree, a config blob), store the branch as JSON in one row and let the app parse it. Modern engines can even index into JSON.
• Native graph databases. Neo4j and similar stores treat relationships as first-class citizens, so deep or many-to-many traversals that cripple SQL become natural. If hierarchy is your core domain, a graph database may beat every pattern above.

Practical notes for scraped and crawled data

Trees that come out of a scraper carry their own quirks, and the storage model has to accommodate them:

• Re-crawls mean churn. Categories get added, renamed, and re-parented between runs. That write pressure argues against nested sets and toward adjacency lists or closure tables.
• Sources disagree on structure. Merging category trees from several sites is easier when each node carries a stable natural key alongside its position, so normalize and deduplicate before you assign tree positions.
• Depth is rarely known in advance. Design for arbitrary depth from day one — recursive CTEs or a closure table — rather than hard-coding a fixed number of self-joins you will outgrow.

Building and maintaining these pipelines — crawling the source, extracting the tree, and loading it into a schema that stays fast as it grows — is exactly the kind of work we handle as a data as a service offering, so the storage model is chosen to fit your query patterns rather than the other way around.

FAQ

Does MySQL support recursive queries now? Yes. MySQL 8.0+ and MariaDB 10.2+ support WITH RECURSIVE. The old advice that "MySQL can't do recursion, so avoid the adjacency list" no longer applies — that constraint is what drove much of the historical preference for nested sets and closure tables.

Which pattern is fastest for reads? Nested sets and closure tables both read subtrees without recursion, so raw read speed is comparable. The closure table wins overall because its writes are far cheaper.

Can I mix patterns? Yes, and people do. A common hybrid keeps an adjacency list as the source of truth (simple, integrity-preserving writes) plus a derived materialized path or closure table for fast reads, rebuilt or trigger-maintained as data changes.

How do I stop infinite loops in a self-referencing tree? Enforce that parent_id can never point to the node itself or to one of its descendants, and cap recursion depth in your CTE. Corrupt cycles usually creep in during bulk imports of messy scraped data, so validate on load.