Modeling Hierarchical Data With PostgreSQL
Recursive CTEs use `WITH RECURSIVE` clause to define recursive query, adjacency lists store each node's children separately & use joins, nested sets store ancestors/descendants in separate table & use range queries.
He who plants a tree,
Plants a hope.
Plant a tree by Lucy Larcom 🌳
Intro
In this post I'm going to show you a couple of options for managing hierarchical data represented as a tree structure. This is the natural approach when you need to implement things like:
file system paths
org charts
discussion forum comments
a more contemporary topic: small2big retrieval for RAG applications [1][2]
Now, if you know what a graph is already, a tree is basically a graph without any cycles. Visually, it looks like this:
I've learned there are multiple alternatives for storin...