I've done this a few times. First, see the list of PostgreSQL-based extensions and tools listed by other folks below; they're all good things to look at before you implement something from scratch. Regardless, this is probably a good approach if you are using Postgres anyway and just need a graph for part of your application.
For short graph traversals, recursive queries and joins perform surprisingly well, even when compared against dedicated graph databases -- at least, the open source ones I've been able to try, including neo4j. The ones I've tried perform better than Postgres mostly when you get more than two hops away -- but then at least Neo and the MySQL thing tend to suffer if the data set doesn't fit in memory.
I had at least one project which reverted from a dedicated graph database to Postgres specifically because it needed decent on-disk performance (2TB per shard).
Anyway, to your question: you could build a PostgreSQL database like that, but why would you? If you have distinct types of entities (person vs. furniture vs. town), why would you denormalize them into generic node interitance? Having a completely abstracted database with one big table called "things" and another big table called "relationships" seems really attractive before you actually do it. Then it starts to suck.
Relational databases are designed to perform well, and to be easy to query, with "normalized" databases in which different types of entities are represented by different tables and relationships are defined by foreign keys. The further you get away from that model, the harder the database gets to use.
This is one of the problems with using graph concepts for questions which are not, fundamentally, graphs. Yes, you can define people's nationality as a graph from location to nation, but that makes it pretty hard to do voter registration stats. Graphs are a great tool for answering many important questions, but they are not a great tool for answering all questions.
Now, if you are answering an actual graphing question (i.e. show me everyone who shares something in common with me via at least two links within 3 hops), then proceed. But figure out what kinds of questions you specifically want to answer before you get too far.
I noticed the same - the "explosion of tables" when I was doing the actual designing. But, almost accidentally, it also turned out that the tables were almost all either 5NF or 6NF. So I thought this is just a highly normalized setup anyway - something traditional databases are supposed to be designed for.
The list of Postgres-basd extensions mentioned in this thread turned out quite relevant actually! I'm still going through them all, seeing if I can reuse something..
Comments
I've done this a few times. First, see the list of PostgreSQL-based extensions and tools listed by other folks below; they're all good things to look at before you implement something from scratch. Regardless, this is probably a good approach if you are using Postgres anyway and just need a graph for part of your application.
For short graph traversals, recursive queries and joins perform surprisingly well, even when compared against dedicated graph databases -- at least, the open source ones I've been able to try, including neo4j. The ones I've tried perform better than Postgres mostly when you get more than two hops away -- but then at least Neo and the MySQL thing tend to suffer if the data set doesn't fit in memory.
I had at least one project which reverted from a dedicated graph database to Postgres specifically because it needed decent on-disk performance (2TB per shard).
Anyway, to your question: you could build a PostgreSQL database like that, but why would you? If you have distinct types of entities (person vs. furniture vs. town), why would you denormalize them into generic node interitance? Having a completely abstracted database with one big table called "things" and another big table called "relationships" seems really attractive before you actually do it. Then it starts to suck.
Relational databases are designed to perform well, and to be easy to query, with "normalized" databases in which different types of entities are represented by different tables and relationships are defined by foreign keys. The further you get away from that model, the harder the database gets to use.
This is one of the problems with using graph concepts for questions which are not, fundamentally, graphs. Yes, you can define people's nationality as a graph from location to nation, but that makes it pretty hard to do voter registration stats. Graphs are a great tool for answering many important questions, but they are not a great tool for answering all questions.
Now, if you are answering an actual graphing question (i.e. show me everyone who shares something in common with me via at least two links within 3 hops), then proceed. But figure out what kinds of questions you specifically want to answer before you get too far.
I noticed the same - the "explosion of tables" when I was doing the actual designing. But, almost accidentally, it also turned out that the tables were almost all either 5NF or 6NF. So I thought this is just a highly normalized setup anyway - something traditional databases are supposed to be designed for.
The list of Postgres-basd extensions mentioned in this thread turned out quite relevant actually! I'm still going through them all, seeing if I can reuse something..
Many thanks for the inputs, quite helpful.