What DBMS would you recommend when working with tree-like data structures?

Question

Database

The project is at the design stage. I choose DBMS. An nginx web server with FastCGI (PHP), onboard Ubuntu server 12.10 x64. This is a web front that allows users to edit some information in the database. The main data in the database is like a big tree, where there is one root element with a decent number of children, and the children have their own children, etc ...
I would like to hear advice on what to choose. Also, I would not want something completely exotic , because I need to store a small amount of non-tree data. Users, texts of some pages and so on.
Perhaps this is stupid, because I am not very competent in this matter. But I chose between MySQL and PostgreSQL.
In addition, the safety of the database is very important. I want to make backups / replications automatically, and preferably to another server. I will probably be hosted on some kind of VPS + I will buy a second one from another hosting provider to merge backups there.

Answer 1

If you really need a tree structure with a large number of elements, then you will have to abandon the relational database in favor of some kind of graph database, such as neo4j. If you are not ready for such a transition, then with the help of "traditional" solutions such functionality can be implemented quite easily. From the point of view of working with tree structures, all RDBMS are approximately equal, including MySQL or PostgreSQL.
From the point of view of "safety" and support for automatic backups and replication - again, all normal solutions can do this, including the ones you specified.
If I were you, I would take the solution that you are more familiar with if you need stability and reliability of the service. And the one with which you are less familiar if you do the service “for yourself”, for educational purposes, and if the downtime in case you set something up is not critical.

Answer 2

Neo4j is the right choice for graphs.
Regarding the implementation in RDBMS, there is a book by Joe Celko - Tree and Hierarchies in sql for smarties. The last word should hint at a contradiction with the phrase from the previous answer: "with the help of "traditional" solutions, such functionality can be implemented quite easily." :)

Answer 3

Postgresql, in addition to the above goodies specifically for working with trees, also has the ltree extension . Postgres is more complicated than mysql, but it is difficult to compare them in terms of functionality ...

Answer 4

Isn't it easier to master algorithms like Nested Sets and the like?

Answer 5

I strongly advise MongoDB, stores trees (called documents), can build queries on documents. But there are 2 nuances:
- There are no transactions (there are atomic update operations)
- There is no familiar query language (SQL)
The main ideas of mongodb:
www.mongodb.org/display/DOCS/Philosophy
According to performance - it will be faster than Postgre with recursive queries on samples. The write will always be faster than Postgre. This is according to the tests we have done.