How To Efficiently Fetch Large View Count on Social Platforms
Jotted down on 08 May 2022
Have you ever noticed the view count on a YouTube video? Or, a viral Instagram post? I have observed some discrepancies in the view count a couple of times (by switching clients, watching YouTube from a different account/incognito, etc.).
What is the problem
Is performing SELECT COUNT(*) on your table just to get a count worth it? Considering it contains millions of rows. It doesn't feel like a good use of resources.
People are not coming to YouTube just to look at the view count.
YouTube's primary focus is to deliver the video to the end-user. That's why clicking a video on YouTube loads the video first, and everything else after that.
There are two ways that help you fetch a large count of rows:
- Store the count of rows in a separate table (for eg. a
One way to achieve this is to set up triggers in your PostgreSQL database (or, change stream in MongoDB) on your content table and update the count in your statistics table for every INSERT/UPDATE/DELETE operation.
- Don't count the rows, use approximations instead
In PostgreSQL, you can use the EXPLAIN statement. You can extract the count of rows from the execution planner. But it is not accurate always, ANALYZE helps in updating these stats. It can be run manually or automatically (after performing VACUUM).
To explore further and understand the difference, I'd recommend following the below resources for a brief idea on the topic by Hussein Nasser.