5

Is it bad practice to store calculated data in each row, or is it better to calculate at the application layer with every read from the database.

Storing in the database avoids the need to calculate multiple times, but if an error is made then data needs to be updated rather than just changing the application level calculations.

I think the latter is better, but is there a general rule of thumb?

I need to, for example, calculate total daily nutritional intake of foods. So various portions of energy of foods. I can either calculate the portion energy based on the corresponding food and store the energy of each portion in the portions table OR I can calculate from the join with the corresponding food every time.

You can imagine if you had to calculate yearly averages, monthly averages, daily averages, etc. for a long period of time it could get quite unwieldy.

What about using materialized views that would get recomputed every time old data, say a week or older, gets updated based on a trigger or something along those lines?

closed as too broad by LowlyDBA, John aka hot2use, McNets, mustaccio, paparazzo Apr 2 '18 at 15:31

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 4
    It will always be "it depends". You'll need to add a lot more information to get any useful answers. – LowlyDBA Apr 1 '18 at 2:23
  • @LowlyDBA very true, I have updated my question with a broad scenario. Is that specific enough to get a gist of it? – Adam Thompson Apr 1 '18 at 17:18
7

If computers were infinitely fast, then, No, you would never store a value that could be calculated from other columns in the database. Storing calculated values is a violation of database normalization.

Examples would include:

  • The extended cost on an invoice line that carries price and quantity fields.
  • The total cost of an invoice’s lines.

In the real world, we do sometimes choose to violate normalization. The motivation is usually for performance reasons. Never do so without much thought, and hopefully a consultation with another DBA or database developer. And always document thoroughly your decision and its specific motivation.

is there a general rule of thumb?

Yes: Normalize, and test/profile.

Always start with a normalized design. Load with fake data, test performance. Verify any discovered bottleneck is indeed due to your on-the-fly calculations.

  • 1
    What do you think about the asymmetric risk of bugs? Since you cannot fix the bug and simply have the recalculated values fixed at the application level, if any bug is introduced you would need to recalculate values. This could be very painful depending on the situation, especially within larger applications with lots of user data and high uptime SLA guarantees. – Adam Thompson Apr 1 '18 at 20:49
  • @AdamThompson I do not follow the meaning of your comment. If you have stored and used miscalculated values, you have reported incorrect results. If you have dynamically executed miscalculated values, you have reported incorrect results. Either is bad, and either must be dealt with. – Basil Bourque Apr 2 '18 at 19:24
  • The former must be fixed on the database level, this can pose a lot of problems. For example, how to determine which rows need to be recomputed? When do you run the calculations on production? What if a live customer updates the data the computations depend on during the recalculation (e.g. race conditions). This may not seem bad but once you get huge datasets and you can't just take down production because of zero downtime guarantees, etc. it can be very problematic. – Adam Thompson Apr 3 '18 at 0:24
  • @AdamThompson I can’t help you there. I don’t make zero downtime guarantees. – Basil Bourque Apr 3 '18 at 0:32
3

It depends. Some values are better to calculate immediately, some values are better to postpone until requested.

Once I had to precalculate distances between each two consecutive GPS points and increase an odometer value stored along with the point data. These spread calculations allowed me to get the distance between any two arbitrary points on the route just by substracting the first odometer value in the range from the last. Calculation of the distance on demand took a lot of time that was unacceptable. But these data aren't critical and if some point was missed odometer/distance values still have acceptable precision.

In my opinion if you can precalculate some data that speeds up some frequent and heavy queries - precalculate them. If you want to precalculate in advance without real necessity - do not waste CPU and IO resources.

2

An additional consideration: some values are calculated from a continually increasing number of other values. An example: a customer's current balance is calculated by taking adding all their charges and subtracting all their payments. If a customer has been active for years, this can require accessing a large number of rows each time you want to display their balance.

All the same concerns (time/resources to compute on the fly, vs. the recalculation cost if there's a problem) still apply, of course. The difference here is that the cost to compute increases over time.

A system I worked with used a hybrid approach: the customer balance was stored and modified as new charges or payments hit the account, but the value was recalculated from scratch during a nightly process.

  • Good points, I was thinking of a similar hybrid approach using materialized views with a trigger, but recalculating the values within the rows themselves would be another alternative as well. – Adam Thompson Apr 2 '18 at 16:41

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