How To Efficiently Fetch Large View Count on Social Platforms

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.

Better approach

There are two ways that help you fetch a large count of rows:

1. Store the count of rows in a separate table (for eg. a statistics table).

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.

2. 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).

Resources

To explore further and understand the difference, I'd recommend following the below resources for a brief idea on the topic by Hussein Nasser.

• Video on how to avoid SELECT COUNT(*)
• Code snippet for estimating count of rows.