I am looking to model a hierarchical structure where the parent nodes on each level will contain calculations based on immediate children's values. For example the parent row may have an average of all the child rows (the calculations will be more complicated than that of course).
So every time a record changes this is going to possibly trigger an aggregation function on the parent which will trigger an aggregation on the parent of that record etc until the root node is calculated.
I am wondering if it's more efficient to build very complicated selects which do the calculations on the fly or whether to put the calculation results in the rows to make selects more efficient but make the writes more inefficient.
At this time I am not planning on using ltree but I would be interested in hearing your experiences with ltree as well.