How to implement a better like, views, comment counters in PostgreSQL?

Databases
1

i’m working on a personal social media platform for a university which implements a star like system and i’m just counting votes and sum of stars on each request so i’m looking for a better more scalable option. should i use the counter in database like (total views + 1) each time someone votes ? or there are other more scalable methods ? this well help me also build "followers counter, following counter, number of posts a user has…)

localhost_4200_marwan.yadri.8 (3)
localhost_4200_marwan.yadri.8 (3)1920×1182 100 KB

2

I would personally use a small table with two foreign keys to your user table.

Table: Followers
-------
UserID - PK
FollowedByUserID

Then to get your followers, you simply count the rows

SELECT COUNT(*) FROM Followers WHERE UserID = 12345

If you want a count of the people he follows, you switch the query

SELECT COUNT(*) FROM Followers WHERE FollowedByUserID = 12345

Now if the site gets REALLY busy, you could consider adding a counter field to the user table which contains those metrics, but that also means you have two places to maintain when a follower is added/removed. That could be done via a trigger but something that would need to be considered that there are two maintenance points.

1 Like
3

there’s an old saying in IT – premature optimization is the root of all evil

don’t go looking for tricks to outsmart the database engine until you’ve got maybe a million users

until then, obtain your counts by running SELECT COUNT(*) queries from the basic tables

proper indexing will ensure excellent performance

3 Likes
4

yes but ofc i have to be aware of everything first to avoid future problems just for the sake of learning

5

there should be reasonable limits, though

you do ~not~ need to know how big sites like Facebook and Twitter handle their database design and optimize their queries

start with the basics, and remember that old IT saying

1 Like