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!

Mauromaurois

That closure table thing is actually pretty neat. Unnecessary if you have recursive queries, but still pretty neat. Thanks for bringing it to my attention. – Thelen

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.

Hanoverian

Thanks, I thought it might be so – Mauromaurois

generally agree, although see Bill Karwin's SQL Antipatterns for a guide on when you might still want to use an alternative to the adjacency list approach (where parent_id is a field) – Oys

Recursive queries has one drawback, what if you want to delete a parent node? I guess that would make we as developers to run each delete call for that particular child node. That is a huge drawback. – Dentalium

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.

Discretion