Building a 10,000+ Learning Object Hierarchical Content System with PostgreSQL & NestJS

1. The Challenge: Deep Hierarchical EdTech Content

In modern educational management systems (such as the Oneness Academy platform), learning materials are structured as deeply nested hierarchies: Programs → Courses → Modules → Lessons → Topics → Assessment Items.

As the platform scaled to manage over 10,000+ individual learning objects, traditional database query approaches started hitting performance bottlenecks. Administrators frequently reordered lessons, moved modules between courses, and rendered full curriculum trees in real time. We required a solution that could handle:

• Instant subtree fetching (fetching a course and all its 500+ child nodes in a single DB query).
• Atomic subtree reordering without recursively querying parent-child chains.
• Strict relational integrity with zero deadlocks during concurrent administrative edits.

2. Database Model Evaluation

When evaluating hierarchical data structures in PostgreSQL for NestJS microservices, four primary models were benchmarked:

3. Materialized Path Implementation in NestJS & TypeORM

In the Materialized Path model, each node stores a string path representing its ancestor sequence, such as 1.4.12.8. Fetching an entire subtree for node 1.4 becomes a simple indexed SQL LIKE query:

SELECT * FROM learning_objects 
WHERE path LIKE '1.4.%' OR id = 4 
ORDER BY position ASC;

Single-Query Subtree Reordering Algorithm

The key innovation was writing a raw SQL query inside a NestJS TypeORM transaction to update the paths of all descendant nodes when a parent node moves, eliminating the need to load descendants into application memory:

// NestJS Content Service - Reorder Subtree Node
async reorderNode(nodeId: string, newParentPath: string): Promise<void> {
  const queryRunner = this.dataSource.createQueryRunner();
  await queryRunner.connect();
  await queryRunner.startTransaction();

  try {
    const targetNode = await queryRunner.manager.findOne(LearningObject, { where: { id: nodeId } });
    const oldPath = targetNode.path;
    const newPath = `${newParentPath}.${targetNode.id}`;

    // Update target node path and position
    targetNode.path = newPath;
    await queryRunner.manager.save(targetNode);

    // Atomically rewrite paths for ALL descendants in a single raw SQL query
    await queryRunner.query(`
      UPDATE learning_objects
      SET path = REPLACE(path, $1, $2)
      WHERE path LIKE $3
    `, [`${oldPath}.`, `${newPath}.`, `${oldPath}.%`]);

    await queryRunner.commitTransaction();
    await this.redisService.invalidatePattern(`content:tree:*`);
  } catch (err) {
    await queryRunner.rollbackTransaction();
    throw err;
  } finally {
    await queryRunner.release();
  }
}

4. Redis Cache-Aside & Scan Invalidation

To guarantee sub-50ms latency under high read volume, curriculum trees are cached in Redis under key patterns like content:tree:program_102.

Rather than flushing the entire Redis cache upon every edit, we implemented non-blocking SCAN iteration to invalidate only the affected program subtrees, preserving high cache hit ratios for unedited sections of the platform.

5. Results & Conclusion

By combining PostgreSQL materialized paths with NestJS TypeORM transactions and Redis caching:

• Curriculum tree API endpoints consistently serve requests in 28ms to 42ms.
• Reordering operations execute in a single SQL statement regardless of subtree depth.
• Database write overhead dropped by 75% compared to previous recursive approaches.