👀 [en] Optimizing a Hierarchical Data Model with the Closure Table Pattern.

Querying Hierarchical Data Without Recursion!

When we design a database with parent-child relationships, we often use parent_id and child_id in each row.
A common way to query hierarchical data is using a recursive CTE (Common Table Expression).
However, recursive queries can become complex and hard to maintain as the service grows.

A more efficient alternative is the Closure Table Pattern.

Closure Table

The closure table pattern stores all ancestor-descendant paths in a separate table.
This makes queries simpler. We do not need recursion anymore.

Here is an example:

    A
   / \
  B   C
 /
D

closure table can look like this:

ancestor	descendant	depth
A	A	0
A	B	1
A	C	1
A	D	2
B	B	0
B	D	1
C	C	0
D	D	0

The biggest advantage of the closure table is that it is easy to read.

• No recursion needed
• Only Simple SELECT queries

Case Study

• Find All Descendants of A

SELECT descendant 
FROM closure
WHERE ancestor = 'A';

• Find Direct Children of A

select descendant 
from closure
where ancestoer = 'A' and dpeth = 1

• Find All Ancestors of D

select ancestor
from closure
where descendant = 'D'

• Find Direct Parent of D

select ancestor
from closure
where descendant = 'D' and depth = 1

But always remember: there is no silver bullet.

While closure tables make SELECT queries fast and easy, INSERT and UPDATE become more complex.

Choose the best approach based on your service needs.