# Scalable way to manage aggregated statistics over time in hierarchy?

I'm using a closure table (Postgres through Django) to roll up stats through a hierarchical tree. When a node that is 10 layers deep has a value that increases, each parent's sum total of that value can also be easily calculated at any given time, thanks to the closure table.

Let's say our parent node has 4 children, each of which have 4 children, each of which have 4, etc to the point of 5 layers of depth.

Each node has a node score table in which we update their score with new entries that can change a couple times per day, and a calculated column called family score that is the sum of its sum of that value for all descendent nodes.

So here's my challenge: In this example, the very top parent has 1363 children whose changing scores influence it's own family score calculated value, along with the family score of every parent between that one and the one who's node score just changed.

I need a way to be able to map any node's family score over time. I've come up with two solution, neither of which I think are likely to scale well:

1. Add a family score table, and store a new static value for each ancestor node any time a node's score changes. Thus each write is multiplied by the depth of the node whose score is changing. The structure could easily get to 150-300 in depth.

2. When loading the data for the yearly score chart, first get an array of timestamps for each node score row within the date range specified and with the given parent specified, and then for each timestamp, find the latest node score row for every descendent and sum the values.

Option 1 seems like its less likely to break, but I'm guessing there's a best practice for this type of thing? Searching hasn't produce anything that quite addresses this use case. Any insights would be massively appreciated!!

• Another thought is to have a nightly ETL into a 'data warehouse' of sorts. We'd lose more granular time-based data than daily, but that may be just fine. – Evan H Jun 15 '18 at 17:57