PostgreSQL ltree- vs tree module vs integer/string arrays or string delimited path

Question

As you may know there's a module for PostgreSQL called ltree. Also you have the possibility to use the Array type for integers (*1, see comment below), which in this test shows to actually perform a little slower with its recursive queries, compared to ltree - except from the string indexing (*2, see comment below).

I'm not too sure about the credibility of these testresults though.

My biggest question here is actually about the relatively unknown, and almost undocumented tree module. Described here (where the documentation also can be found!!) as:

support for hierachical data types (sort of lexicographical trees), should go to contrib/tree, pending because of lack of proper documentation.

After reading through the documentation I'm a little bit confused as to whether or not I should base my big application (a CMS, where everything will be stored in a hiearchical tree structure - not only content, also files etc, so you can see this quickly scales up) around ltree, normal Materialized Path (Path Enumeration) with a delimited string or integer array as path - or if the relatively unknown "tree" module in theory should be the faster performing, more scalable and better solution of the two.

I've already analysed the different tree structure models and due to query performance, scalability and reordering of nodes and subtrees being my main requirements, I've been able to rule out Adjacency Lists (recursive CTE will not solve performance as the tree scales huge), Nested Sets/Intervals (not fast enough in some queries, considering its disadvantages when manupulating the tree), Closure Tables (terribly at scaling big in complex trees - not useful for such a large project as mine) etc and decided to go with the Materialized Path, which is super fast for read operations, and makes it easy to move subtrees and nodes around the hiearchy. So the question is only about the best of the proposed implementations for Materialized Path.

I'm especially curious in hearing your theories or experiences with "tree" in PostgreSQL.

Ricotta

I couldn't post more than 2 links, so here are the above referenced urls: 1) monkeyandcrow.com/blog/hierarchies_with_postgres and 2) gbif.blogspot.dk/2012/06/taxonomic-trees-in-postgresql.html if it might be of interest. – Ricotta

I suspect that contrib/tree might just be a reference to early versions of contrib/ltree. I've never heard of any such thing. – Ostracoderm

Ltree is really easy and efficient. leopard.in.ua/2013/09/02/postgresql-ltree – Lauer

Answer 1

AS far as I read, contrib/tree was never officially released, whereas ltree was merged into PostgreSQL's core.

I understand both use the same idea of labeled path, but tree only allowed integer labels, when ltree allows text labels that permits fulltext searches, thought the full path length is limited (65Kb max, 2Kb prefered).

Anthropomorphism