This.

The project I'm on used materialized paths, which lead to great pain. I investigated nested intervals ... and they could't achieve the tree depths we needed (we were modeling a file system tree).

We are back to adjacency lists (using a parent ID) but redesigned to avoid the need for recursive ancestor and descendant queries.

_But_ the RDBMS doesn't support recursive queries and I've been curious about PostgreSQL's recursive query support. I played with it, but not on a fully loaded database with deep trees of data.

Does PostgreSQL recursive query support work well with deep trees (> 100 levels) on tables with tens of millions or more rows?