Re:Hierarchical Structures in PostgreSQL (2020)
by mceachen on Sunday June 27, 2021 @09:03PM
(#27657019)
I'm actually using materialized views and CTE with PhotoStructure (where hierarchies are arguably the cornerstone of the product).
It's pretty funny that I landed on this implementation, given that I spent a couple years building https://github.com/ClosureTree/closure_tree (one of the most popular acts-as-hierarchy ActiveRecord gems), as it (unsurprisingly) uses closure trees.
When CTE isn't available, closure trees are nice, but boy howdy, does that closure tree table get gigantic with deeper graphs.
If CTE is available, closure trees don't even come close in performance and simplicity.
It's pretty funny that I landed on this implementation, given that I spent a couple years building https://github.com/ClosureTree/closure_tree (one of the most popular acts-as-hierarchy ActiveRecord gems), as it (unsurprisingly) uses closure trees.
When CTE isn't available, closure trees are nice, but boy howdy, does that closure tree table get gigantic with deeper graphs.
If CTE is available, closure trees don't even come close in performance and simplicity.
(Hint: materialized paths should use a unique separator: ASCII 0x1F is applicable: https://en.wikipedia.org/wiki/C0_and_C1_control_codes#Field_...)