96

Are there any guidelines or rules of thumb to determine when to store aggregate values and when to calculate them on the fly?

For example, suppose I have widgets which users can rate (see schema below). Each time I display a widget I could calculate the average user rating from the Ratings table. Alternatively I could store the average rating on the Widget table. This would save me from having to calculate the rating every time I display the widget, but then I'd have to recalculate the average rating each time a user rated a widget.

Ratings       Widgets
---------     -------
widget_id     widget_id
user_id       name              
rating        avg_rating  <--- The column in question
58

It depends. Pre-calculating aggregate values places a larger load on writes, deriving them makes reads more difficult

If you are frequently accessing a derived value, pre-calculation is a valid de-normalization step. However, in this instance, I recommend using a Materialized View (a view, written to disk, linked by trigger to the parent tables). The materialized view is designed to store frequently asked but tedious-to-derive data, and is useful for high numbers of writes and low numbers of reads.

In a high-write, high-read scenario, consider having a task in the background which mimics the effects of a materialized view, but in less than real-time. This will present a "good enough" average while preserving write and read performance.

In no circumstances, should you treat the derived column like a "normal" column: make sure the data presented in the Widgets "view" is present elsewhere in the table, such that the entire tuple can be derived by whatever processes you emplace. This question is also strongly database (and database-version) specific, so I recommend performance testing of the aggregate (with appropriate indexes) against a normal-sized data set and the materialized view.

  • I found this discussion to be very helpful regarding Materialized views. It's tailored towards Oracle but can be understood generically. For those like me who came from a MySQL background, a MySQL view is different from a Materialized view, it's virtual and does not store to disk (as talked about in the link I gave). – Siddhartha Sep 12 '17 at 2:05
  • upvoted! was about to ask the exact question, i need to store indicators such as SMA, EMA, WMA, RSI etc and they involve heavy computation, i was making a table currently which i was manually refreshing so far, these indicators change 100% everytime with new data coming in, what is a good strategy to maintain them, i know views will completely rip the database apart if everyone starts querying the views left and right – PirateApp Jun 8 '18 at 5:48
11

How often you need to calculate/display the values relative to how often the underlying numbers are changed/updated.

So, if you have a website with 10k daily hits that's displaying a value that's only going to change once an hour, I'd calculate it when the underlying values change (could be a database trigger, whatever).

If you have a tool to go and look at stats, where the stats are changing by the second, but you only three people have access, and they only look at it a couple of times a day, I'd be more likely to calculate it on the fly. (unless, it takes a couple of minutes to calculate that having had stale data in the first place isn't a big deal ... and my boss tells me to just generate the thing from cron every hour, so he doesn't have to wait when he wants to look at it.)

  • every 15 mins, 10 metrics that change 100% with 1000 rows per metric – PirateApp Jun 8 '18 at 5:49
  • 1
    @PirateApp and how many times does it get viewed in an average 15min window? What you can also do is generate it on first request in a 15min window and then cache it for the people who keep hitting reload over and over – Joe Jun 10 '18 at 21:19
  • it will be up on a website so i assume atleast 10000 people will be seeing it for starters, site isnt live so dont have actual data on user behavior – PirateApp Jun 11 '18 at 3:02
  • 1
    The issue is how many request relative to how often it changes. So if you pre-generate something that's going to be seen 10,000 times before the underlying data changes, then yes, pre-generate it. If it's only viewed once, or less than once (because the data changes so rapidly, or because the page is rarely looked at), then you don't. – Joe Jun 11 '18 at 13:00
4

Use StaleWidgets table as a queue of "invalid" (to be recalculated) widgets. Use other thread (asynchronous) task that can recalculate these values. Period or moment of recalculations depends on system requirements:

  • just on read,
  • at the end of month,
  • for some user at the start of day
  • ...
  • 1
    How do they get into the stale queue then? – jcolebrand Jan 6 '11 at 15:46
  • 2
    @jcolebrand ..at the moment of inserting/deleting rating (Ratings table) for some widget. At this moment the average value in Widgets table is becoming invalid, so we have to insert into table StaleWidgets record that has only one column - widget_id. Use trigger or stored proc that inserts record to Ratings table or your variant of course. – garik Jan 7 '11 at 17:57
2

I would suggest calulating on fly if calulation is not too cumbersome and in case where you have complex calcutaion and frequent update but not that frequnet read than you can store calculated data and have extra column(bool) which will store whether recalculation is required or not. e.g. set this column to true whenever recalculation should be done but don't do recalculation and when you do recalculation set this column as false(this will represent calculated value is latest and not stale).

This way you don't have to recalculate everytime, you will calculate only when you have to read and recalculation column value is true. This way you will save lots of recalculation.

1

For the case in particular there is a diferent solution where you don't have to add all the ratings and divide it by the total to find the average. Instead you can have an other field that contains the total of the reviews, thus each time you add a rating you calculate the new average using (avg_rating×total+new_rating)/total, this is much faster than aggregate and reduces disk readings since you don't have to access to all the rating values. Similar solutions might apply to other cases.

The downside of this is that it is not an acid transaction, so you might end with an outdated rating. But still you can solve that by using triggers in the database. The other problem is that the database is not normalized anymore, but don't be afraid to denormalize data in exchange to performance.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.