𝗕𝘂𝗶𝗹𝗱𝗶𝗻𝗴 𝗩𝗶𝗱𝗲𝗼 𝗛𝗲𝗮𝘁𝗺𝗮𝗽𝘀 𝘄𝗶𝘁𝗵 𝗛𝘆𝗽𝗲𝗿𝗟𝗼𝗴𝗟𝗼𝗴

𝗕𝘂𝗶𝗹𝗱𝗶𝗻𝗴 𝗩𝗶𝗱𝗲𝗼 𝗛𝗲𝗮𝘁𝗺𝗮𝗽𝘀 𝘄𝗶𝘁𝗵 𝗛𝘆𝗽𝗲𝗿𝗟𝗼𝗴𝗟𝗼𝗴

Counting unique viewers per second kills your database.

One person watching a 9-minute video creates 540 rows. One million viewers create millions of rows.

Most people use COUNT(DISTINCT). This is slow. It forces your database to sort everything every time you open the analytics tab.

Use HyperLogLog (HLL) in Postgres.

HLL gives a close estimate. It does not give a perfect number. For a heatmap, a close number is enough.

Why use HLL?

• Fixed size. Storage stays the same for one viewer or fifty million viewers.
• Mergeable. You combine regions or time buckets in milliseconds.
• Low error. A 1.6% error is invisible to the eye.

The Setup:

• Install the hll extension in Postgres.
• Store one sketch per video, region, and time bucket.
• Use a Python worker to batch writes.
• Cache the results at the edge.

The Trade-offs:

• No exact counts.
• No individual user data.
• No subtraction of sets.

The Result:

Your database stays small. Your analytics load in milliseconds. You save money on servers.

Source: https://dev.to/ahmet_gedik778845/building-video-heatmap-analytics-with-hyperloglog-in-postgres-42ah