Materialized Paths vs Adjacency Lists: Scaling Hierarchical Data in PostgreSQL

Hierarchical data looks simple until the system grows.

A course has chapters. Chapters have lessons. Lessons may contain videos, quizzes, assignments, and other learning objects. At first, representing this structure in PostgreSQL seems straightforward: give every node a parent_id and recursively query the tree.

That approach works. Until the application starts asking questions like: Give me an entire course subtree. Fetch every descendant under a chapter. Move an entire subtree. Load a large hierarchy efficiently without repeatedly querying the database.

I faced exactly this kind of problem while building the content architecture for an eLearning platform with 10,000+ learning objects. The key architectural decision was choosing between an Adjacency List model and a Materialized Path model.

1. The Problem: Hierarchical Learning Content

A simplified course hierarchy might look like this:

Course
├── Chapter 1
│   ├── Lesson 1
│   │   ├── Video
│   │   └── Quiz
│   └── Lesson 2
│       └── Video
├── Chapter 2
│   ├── Lesson 3
│   └── Lesson 4
│       ├── Video
│       └── Assignment
└── Chapter 3
    └── Lesson 5

Every item is a node in a tree. A simple relational representation is the Adjacency List model.

2. Adjacency Lists

The adjacency-list model stores only the immediate parent. Each node knows "Who is my parent?", but it does not directly know "Who are all my descendants?"

CREATE TABLE learning_objects (
    id UUID PRIMARY KEY,
    name TEXT NOT NULL,
    parent_id UUID REFERENCES learning_objects(id)
);

The Advantage

The schema is extremely simple. Creating a node or moving a node is cheap because the database only changes one relationship.

The Problem

The application frequently needs to ask: "Give me everything underneath this node." That requires recursive traversal using Common Table Expressions (CTEs):

WITH RECURSIVE tree AS (
    SELECT id, name, parent_id 
    FROM learning_objects WHERE id = $1

    UNION ALL

    SELECT child.id, child.name, child.parent_id
    FROM learning_objects child
    INNER JOIN tree parent ON child.parent_id = parent.id
)
SELECT * FROM tree;

This works well for many use cases. But when hierarchical reads become one of the application's dominant operations, repeatedly traversing large trees becomes an architectural concern.

3. Materialized Paths

Instead of storing only the immediate parent, a Materialized Path stores the node's position inside the hierarchy. The path becomes a representation of the node's ancestry.

CREATE TABLE learning_objects (
    id UUID PRIMARY KEY,
    name TEXT NOT NULL,
    parent_id UUID REFERENCES learning_objects(id),
    path TEXT NOT NULL
);

Now the database can answer "Give me everything underneath node 2" without recursively walking the tree. Conceptually:

SELECT *
FROM learning_objects
WHERE path LIKE '/1/2/%';

With the right PostgreSQL indexing strategy, this becomes particularly powerful for read-heavy hierarchical workloads.

4. The Real Difference

The important difference isn't simply: Adjacency List vs Materialized Path.
It is: Where do you want to pay the complexity cost?

• Adjacency List: You pay more during hierarchical reads (Recursive traversal → More database work).
• Materialized Path: You pay more during hierarchical writes (Move subtree → Path changes → Descendant paths update).

The content service platform performed significantly more hierarchical reads than structural moves. Therefore, optimizing the read path made more sense. We could store enough information to make that relationship directly queryable.

5. The Scaling Benefit vs. The Hidden Cost

With thousands of nodes, descendants share a common prefix (e.g., /1/2/3/, /1/2/4/). The parent path becomes a natural grouping mechanism utilizing PostgreSQL indexes efficiently.

However, Materialized paths are not magic. Their biggest weakness appears when moving branches. If you move a node, all descendants need new paths. A subtree move becomes a bulk update. They are better only when the workload justifies the trade-off.

6. Adjacency List vs Materialized Path

7. The PostgreSQL & TypeORM Perspective

Hierarchical modeling is not just about choosing a schema; it is understanding how PostgreSQL executes queries. If the application constantly needs subtree operations, storing only parent_id forces the database to reconstruct information that could be represented more directly.

Our application layer was built with NestJS and TypeORM. The important principle was not to blindly map the database structure into an ORM abstraction. Instead:

Business requirement 
        ↓ 
Query pattern 
        ↓ 
PostgreSQL data model 
        ↓ 
Indexes 
        ↓ 
TypeORM implementation

The database model was designed around actual access patterns. An ORM can make database operations easier to write, but it cannot rescue a fundamentally inefficient data model.

After designing the hierarchy around access patterns and optimizing the queries, the content service achieved sub-50ms query times across a 10,000+ learning object hierarchy, while significantly reducing database load.

8. When to Choose Which?

Choose Adjacency Lists When:

• The hierarchy is small.
• Structural changes happen frequently.
• Queries mostly involve immediate children.
• Deep subtree queries are uncommon.

Choose Materialized Paths When:

• The hierarchy is large (e.g., content-management or learning-platforms).
• Reads significantly outnumber structural writes.
• Subtree queries are frequent and deep hierarchical navigation is common.
• The structure is relatively stable.

9. Final Takeaway

The interesting part of this problem wasn't PostgreSQL syntax. It was recognizing that data modeling is an optimization decision based on workload.

Don't choose a database structure because it looks elegant on paper. Choose it based on how the system actually uses the data.

At scale, the difference between a good schema and a great schema is often not how easy it is to create the data. It's how efficiently the system can answer its most important questions. For our eLearning content system, Materialized Paths were the clear architectural choice.