Querying Hierarchical Data with Postgres
Apr 15, 2024
•
Wietsche Calitz
Query hierarchical data in Postgres using recursive CTEs. Navigate up/down trees, track depth, and aggregate—great for parent-child data.
Hierarchical data is prevalent and simple to store, but querying it can be challenging. This post will guide you through the process of navigating hierarchical data in Postgres. It’s worth noting that these instructions are also applicable to other database systems that support Common Table Expressions, as introduced in SQL:1999.
Sample dataset
The dataset we’ll use is a good introduction for those unfamiliar with hierarchical data, also known as parent-child relationships. The example should be self-explanatory.
Develop the Query
Let’s start with a question: “In which continent is Brussels?” Instead of providing the SQL immediately, join me in developing a strategy that can serve as a foundation for answering more complex questions.
Step 1: Identify the starting node
Step 2: Include the immediate parent (or children if you’re navigating in the opposite direction)
Step 3: Make the query recursive
Postgres allows the creation of recursive queries, which are queries that execute on their own results. In this scenario, our goal is to tweak the ancestor common table expression so it references itself. This adjustment will prompt the query to repeat the procedure described in Step 2 until no more rows are returned.
Make sure you understand this step before continuing. It can be a bit of a mind-bending!
Step 4: Add a depth counter
The depth counter begins at zero and increases with each added ancestor to the output.
With a little more code, you can set a filter to return the continent based on the second-highest depth:
Top Down
Now, let’s consider a reverse navigation, for example, returning cities in Asia. If we skip to step 3 from the above instructions, it would appear as follows. Pay attention to how the direction of navigation changes by reversing the join conditions. The condition ancestor.parent_id = dataset.id navigates upwards, while ancestor.id = dataset.parent_id navigates downwards.
Aggregation
For example, calculating the sum of populations in European cities:
Notes on performance
Recursion depth
Limit the recursion depth to avoid potentially expensive queries. Understanding your data is crucial for this process. From the example above, we know that the maximum depth is 3. By limiting recursion, you can protect against hidden data quality issues:
ancestor AS (
SELECT *, 0 AS depth FROM start_node
UNION
SELECT dataset.*, depth+1 FROM dataset, ancestor
WHERE (ancestor.parent_id = dataset.id)
AND depth <= 3
)
Indexes
When working with persistent tables, the general approach is to create indexes for the join conditions, such as adding an index on ‘id’ and ‘parent_id’. However, for small datasets, this might not make a significant difference.
Relational vs Graph databases
Postgres, a Relational Database Management System (RDBMS), isn’t the best for navigating complex hierarchies compared to graph databases like Neo4j. However, the hierarchical data we often handle is a small part of a larger domain. This domain mostly consists of simpler data structures that are well-suited for relational databases. Therefore, unless the entire domain is conducive to graph structures, it’s likely that you’ll have to optimize the use of what the RDBMS provides.
In summary
Remember these steps the next time you encounter a hierarchical dataset in Postgres:
Begin by identifying your starting point. This means determining the exact node from which you’ll begin your data retrieval process.
Next, determine the immediate parent or child node, depending on the direction you intend to navigate. This is crucial for shaping the path of your navigation.
Once you’ve determined your direction, modify your query to make it recursive. This will allow your query to repeat until no more rows are returned.
Incorporate a depth counter into your query. This will keep track of how many layers deep into the database structure you have navigated, offering clear insight into the complexity of your data.
Finally, if necessary, you can filter or aggregate the results to customize the end product of your query.
Latest
Portable by design: Rethinking data platforms in the age of digital sovereignty
Build a portable, EU-compliant data platform and avoid vendor lock-in—discover our cloud-neutral stack in this deep-dive blog.
Cloud Independence: Testing a European Cloud Provider Against the Giants
Can a European cloud provider like Ionos replace AWS or Azure? We test it—and find surprising advantages in cost, control, and independence.
Stop loading bad quality data
Ingesting all data without quality checks leads to recurring issues. Prioritize data quality upfront to prevent downstream problems.