Search Posts

How to store efficiently hierarchical data in PostgreSQL (LFT/RGT vs PARENT_ID)

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 left and right position.

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 left and 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.

See https://www.depesz.com/2011/12/16/rtrees-recursive-trees-what-did-you-think-about/ and https://www.postgresql.org/docs/current/static/queries-with.html

What is an ideal implementation for storing hierarchical data for such conditions in a postgresql database ?

Source: Stackoverflow MariaDB

Leave a Reply

Your email address will not be published. Required fields are marked *