4 ms·

What sort of problems are well-suited to graph databases? In other words, what are some scenarios where I will run into trouble using Postgres? edit: I've prev

What sort of problems are well-suited to graph databases? In other words, what are some scenarios where I will run into trouble using Postgres? edit: I've previously looked into ArangoDB.com, Dgraph.io, JanusGraph.org, and Cayley.io (to run on top of CockroachDB). I understand all of these are scalable distributed systems, and Postgres is not (CitusData.com aside). Do the benefits of these other systems mainly come when you outgrow single-node Postgres (which has JSONB for "document" storage, PostGIS.net, Timescale.com, etc)? edit 2: where can I find more technical, concrete examples than https://neo4j.com/use-cases https://neo4j.com/use-cases?

tiuPapa 8y ago
Good question, I should probably add this to my original question. Personally, I am trying to build a recommendation engine of sorts and I think Graph DB is suited for this but I am no expert.
chrisseaton 8y ago
> What sort of problems are well-suited to graph databases? If you have a graph, then a graph database, with built-in graph algorithms, will be able to run operations on your graph without pulling all the data out to a client. I'm not an expert in PostgreSQL but I don't think it has any graph algorithms?
- adamfeldman 8y ago
  Your question got me thinking, and led me to find this: https://github.com/bitnine-oss/agensgraph https://github.com/bitnine-oss/agensgraph "AgensGraph, a transactional graph database based on PostgreSQL"
- reaperducer 8y ago
  If you have a graph, then a graph database How does one know if it's time to migrate to a graph database? Right now I have a large (6.5GB) database that I have to sometimes work with, and the queries are getting more complex as more features are added to the project. Right now the data is stored in MySQL, but to get anything interesting out of it requires multiple queries and then sifting through the data later. It all seems cumbersome.
  - deleted 8y ago
    [deleted]
  - unixhero 8y ago
    Better port that sucker to Postgres sooner rather than later. You know, for future proofing.
  - chadcmulligan 8y ago
    It depends on the structure of the DB - 6.5GB is large if its attributes, if its documents then not so much. If its an index of documents then a different problem. Access patterns matter to. Like if its a 6.5 GB database that stores OLTP data, then yes thats large. If its a history of document modifications then probably small. If its historical data for a data cube sort of app, then its pretty small, I'd say tiny - I've worked on ones that get into the TB's.
    - reaperducer 8y ago
      It's all attributes; no documents being stored. About 2m records.
      - chadcmulligan 8y ago
        So thats a bit over 3k per record. Forgive my curiosity but whats the application domain? Thats a lot of info per record.
- allochthon 8y ago
  PG has recursive common table expressions, which allow you to traverse a tree or graph represented as an adjacency list in a single query. (No doubt there are other important things that graph DBs do.)
- chadcmulligan 8y ago
  I've implemented graphs and trees in a relational DB quite a few times. For graphs you can just store an adjacency list. I think most of the large DB's now have tree traversal extensions to SQL (I know Oracle does). If you need to manipulate on the server then PL/Sql (or equivalent) can do the job. The only time I could imagine that you'd need a specialist graph database would be if you had a very large number of nodes and some time / space intensive algorithm to run over them. Even in this case you could just store the data in a relational DB and use a low level interface (e.g. C) to write the specialist algorithm.
  - MichaelMoser123 8y ago
    Yes, you need a nosql graph databases only for applications that require eventual consistency - and that's a hard thing to get right in any case; an application can do without that if does not have to scale to a very large user base. In any case putting the graph nodes and links into sql tables is a much easier to do option.
- heurist 8y ago
  Are the built in graph algorithms useful these days? I tried neo4j a few years back and didnt find anything similar to eg networkx, which is the level of graph/network algorithms I use regularly. Neo4j made life much harder for me at the time, and I had to migrate that project back to postgres.
- vonholstein 8y ago
  My team opted for postgres over a graph db in one of our projects to model a network of devices. Primarily due to the pgRouting extension, which implements many graph algorithms in postgres. https://pgrouting.org/ https://pgrouting.org/
jkern 8y ago
My understanding is that graph databases are better suited to handling many-to-many relationships
- chiefalchemist 8y ago
  I'd put it slightly different, and more simplified: It's about the relationships (between the nodes).
dogweather 8y ago
How about hierarchical data? E.g., a country's laws / statutes? Thousands of text files organized in a hierachy. I've resorted to relational denormalizing and hacks to get decent performance. So I'm wondering if a graph database would be a better fit. E.g., I frequently need to query, "What is the list of ancestors from the object to the top of the tree?" In a relational system, this needs to be stored in some kind of data structure, which is redundant. But theoretically in a graph database, it'd be a fast O(log n) query if I'm not mistaken.

tiuPapa 8y ago

Good question, I should probably add this to my original question. Personally, I am trying to build a recommendation engine of sorts and I think Graph DB is suited for this but I am no expert.

chrisseaton 8y ago

> What sort of problems are well-suited to graph databases? If you have a graph, then a graph database, with built-in graph algorithms, will be able to run operations on your graph without pulling all the data out to a client. I'm not an expert in PostgreSQL but I don't think it has any graph algorithms?

adamfeldman 8y ago
Your question got me thinking, and led me to find this: https://github.com/bitnine-oss/agensgraph https://github.com/bitnine-oss/agensgraph "AgensGraph, a transactional graph database based on PostgreSQL"
reaperducer 8y ago
If you have a graph, then a graph database How does one know if it's time to migrate to a graph database? Right now I have a large (6.5GB) database that I have to sometimes work with, and the queries are getting more complex as more features are added to the project. Right now the data is stored in MySQL, but to get anything interesting out of it requires multiple queries and then sifting through the data later. It all seems cumbersome.
- deleted 8y ago
  [deleted]
- unixhero 8y ago
  Better port that sucker to Postgres sooner rather than later. You know, for future proofing.
- chadcmulligan 8y ago
  It depends on the structure of the DB - 6.5GB is large if its attributes, if its documents then not so much. If its an index of documents then a different problem. Access patterns matter to. Like if its a 6.5 GB database that stores OLTP data, then yes thats large. If its a history of document modifications then probably small. If its historical data for a data cube sort of app, then its pretty small, I'd say tiny - I've worked on ones that get into the TB's.
  - reaperducer 8y ago
    It's all attributes; no documents being stored. About 2m records.
    - chadcmulligan 8y ago
      So thats a bit over 3k per record. Forgive my curiosity but whats the application domain? Thats a lot of info per record.
allochthon 8y ago
PG has recursive common table expressions, which allow you to traverse a tree or graph represented as an adjacency list in a single query. (No doubt there are other important things that graph DBs do.)
chadcmulligan 8y ago
I've implemented graphs and trees in a relational DB quite a few times. For graphs you can just store an adjacency list. I think most of the large DB's now have tree traversal extensions to SQL (I know Oracle does). If you need to manipulate on the server then PL/Sql (or equivalent) can do the job. The only time I could imagine that you'd need a specialist graph database would be if you had a very large number of nodes and some time / space intensive algorithm to run over them. Even in this case you could just store the data in a relational DB and use a low level interface (e.g. C) to write the specialist algorithm.
- MichaelMoser123 8y ago
  Yes, you need a nosql graph databases only for applications that require eventual consistency - and that's a hard thing to get right in any case; an application can do without that if does not have to scale to a very large user base. In any case putting the graph nodes and links into sql tables is a much easier to do option.
heurist 8y ago
Are the built in graph algorithms useful these days? I tried neo4j a few years back and didnt find anything similar to eg networkx, which is the level of graph/network algorithms I use regularly. Neo4j made life much harder for me at the time, and I had to migrate that project back to postgres.
vonholstein 8y ago
My team opted for postgres over a graph db in one of our projects to model a network of devices. Primarily due to the pgRouting extension, which implements many graph algorithms in postgres. https://pgrouting.org/ https://pgrouting.org/

jkern 8y ago

My understanding is that graph databases are better suited to handling many-to-many relationships

chiefalchemist 8y ago
I'd put it slightly different, and more simplified: It's about the relationships (between the nodes).

dogweather 8y ago

How about hierarchical data? E.g., a country's laws / statutes? Thousands of text files organized in a hierachy. I've resorted to relational denormalizing and hacks to get decent performance. So I'm wondering if a graph database would be a better fit. E.g., I frequently need to query, "What is the list of ancestors from the object to the top of the tree?" In a relational system, this needs to be stored in some kind of data structure, which is redundant. But theoretically in a graph database, it'd be a fast O(log n) query if I'm not mistaken.