How to create a proper post analytic system in PostgreSQL?

Question

i'm a young self-thought web dev from morocco to cut the story short i'm working on a special social media project i learnt coding through but it developed into a bigger project over time. i reached a point where i want to create proper analytic tools for my posts

so this is the post with the stars rating slide i'm storing it in an sql database with (user_id, post_id, stars) but i was wondering do i have to count() everytime a user fetch the post ? or there is a more efficient method to display post analytics cause i've read a lot but didn't find a solution to my case and i don't have a corporate experience to solve solutions of this scale. everytime i request the post i have to count the sum of stars and num of voters and then i do (stars/voters) to get the average and i don't know if it is a good idea to count everytime the post is requested but i'm not sure how to do it any other way.

• i have another place where i have to count views and i also don't know how to calculate them in a more efficient way. i'm open to all suggestions

Answer 1

Save the result of the count and average. Then read that saved value on most page views.

You could store the saved value in a new column of the slide table in your database, or you could add a cache service to your system (some people like to use Memcached or ValKey).

There is a risk that the saved average value is not current. For instance, if a new vote is cast, but the saved average is not immediately recalculated. This is a tricky problem.

There is an old joke from the 1990's by Phil Karlton:

There are only two hard things in Computer Science: cache invalidation and naming things.

He means it's hard to detect that a cached value is inaccurate, or to decide how long is too long to keep it in the cache.

Solutions include:

• Write code to update cached values when a vote is cast. This adds to the complexity of casting a vote, but you are assured the cached value is always accurate.

• Use a cache that automatically erases values after a fixed amount of time (say 1 hour). The next time a viewer tries to read the value from the cache, they find it's missing, and they have to recalculate it. So no more than one viewer per hour bears the cost of recalculating the value. Also it's possible the viewer may view an inaccurate value that is up to 1 hour out of date.

• Refresh values in the cache as a scheduled task, so no viewer is hit with that cost. But viewers may view inaccurate values until the values are refreshed.

Answer 2

You could store the data redundantly. Keep a star count on every item (post, whatever) that gets updated by a trigger whenever somebody votes or unvotes. That way, you don't have to count votes whenever an item is displayed.