Overview
This page is the atomic definition. Hierarchical data modeling patterns live at 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
- postgres - PostgreSQL’s
ltreeextension provides a typed materialized path with indexable operators. - foreign-key - adjacency list uses a self-referential foreign key; materialized path does not require it.
- btree-index - a B-tree index on the path column enables fast prefix scans.
- query-planner - the planner can use index-only scans on path prefix queries with proper index configuration.
- jsonb-path - JSONB path expressions in PostgreSQL solve nested structure queries differently.
Citing this term
See Materialized Path (llmbestpractices.com/glossary/materialized-path).