hierarchical data in a database: recursive query vs. closure tables vs. graph database

Question

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!

Answer 1

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.

Answer 2

The level-field ("depth") of the closure table is redundant. It takes only one recursive query to compute it. That about sums it up.