在 postgresql 中对图形数据建模的最佳方法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20776718/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Best way to model Graph data in postgresql
提问by jethar
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.
如何在 Postgresql 中存储和查询稀疏有向图或无向图。有类似pggraph 的东西，但仍处于计划阶段。
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
我意识到像 Neo4J 这样的专用图形数据库最适合于此。但是，有没有办法在 Postgresql 中实现相同的，通过使用扩展名或数据类型，这将避免添加另一个数据库引擎.dtata
回答by Denis de Bernardy
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.
但本质上，有一些技术可以在 SQL 数据库中高效查询图形数据，适用于高度专业化的场景。例如，如果您的兴趣在于寻找最短路径，您可以选择维护 GRIPP 索引。（它基本上有点像应用于图形的预排序树索引。）据我所知，这些技术都还没有标准化。
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.
话虽如此，看到你提到社交网络的评论，很可能他们每个人都会矫枉过正。如果您的兴趣主要在于获取与用户朋友相关的数据，或者在查询节点邻域的意义上等效的东西，那么在连接中您需要遍历的节点数量非常少，以至于不需要专用工具、数据结构等：只需使用递归 CTE。
http://www.postgresql.org/docs/current/static/queries-with.html
http://www.postgresql.org/docs/current/static/queries-with.html
For optimal performance when using the latter, shift as many whereconditions within the with (...)part of the query, so as to eliminate nodes early.
为了在使用后者时获得最佳性能，请在查询部分where内移动尽可能多的条件with (...)，以便尽早消除节点。
回答by Ian Gow
Use PostgreSQL for the underlying storage and use networkX or iGraph via PL/Python for the processing engine.
底层存储使用 PostgreSQL，处理引擎使用 networkX 或 iGraph 通过 PL/Python。
In their book "Graph Databases", Ian Robinson, Jim Webber, and Emil Eifrem make a distinction between the underlying storage and the processing engine. If you look at the answer I followed in a recent problem (see here), you will see that I'm using PostgreSQL for the underlying storage and networkX as the processing engine. The performance gain relative to my original solution was huge (and similar to the ones described in the "Graph Databases" book) and implementing it was very easy.
在他们的“图形数据库”一书中，Ian Robinson、Jim Webber 和 Emil Eifrem 区分了底层存储和处理引擎。如果您查看我在最近的问题中遵循的答案（请参阅此处），您会发现我使用 PostgreSQL 作为底层存储，使用 networkX 作为处理引擎。相对于我的原始解决方案的性能提升是巨大的（类似于“图形数据库”一书中描述的那些）并且实现它非常容易。
回答by Eron Lloyd
At this point I would recommend experimenting with AgensGraph, a promising multimodel distribution of PostgreSQLthat offers first class graph databases and queries from both SQL and Cypher. Note that it is a full server, and not an extension like PostGIS, although PostgreSQL extensions can be added to it.
在这一点上，我建议尝试使用AgensGraph，这是一种很有前途的 PostgreSQL 多模型发行版，它提供一流的图形数据库以及来自 SQL 和 Cypher 的查询。请注意，它是一个完整的服务器，而不是像 PostGIS 这样的扩展，尽管可以向其中添加 PostgreSQL 扩展。
回答by Duccio A
Since the question is generic, I would add a solution that can work for mostly planar graphs like street networks, PostgreSQL offers an excellent solution through Postgis topology. Postgis topology stores geometries as edges, nodes and faces and their relative relationships. This means that from the geometry of a street network you can select edges and their starting and ending nodes and from this easily build a graph in the processing engine of your choice (networkx or graph-tool for Pyhton are examples).
由于问题是通用的，我将添加一个解决方案，该解决方案可以用于街道网络等大多数平面图，PostgreSQL 通过 Postgis topology提供了一个出色的解决方案。Postgis 拓扑将几何存储为边、节点和面以及它们的相对关系。这意味着您可以从街道网络的几何形状中选择边及其开始和结束节点，并从中轻松地在您选择的处理引擎中构建图形（例如，networkx 或 Pyhton 的图形工具）。
As I said, though, Postgresql/Postgis topology works when we want to study geometries like street networks from the perspective of graph analysis.
不过，正如我所说，当我们想从图形分析的角度研究街道网络等几何图形时，Postgresql/Postgis 拓扑是有效的。
回答by Joe
Use AgensGraph from bitnine.net https://bitnine.net/
使用来自 bitnine.net https://bitnine.net/ 的AgensGraph
and if you want to visualise your graph you can use AgensBrowser also from bitnine
如果你想可视化你的图表，你也可以使用 bitnine 中的 AgensBrowser
Thanks Joe
谢谢乔
