Aggregate data while inserting into raw table

user_4035864 0
Viewed 209

I'm currently building a forum alike application. Users will be able to see recent posts with the total like count. If the post is interesting to the user, they can like it as well and contribute to the total like count.

The normalized approach would be to have two tables: user_post(contains id, metadata ...), liked_post(which includes the user id + post id). When posts are getting queried, the like count would be determined with the COUNT() statement on the liked_post table grouped by the post id.

Im thinking of another approach, which requires no group by on a potential huge table. That would be to add a like_count column to the user_post table and break the normalization. This column would be always updated when a new liked_post entry gets inserted or deleted. That means: Every time a user likes a post -> there will be an update on the user_post table (increment the like_count column) + insert/delete entity in liked_post table (With a trigger or code in App layer).

Would this aggregation on the fly approach have any disadvantages, except for consistency concerns? This would enable very simple and fast select queries but Im not sure if the additional update would be an issue.

What are your thoughts ? Im really interested in the performance impact and not if you should do this from the project begin or not.

relational-database database-design postgresql
2 Answers

Your idea is correct and widely used. Problem that you will face:

  • how do you make sure that like_count is valid? Can this number be delayed or approximated somehow?

In general you can do this following ways

  • update like_count within application code
  • update like_count by triggers

If you want to have exact values correct you could accumulate those sums by triggers or do it programatically ensuring that like count update is always within same transaction that insert to liked_posts

Using triggers it could be something like this:

CREATE FUNCTION public.update_like_count() RETURNS trigger
  LANGUAGE plpgsql
  AS $$
    BEGIN
        UPDATE user_post SET  user_post.liked_count = user_post.liked_count + 1
WHERE user_post.id = NEW.post_id;
        RETURN NEW;
    END;
  $$;


CREATE TRIGGER update_like_counts 
  AFTER INSERT ON public.liked_posts 
  FOR EACH ROW EXECUTE PROCEDURE public.update_like_count();

Also you should handle AFTER DELETE by separate trigger. Be aware that depending on transaction isolation level you might enter concurrency problem here (if 2 inserts are done at the same time - like_count may be exactly same number for two transactions) and end up with invalid total.

user_2519200

So I've had a problem similar to this in the past, the solution I went with is similar to what you've described, which is having an aggregated stored value like_count. Like you mentioned the only downside would be consistency concerns however this problem exists even in the former.

The solution to something like this lies more in the application dev, so utilizing something like web-sockets to keep posts up to date, without too much fluff

When a user's browser/client loads a post they join a room with the post id, and when user interacts with a post ( like, dislike etc ) that interaction is broadcasted to all users in that room ( post id ).

Finally when it comes to finding out which users liked this post, you can query/load at the point of when the user clicks to find out. ~ cheers

user_17974190
Related