3 ms·
In Postgres, there is a data type ltree[0] which can materialize the full path in a DAG and help do relatively complex searches. Tree coordinates are typically
by e1g 6y ago
In Postgres, there is a data type ltree[0] which can materialize the full path in a DAG and help do relatively complex searches. Tree coordinates are typically stable, so it's easier and faster to recalculate the path on writes vs reads. We use parent_id + ltree to do search hundreds of thousands of relationships in milliseconds.
ltree is no match in power to CTEs, but it is perfect for a simple domain like the one used in the article.
[0] https://www.postgresql.org/docs/current/ltree.html https://www.postgresql.org/docs/current/ltree.html
- Tostino 6y agoI 2nd this recommendation. If nothing else, it helps to have fewer recursive queries which are still treated as optimization barriers when you need to do complex queries joining to your DAG. So using a nice indexable ltree is much better for performance when your queries get complicated.
- Guillaume86 6y agoSimilarly SQL Server has hierarchyid (https://docs.microsoft.com/en-us/sql/t-sql/data-types/hierarchyid-data-type-method-reference?view=sql-server-ver15 https://docs.microsoft.com/en-us/sql/t-sql/data-types/hierar...) if anyone is interested.