How to properly store post ratings but quickly sort posts by average rating?

Question

PostgreSQL

There are posts about movies. There are many such posts. Each film has a rating from 1 to 10. Of course, if everything is normalized, then all films are in one table, all ratings are in another. But the task is to bring out the top 100 films with the highest average rating for all time.
Two options come to mind:
1) we could just take all the ratings, group by movie, calculate the averages, take the top hundred averages. But it seems to be inefficient
2) when the next rating is added, put it in the table, but also immediately recalculate it, and add it to a special column with the film. This sounds efficient for sampling, but what about competitive entry? Or will the transaction be queued when two evaluations are added at the same time?
Or are there other practices?

Answer 1

As already correctly stated, it should be:
Note. Not necessarily a table, not necessarily a relational base - it does not play any role for the task.
These 3 entities should not directly influence each other through any logic, because it can lead to rather sad consequences.
As for transactions, a lot of interesting things can happen there, including blocking, but the most important thing is that this logic should not be carried out on a client connection - as the size of the database increases, the request time will increase, and there are other interesting consequences. But! There is a solution.
In order to make everything beautiful, there are Message Brokers (RabbitMQ, Kafka, ... yes, whatever you want in principle - the choice depends on the requirements for the system).
The architecture is like this:
Like this. I hope this little example of their enterprise development world helps you.

Answer 2

When it comes to the rating of the film, and not the required level of immersion of graphite rods in the reactor core, accuracy and relevance can be neglected. Store the average rating along with the movie and recalculate it from time to time. This will be the fastest solution aak in terms of both writing and reading.