We have a Java application and we want to create an abstract
HierarchicalId class to use as an extend of any entity that is part of a tree.
Our experience in MySQL showed us that it is much more efficient for READ operation to store the
See http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ for more details
We will read 95% of the time, but the 5% remaining will still require CREATE/UPDATE/DELETE, we also need to be scalable speaking of users but also data.
We are now using postgresql and my first idea was to store hierarchicaly using
right instead of
parent_id as it looks much more efficient when you need to read any part of the tree in MySQL.
Apparently this is not true with postgresql, where we can use recursive querying.
What is an ideal implementation for storing hierarchical data for such conditions in a postgresql database ?
Source: Stackoverflow MariaDB