Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question

4 Answers 4

up vote 35 down vote accepted

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.

share|improve this answer
3  
+1 Great answer. I would also add that some Materialized View solutions allow customizing what triggers the MView to be updated (timed, on commit, etc). Very flexible solution. –  ScottCher Jan 5 '11 at 14:54
1  
@Brian: Don't you mean that a materialized view is useful for high numbers of reads and low numbers of writes ? –  voithos Dec 12 '11 at 20:36

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.)

share|improve this answer

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
  • ...
share|improve this answer
1  
How do they get into the stale queue then? –  jcolebrand Jan 6 '11 at 15:46
    
@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

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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