From Trees to Tables: Storing Hierarchical Data in Relational Databases
Not long ago, I had to deal with a similar issue at work, where one of our backend systems was handling hierarchical data and required aggregations on it. The aggregation queries on the hierarchical data were too slow and violated the SLA for our API’s response time.
While I was going through the problem, I discovered that we were using the classic Adjacency List technique to store our data in the database.
Although the adjacency list is easy to implement and provides cheap node moves, inserts, and deletes, the major drawback is that it is expensive to find the level, ancestry & descendants of nodes. To find all the descendants of a given node, our existing system used a recursive query (expensive at scale).
We needed a way to store and query the hierarchical data more efficiently, without compromising the accuracy or the flexibility of the app. But how?
While researching the different data modeling techniques for hierarchical data, I stumbled upon two other techniques used across the industry besides the Adjacency List.
