* Fetch your whole ancestor lineage in 1 SELECT.
* Grab all your descendants in 1 SELECT.
* Get all your siblings in 1 SELECT.
* Fetch all descendants as a nested hash in 1 SELECT.
* Find a node by ancestry path in 1 SELECT.
* 2 SQL INSERTs on node creation
* 3 SQL INSERT/UPDATEs on node reparenting
None of the other approaches above have even remotely similar performance characteristics. If your tree is small (tens of nodes), you won't care. If it's bigger, you will.
Re:Representing Trees in PostgreSQL
by Todd on Sunday November 23, 2014 @02:31PM
(#8649618)
I wasn't aware of closure trees before, thanks. The presentation that you link to by Bill Karwin, along with a few other resources are in the comments by coleifer and chdir.
If you're using Rails (3.2 through 4.1), try this: http://mceachen.github.io/closure_tree/
Like it says in the README:
* Fetch your whole ancestor lineage in 1 SELECT. * Grab all your descendants in 1 SELECT. * Get all your siblings in 1 SELECT. * Fetch all descendants as a nested hash in 1 SELECT. * Find a node by ancestry path in 1 SELECT. * 2 SQL INSERTs on node creation * 3 SQL INSERT/UPDATEs on node reparenting
None of the other approaches above have even remotely similar performance characteristics. If your tree is small (tens of nodes), you won't care. If it's bigger, you will.