Best way to model Graph data in postgresql
How would one go about storing and querying sparse directed or undirected graphs in Postgresql. There is something like pggraph, but that is still in planning stage.
I realize dedicated graph databases like Neo4J are best suited for this. However is there way to implement same within Postgresql, by using extension or a data type, which would avoid adding another database engine.dtata
Answer
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.