Best way to model Graph data in postgresql

Question

(Closed). This question is opinion-based. It is not currently accepting answers. question from:https://stackoverflow.com/questions/20776718/best-way-to-model-graph-data-in-postgresql

Answer 1

Question is, I think, way too vague and broad to give a precise answer...

In essence, though, there are some techniques to efficiently query graph data within an SQL database, that apply to highly specialized scenarios. You could opt to maintain a GRIPP index, for instance, if your interests lie in finding shortest paths. (It basically works a bit like pre-ordered tree index, applied to graphs.) To the best of my knowledge, none of these techniques are standardized yet.

With that being said, and seeing your comment that mentions social networks, the odds are that each of them will be overkill. If your interest primarily lies in fetching data related to a user's friends, or something equivalent in the sense that it amounts to querying a node's neighborhood, the number of nodes you'll need to traverse in joins is so tiny that there is no need for specialized tools, data structures, etc.: simply use recursive CTEs.

http://www.postgresql.org/docs/current/static/queries-with.html

For optimal performance when using the latter, shift as many where conditions within the with (...) part of the query, so as to eliminate nodes early.