数据库中的分层数据：递归查询、闭包表、图形数据库
我正在开始一个具有一些分层数据的新项目，并且我正在研究目前将其存储在数据库中的所有选项。
我正在使用 PostgreSQL，它允许递归查询。我还研究了关系数据库的设计模式，例如 闭包表，我研究了图形数据库解决方案，例如 Neo4j。
我发现很难在这些选项之间做出决定。例如：假设我的 RDBMS 允许递归查询，那么使用闭包表是否仍然有意义？在可维护性和性能方面，它与图形数据库解决方案相比如何？
任何意见/经验将不胜感激！
I'm starting on a new project that has some hierarchical data and I'm looking at all the options for storing that in a database at the moment.
I am using PostgreSQL, which does allow recursive querying. I also looked into design patterns for relational databases, such as closure tables and I had a look at graph database solutions such as neo4j.
I'm finding it difficult to decide between those options. For example: given that my RDBMS allows recursive queries, would it still make sense to use closure tables and how does that compare to graph database solutions in terms of maintainability and performance?
Any opinions/experience would be much appreciated!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
如果您可以使用递归查询，则整个闭包表是多余的:)
我认为，有一个您必须弄清楚一次的复杂递归查询比处理单独表和关联触发器的额外 IO（和磁盘空间）要好得多。
我在 postgres 中用递归查询做了一些简单的测试。表中有几百万行，查询仍然 <<返回特定孩子的所有父母需要 10 毫秒。返回所有孩子也很快，具体取决于父母的水平。它似乎更多地依赖于获取行的磁盘 IO，而不是查询速度本身。这是单用户完成的，因此不确定它在负载下的表现如何。我怀疑如果你还可以将大部分表保存在内存中（并正确设置 postgres），它仍然会非常快。按父 ID 对表进行聚类似乎也有帮助。
The whole closure table is redundant if you can use recursive queries :)
I think it's much better to have a complicated recursive query that you have to figure out once than deal with the extra IO (and disk space) of a separate table and associated triggers.
I have done some simple tests with recursive queries in postgres. With a few million rows in the table queries were still < 10ms for returning all parents of a particular child. Returning all children was fast too, depending on the level of the parent. It seemed to depend more on disk IO fetching the rows rather than the query speed itself. This was done single user, so not sure how it would perform under load. I suspect it would be very fast still if you can also hold most of the table in memory (and setup postgres correctly). Clustering the table by parent id also seemed to help.
闭包表的级别字段（“深度”）是多余的。只需要一次递归查询即可计算它。这大约总结了这一点。
The level-field ("depth") of the closure table is redundant. It takes only one recursive query to compute it. That about sums it up.