Existem diretrizes ou regras práticas para determinar quando armazenar valores agregados e quando calculá-los em tempo real?
Por exemplo, suponha que eu tenha widgets que os usuários podem avaliar (veja o esquema abaixo). Cada vez que exponho um widget, posso calcular a classificação média do usuário na Ratingstabela. Alternativamente, eu poderia armazenar a classificação média na Widgetmesa. Isso me pouparia de ter que calcular a avaliação toda vez que eu exibir o widget, mas então eu teria que recalcular a avaliação média cada vez que um usuário avaliasse um widget.
Ratings Widgets
--------- -------
widget_id widget_id
user_id name
rating avg_rating <--- The column in question
Depende. O pré-cálculo de valores agregados coloca uma carga maior nas gravações, derivá-las torna as leituras mais difíceis
Se você estiver acessando frequentemente um valor derivado, o pré-cálculo é uma etapa de desnormalização válida. No entanto, neste caso, recomendo usar uma Visualização Materializada (uma visualização, gravada em disco, vinculada por gatilho às tabelas pai). A visualização materializada foi projetada para armazenar dados solicitados com frequência, mas tediosos para derivar, e é útil para números altos de gravações e números baixos de leituras.
Em um cenário de alta gravação e alta leitura, considere ter uma tarefa em segundo plano que imite os efeitos de uma visualização materializada, mas em menos tempo real. Isso apresentará uma média "boa o suficiente", preservando o desempenho de gravação e leitura.
Em nenhuma circunstância, você deve tratar a coluna derivada como uma coluna "normal": certifique-se de que os dados apresentados na "exibição" de Widgets estejam presentes em outro lugar na tabela, de modo que toda a tupla possa ser derivada por qualquer processo que você colocar. Essa questão também é fortemente específica do banco de dados (e da versão do banco de dados), portanto, recomendo testar o desempenho do agregado (com índices apropriados) em relação a um conjunto de dados de tamanho normal e a exibição materializada.
Com que frequência você precisa calcular/exibir os valores relativos à frequência com que os números subjacentes são alterados/atualizados.
Portanto, se você tiver um site com 10 mil acessos diários que exibe um valor que só mudará uma vez por hora, eu o calcularia quando os valores subjacentes mudarem (pode ser um gatilho de banco de dados, qualquer que seja).
Se você tiver uma ferramenta para ver as estatísticas, onde as estatísticas estão mudando a cada segundo, mas apenas três pessoas têm acesso, e elas só olham para ela algumas vezes por dia, é mais provável que eu calcule isso na mosca. (a menos que leve alguns minutos para calcular que ter dados obsoletos em primeiro lugar não é grande coisa ... e meu chefe me diz para apenas gerar a coisa do cron a cada hora, para que ele não tenha esperar quando ele quiser olhar para ele.)
Use a tabela StaleWidgets como uma fila de widgets "inválidos" (a serem recalculados). Use outra tarefa de encadeamento (assíncrona) que possa recalcular esses valores. O período ou momento dos recálculos depende dos requisitos do sistema:
Eu sugeriria calcular em tempo real se o cálculo não for muito complicado e no caso de você ter cálculos complexos e atualizações frequentes, mas não que a leitura de frequência seja possível armazenar dados calculados e ter uma coluna extra (bool) que armazenará se o recálculo é necessário ou não . por exemplo, defina esta coluna como verdadeira sempre que o recálculo deve ser feito, mas não faça o recálculo e quando você fizer o recálculo, defina esta coluna como falsa (isto representará o valor calculado mais recente e não obsoleto).
Dessa forma, você não precisa recalcular toda vez, você calculará apenas quando tiver que ler e o valor da coluna de recálculo for verdadeiro. Dessa forma, você economizará muito recálculo.
Para o caso em particular existe uma solução diferente onde você não precisa somar todas as avaliações e dividir pelo total para encontrar a média. Em vez disso, você pode ter um outro campo que contenha o total das avaliações, portanto, cada vez que você adiciona uma classificação, calcula a nova média usando (avg_rating×total+new_rating)/total, isso é muito mais rápido do que o agregado e reduz as leituras do disco, pois você não precisa acessar todos os valores de classificação. Soluções semelhantes podem se aplicar a outros casos.
A desvantagem disso é que não é uma transação ácida, então você pode terminar com uma classificação desatualizada. Mas ainda assim você pode resolver isso usando gatilhos no banco de dados. O outro problema é que o banco de dados não é mais normalizado, mas não tenha medo de desnormalizar dados em troca de desempenho.