Hey, author here. I'm surprised this got posted to HN. Anecdotally, when I first looked into ways to handle this, we tested recursive CTEs, and they didn't perform as well for our workload.
The indexed arrays work well for doing lots of queries of descendants, though are harder to manage.
One nice thing is that this approach worked well with ActiveRecord, while at the time CTE support was a bit awkward.
I'd love to see some people benchmark other approaches.
I had versioning problem to solve recently, and recursive CTE was my first choice (did take some time to wrap my head around but was also performant enough), https://github.com/recipehub/recipehub-service/blob/master/d...