数据库中的分层数据:递归查询vs.闭合表vs.图形数据库 [英] hierarchical data in a database: recursive query vs. closure tables vs. graph database

问题描述

我正在开始一个新项目，该项目具有一些层次结构数据，目前正在寻找将其存储在数据库中的所有选项.

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.

我使用的是PostgreSQL，它确实允许递归查询.我还研究了关系数据库的设计模式，例如闭合表，然后看了图数据库解决方案，例如neo4j.

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.

我发现很难在这些选项之间做出选择.例如:假设我的RDBMS允许递归查询，使用闭包表是否仍然有意义，并且在可维护性和性能方面，它与图形数据库解决方案相比如何?

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!

推荐答案

如果可以使用递归查询，则整个闭合表都是多余的:)

The whole closure table is redundant if you can use recursive queries :)

我认为拥有一个复杂的递归查询要好得多，这比处理单独表和相关触发器的额外IO(和磁盘空间)要好得多.

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.

我用postgres中的递归查询做了一些简单的测试.在表中有几百万行的情况下，查询仍然小于< 10ms，用于返回特定孩子的所有父母.归还所有孩子的速度也很快，这取决于父母的水平.它似乎更多地依赖于磁盘IO获取行而不是查询速度本身.这是由单用户完成的，因此不确定在负载下的性能如何.我怀疑如果您还可以将大部分表保存在内存中(并正确设置postgres)，那仍然会非常快.按父ID对表进行群集似乎也有帮助.

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.

这篇关于数据库中的分层数据:递归查询vs.闭合表vs.图形数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！