Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Representing Trees in PostgreSQL

woss.name

Post reply on HN
Print view View original

21–30 of 60 posts

Re: Representing Trees in PostgreSQL

#21

Post by mikesname »

mattdeboard wrote: post #17

What are the upsides to this vice using a graph database like Neo4j?

You get all the benefits of a mature and very full-featured RMDBS.

Neo4j is a great database, and I use it myself for a project that involves lots of deep hierarchies. But it has a fairly sparse feature-set where schema enforcement and data integrity checking are concerned; you basically have to add all that stuff yourself at the application level which can amount to a lot of work.

Re: Representing Trees in PostgreSQL

#22

Post by btown »

techtalsky wrote: post #4

There'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

Looks like the main fork hasn't been updated since 2012. Is there an updated version you recommend for Rails 4 compatibility?

Re: Representing Trees in PostgreSQL

#24

Post by btown »

mikesname wrote: post #21
mattdeboard wrote: post #17

What are the upsides to this vice using a graph database like Neo4j?

You get all the benefits of a mature and very full-featured RMDBS. Neo4j is a great database, and I use it myself for a project that involves lots of deep hierarchies. But it has a fairly sparse feature-set where schema enforcement and data integrity checking are concerned; you basically have to add all that stuff yourself at the application level which can amount to a lot of work.

To second this, if you're using multiple languages to write to your database, you almost certainly want that schema enforcement at the DB level. We decided not to go with Neo as our canonical database for exactly that reason. Its main strength is probably as a follower or slave to a RDBMS or log file, which is how it seems to be used in enterprise - when you want to do graph-based analytics, you bulk-load a snapshot of your non-graph-stored DB into Neo, then run read-only workloads.

Re: Representing Trees in PostgreSQL

#25

Post by benjohnson »

twerquie wrote: post #12
barosl wrote: post #9

Does 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.

This pattern is commonly called "materialized path", if anyone is trying to search for it.

A similar system is also heavily used in the construction world in their "Work Breakdown Structure" - a borrowing on how they document airplane parts that are part of an assembly, that are part of a module, that part of a structure.

http://en.wikipedia.org/wiki/Work_breakdown_structure

Re: Representing Trees in PostgreSQL

#26

Post by gtaylor »

rosser wrote: post #8

Other comments have mentioned this as well, but recursive CTEs are a very effective technique for representing trees, and they work very well in PostgreSQL.

We have used these for our discussion features on Pathwright. With ltree, you don't have as much flexibility when sorting by multiple values. With the Recursive CTE, you can go nuts and still end up very efficient.

I believe Disqus uses this, as well: http://cramer.io/2010/05/30/scaling-threaded-comments-on-dja...

Re: Representing Trees in PostgreSQL

#28

Post by robertfw »

savagej wrote: post #19

I've used django-mptt to represent a tree of human phenotypes before. It was fast and quite an easy api to use. I was using PostgreSQL as the backend. https://www.djangopackages.com/packages/p/django-mptt/

I've also used django-mptt and just wanted to throw another vote behind it in case anyone is considering using it. Our data structure was a good fit for MPTT (many reads, few writes) so I can't comment on how it would behave in the opposite scenario. Nice API, very easy to use from a developer standpoint. We were using MySQL.

Re: Representing Trees in PostgreSQL

#29

Post by techtalsky »

btown wrote: post #22
techtalsky wrote: post #4

There'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

Looks like the main fork hasn't been updated since 2012. Is there an updated version you recommend for Rails 4 compatibility?

Hm, I didn't realize that. I'm using it on a Rails 4 project and haven't had any problems.
Post reply on HN