Building Video Heatmap Analytics with HyperLogLog in Postgres

The problem: counting unique viewers per second is a row explosion A viewer scrubs to 4:12 of a 9-minute trending clip, watches for 40 seconds, jumps back to the intro, then bounces. Multiply that by the few hundred thousand sessions a day that hit a mid-size aggregator and you get the question every product person eventually asks: which parts of this video do people actually watch, and how many distinct people watched each part? The naive answer is a watch_events table: one row per (user, video, second) . It works until it doesn't. A 9-minute video is 540 seconds. One viewer who watches the whole thing generates 540 rows. A million viewers across our catalog generate hundreds of millions of rows per day , and the only query anyone runs against them is COUNT(DISTINCT user_id) GROUP BY second . That COUNT(DISTINCT) is a sort-or-hash over the entire partition every single time someone opens the analytics tab. At TopVideoHub we aggregate trending video across Asia-Pacific, so a single popular clip can spike from zero to half a million sessions in an afternoon when it lands in the JP and KR feeds simultaneously. We did not want a fact table that grew by hundreds of millions of rows a day to answer a question whose answer is approximately fine. "Roughly 41,000 unique viewers saw the hook at 0:08" is just as actionable as "41,287". That tolerance for approximation is exactly what HyperLogLog is built for, and Postgres has a battle-tested extension for it. This post is the design we landed on: fixed-size HLL sketches, one per (video, time_bucket) , that you can merge, slice, and union across regions in milliseconds. The main app is PHP 8.4 on LiteSpeed behind Cloudflare, with our search layer on SQLite FTS5; the analytics store is a separate Postgres instance, and HLL is what made that store affordable. Why HyperLogLog instead of COUNT(DISTINCT) HyperLogLog estimates the cardinality of a set using a fixed amount of memory regardless of how many elements you throw at it. Th