We have a ratings system in our website that allows users to provide feedback to 3 different questions about a user.
We currently calculate the rating using averages using the following query on our RDBMS:
SELECT AVG(question_1), AVG(question_2), AVG(question_3)
FROM ratings
WHERE user_id = 1
This query does not scale even when the result is cached (and it is) since some of our users have millions of ratings.
Using a functional index is not an option because our RDBMS does not support them and using one would slow down writes significantly.
The solution I came up with is to create an append-only log of the averages in a given time frame and periodically merge them using a weighted average.
So we'd end up with the following data structure per user:
| question1_avg | question2_avg | question3_avg | ratings_count | timestamp | | 3.4 | 4.5 | 4.9 | 10000 | 1480429792 | | 5 | 5 | 5 | 30 | 1480429848 |
So the merge process would look like:
(3.4 * 10000 + 5 * 30) / 10030
The previous records would be tombstoned and the new average will be appended to the log.
Is this design correct? Will it work at scale?
Where will you store that kind of data? A document store (such as MongoDB), a key-value store (such as Redis) or an RDBMS?
Since this concept is very similar to CRDT Counters I tried to find a Convergent Replicated Data Type that allows you to calculate averages but I could not find one. Is there a data structure I missed?
Is there another algorithm or data type I should look into?