Representing Trees in PostgreSQL
woss.name
Representing Trees in PostgreSQL
1–10 of 60 posts
Re: Representing Trees in PostgreSQL
#2There's also the ltree module which is built-in to postgres: http://www.postgresql.org/docs/9.1/static/ltree.html
Re: Representing Trees in PostgreSQL
#3There 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.
Re: Representing Trees in PostgreSQL
#4There's a great Ruby on Rails gem called acts_as_sane_tree (after the non-recursive acts_as_tree) that uses postgresql's recursive queries. I'm using it on a project and have found it useful with good performance:
https://github.com/chrisroberts/acts_as_sane_tree
Re: Representing Trees in PostgreSQL
#5> We could have retrieved all the appropriate data with a single query, but that means reconstructing the tree from a flat set of rows we got back from PostgreSQL. Doing that sort of thing in the view would be hienous.
Implement a view helper to take the flat data and return a hierarchical structure that you can render.
Re: Representing Trees in PostgreSQL
#6with recursive is actually VERY quick in postgresql (adjacency model). currently using that and thoroughly tested it. simple, very quick and no hassle to update it (as the other options).
Compared to a mysql with nested set postgres using with recursive is a life changer :D
Re: Representing Trees in PostgreSQL
#7There are some more tricks you can do using postgres' arrays to efficiently query the data:
http://monkeyandcrow.com/blog/hierarchies_with_rails/
Namely using the && operator let's you make use of indices on the materialized paths. We've been using it in production for years to great effect.
Re: Representing Trees in PostgreSQL
#8Other comments have mentioned this as well, but recursive CTEs are a very effective technique for representing trees, and they work very well in PostgreSQL.
Re: Representing Trees in PostgreSQL
#9Does anyone remember how Drupal handles the hierarchical comments? It maintains a "sort key" field for each comment, which consists of multiple index numbers for all levels, much like the section numbers in Wikipedia.
1
1.1
1.2
2
2.1
2.1.1
2.1.2
2.1.2.1
In this way, displaying comments in a tree form is trivial. Just ORDER BY the sort key. I find it brilliant for applying to such an application.
Re: Representing Trees in PostgreSQL
#10> We could have retrieved all the appropriate data with a single query, but that means reconstructing the tree from a flat set of rows we got back from PostgreSQL. Doing that sort of thing in the view would be hienous. Implement a view helper to take the flat data and return a hierarchical structure that you can render.
And store the data in PostgreSQL as a modified-preordered list so that the data is already in the order you need to efficiently recreate the object graph (note that this implies many reads for each write, since tree modifications become the costly operation).