Written by Kris Schultz on — 08:54 reading time
Postgres has supported the ltree datatype as far back as 8.3 (potentially even further back but 8.3 is the oldest version of docs that were available). The ltree datatype is Postgres’ implementation of materialized paths, allowing the storage of label trees (hence the name) that represent the path to a record in a hierarchical structure. Materialized paths, adjacency lists and nested sets represent the three primary methods for modeling hierarchical data in a relational database. I’ll give a brief overview of those methods but will not delve too deeply as there are plenty of online resources that discuss the varying approaches at length. Instead, I’ll talk about how and why I decided to use ltree for FOX, our content management system.
Modeling a Hierarchy in a Relational Datastore
A problem that every software engineer will encounter in their lifetime is how to model hierarchical data in a relational database. They will have to make the choice between three modeling methods: adjacency...Read more