Hacker News Reader

I recommend actually trying it (e.g. PostgreSQL CTE) on a problem you are trying to solve, if you haven't already.

People tend to report the bad cases, and stay silent for the good cases, which can contribute to a sense of fear of the feature. It could have started from a real issue, or it could just be someone trying to solve the traveling salesman problem with fake data and then blogging when it's slow.

Recursive CTEs work quite well in PostgreSQL, and the performance isn't at all what I'd call "horrible".

In the read-mostly case, you can use an adjacency list, use a query with a CTE to generate a heirarchy in a materialized view which is updated when you write to the main table, and query against the materialized view. You still pay the performance cost for the CTE, but only on writes, not every read.

This doesn't help in a write-heavy use case.