---
title: "Materialized Path"
slug: "materialized-path"
category: "glossary"
tags: ["glossary", "backend", "database", "postgres", "trees", "hierarchical-data"]
status: "stable"
last_updated: 2026-05-14
summary: "Materialized path stores the full ancestor path of each tree node as a string, enabling fast subtree queries with simple LIKE or prefix operators."
related:
  [
    "[[backend/postgres]]",
    "[[glossary/foreign-key]]",
    "[[glossary/btree-index]]",
    "[[glossary/query-planner]]",
    "[[glossary/jsonb-path]]",
  ]
---

## Overview

This page is the atomic definition. Hierarchical data modeling patterns live at [[backend/postgres]].

## Definition

Materialized path is a pattern for storing tree-structured data in a relational database. Each row carries a `path` column containing the concatenated IDs of all its ancestors from root to self, separated by a delimiter (commonly `/` or `.`). For example, a category node with ID 42 whose ancestors are 1 and 7 stores the path `/1/7/42/`. To retrieve all descendants of node 7, issue `SELECT * FROM nodes WHERE path LIKE '/1/7/%'`. A `btree` index on the path column makes this prefix query fast. Inserts require only writing the parent's path plus the new node's ID. Re-parenting a subtree requires updating the path of every node in the subtree, which can be expensive for large trees. Depth is easily computed from the number of separators: `length(path) - length(replace(path, '/', ''))`. Materialized path is simpler to query than adjacency list (requires recursive CTEs) and easier to implement than nested sets, but it lacks integrity enforcement, because the database cannot validate that a path is consistent with actual parent-child relationships. Compare with PostgreSQL's `ltree` extension, which provides a native type and operators for materialized paths.

## When it applies

Use materialized path for read-heavy trees with infrequent re-parenting: category hierarchies, comment threads, file system representations, and org charts. For frequently restructured trees, adjacency list with recursive CTEs or closure tables may be more appropriate.

## Example

A comment tree stores paths like `/1/`, `/1/42/`, `/1/42/99/`. To display a full thread under comment 42: `SELECT * FROM comments WHERE path LIKE '/1/42/%' ORDER BY path`. No recursive query needed.

## Related concepts

- [[backend/postgres]] - PostgreSQL's `ltree` extension provides a typed materialized path with indexable operators.
- [[glossary/foreign-key]] - adjacency list uses a self-referential foreign key; materialized path does not require it.
- [[glossary/btree-index]] - a B-tree index on the path column enables fast prefix scans.
- [[glossary/query-planner]] - the planner can use index-only scans on path prefix queries with proper index configuration.
- [[glossary/jsonb-path]] - JSONB path expressions in PostgreSQL solve nested structure queries differently.

## Citing this term

> See [[glossary/materialized-path|Materialized Path]] (llmbestpractices.com/glossary/materialized-path).

## Related

- [[backend/postgres]]
- [[glossary/foreign-key]]
- [[glossary/btree-index]]
- [[glossary/query-planner]]
- [[glossary/jsonb-path]]