There is also ltree
http://www.postgresql.org/docs/9.3/static/ltree.html
Adjacency lists also don't perform that badly with recursive queries in my experience.
Adjacency lists also don't perform that badly with recursive queries in my experience.
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?
Second, the materialized path's length exceeded the database's indexable length limit. (MySQL, the DB in question, has a default index limit of 767 bytes, so only first 767 bytes are indexed.)
There are ways around these issues (like using "UPDATE ... WHERE" rather than using an ORM to walk the tree and update... sigh). We also had other app-specific / design-specific issues too that swamped these issues performance wise.
I'm hopefully we won't need ancestor and descendant queries again in our re-design. But I'm keeping PostgreSQL with its Common Table Expression stuff--the thing that facilities recursive queries--in my back pocket. It's that or use a stored procedure to build the capability by hand.
http://illuminatedcomputing.com/posts/2014/09/postgres-cte-f...
The problem I was tackling was making the tree sorted like you see in threaded, scored comments.