Data Hierarchies Part 1.5: PostgreSQL ltree with EF Core

PostgreSQL's ltree extension gives you materialised paths with database-native superpowers: GiST indexes, specialised operators like @> and <@, and powerful pattern matching. If you're committed to PostgreSQL and want the best hierarchy query performance,…

Data Hierarchies Part 1.4: Nested Sets with EF Core

Nested sets encode the entire tree structure into just two integers per node - Left and Right boundaries from a depth-first walk. Finding all descendants becomes a simple range query, and ORDER BY Left gives you perfect display order. The catch? Every insert…

Data Hierarchies Part 1.3: Materialised Path with EF Core

Materialised paths store the complete ancestry as a delimited string - like /1/3/7/ - making ancestors instantly readable without any joins. Perfect for breadcrumb generation and human-readable debugging, though moving subtrees means updating every…

Data Hierarchies Part 1.2: Closure Table with EF Core

Closure tables precompute and store every ancestor-descendant relationship, trading storage space for blazing-fast reads. This is the approach this blog uses for its comment system - when reads vastly outnumber writes, the extra insert complexity pays off with…

Data Hierarchies Part 1.1: Adjacency List with EF Core

The adjacency list is the simplest and most intuitive approach to storing hierarchical data - each row just points to its parent. It's what most developers reach for first, and for shallow trees with frequent moves, it's often the right choice. This article…

Data Hierarchies: Managing Hierarchical Data with EF Core and PostgreSQL (Overview)

Hierarchical data is everywhere in software development: threaded comments, organisational charts, file systems, product categories, and forum discussions. The eternal question of "how do I store a tree in a relational database?" has haunted developers since…