A viewer scrubs to the 47-second mark of a viral clip, watches for three seconds, then jumps to the ending. Multiply that by two million playbacks a day across a few thousand trending videos, and you get the problem that broke our first analytics design: we wanted a per-second engagement heatmap for every video — "how many unique people watched second 47?" — and the obvious schema (one row per viewer per second watched) produced a table that grew by hundreds of millions of rows daily. Worse, it stored a viewer identifier next to a video timestamp, which is exactly the kind of behavioural profiling that turns a GDPR audit into a bad afternoon.
I run the backend for ViralVidVault, a European viral-video discovery platform, and "unique count, but privacy-safe, but cheap" is a recurring shape in our data problems. The answer we landed on is HyperLogLog (HLL) sketches stored directly in Postgres. This post is the working version of that system: the schema, the ingestion path in PHP 8.4, the merge queries that build a heatmap, and the privacy properties that let us keep it on the right side of the regulation. Everything here runs — I've stripped it down from production but not into pseudocode.
Why exact unique counts are the wrong tool
The naive heatmap is a COUNT(DISTINCT viewer_id) grouped by (video_id, second). To answer that exactly, the database has to remember every viewer_id it has ever seen for every second of every video. There is no cheating a COUNT(DISTINCT) — the cardinality of the set is only knowable if you keep the set.
For a 3-minute video that's 180 buckets, each potentially holding a set of millions of 8-byte IDs. Across our catalogue that's tens of gigabytes of set membership data whose only purpose is to be counted and thrown away. And it's radioactive from a compliance standpoint: (viewer_id, video_id, second_watched) is a behavioural record tied to an individual. Under GDPR that's personal data, it needs a lawful basis, it's subject to access and erasure requests, and it has to be minimised.
HyperLogLog inverts the trade. Instead of storing the set, you store a fixed-size sketch — for us, 1280 bytes per bucket regardless of whether one person or ten million people are in it — that estimates the set's cardinality to within about 2% error. You cannot ask a sketch "was viewer X here?" It is mathematically incapable of answering that. It only answers "roughly how many distinct things were added?" That lossy-by-design property is the whole point: it's a data-minimisation technique that happens to also be a storage optimisation.
How HyperLogLog actually estimates cardinality
The intuition is worth understanding before you trust it in production. You hash each element to a uniformly random bitstring. In a stream of random bitstrings, seeing a value that starts with k leading zeros is roughly a 1-in-2^k event — so if the longest run of leading zeros you've ever seen is 20, you've probably observed somewhere around 2^20 distinct values. A single such estimator is wildly noisy, so HLL splits the stream into many registers (2^14 = 16384 registers is a common precision), tracks the max leading-zero count in each, and takes a harmonic mean across all of them to crush the variance. The standard error works out to roughly 1.04 / sqrt(m), which for 16384 registers is about 0.81%.
The two properties that make it useful for a heatmap:
-
Mergeable. The union of two HLL sketches is the register-wise maximum. That means
unique viewers of second 47 across the whole dayis just the merge of every hourly sketch for second 47 — no re-scanning raw events. - Fixed size. A register array is a register array whether you fed it 10 or 10 billion elements. Storage is O(registers), not O(cardinality).
The postgresql-hll extension (originally from Citus, now community-maintained) gives you an hll column type and the aggregate/merge operators as native Postgres. Install it once:
-- Requires the hll extension package (e.g. postgresql-16-hll on Debian)
CREATE EXTENSION IF NOT EXISTS hll;
-- One sketch per (video, playback-second, hour-bucket).
-- The hour bucket keeps sketches small and lets us expire old data.
CREATE TABLE heatmap_sketch (
video_id BIGINT NOT NULL,
position_s INTEGER NOT NULL, -- second offset into the video
hour_bucket TIMESTAMPTZ NOT NULL, -- truncated to the hour
sketch hll NOT NULL DEFAULT hll_empty(),
PRIMARY KEY (video_id, position_s, hour_bucket)
);
-- Tune the sketch: log2(registers)=14 -> ~0.8% error, 1280 bytes/sketch.
ALTER TABLE heatmap_sketch
ALTER COLUMN sketch SET DEFAULT hll_empty(14, 5);
That PRIMARY KEY is the aggregation grain. When ingestion adds a viewer to second 47 during the 14:00 hour, it touches exactly one row and merges a single-element sketch into it. Reads merge across hour_bucket to get whatever window you want.
The privacy layer: what we hash and what we never store
Before anything reaches Postgres we have to decide what a "viewer" is, because that decision is the entire GDPR posture. We do not use a stable account ID or an IP address as the HLL element. We derive a rotating, salted pseudonym at the edge:
element = SHA256( daily_salt || video_id || coarse_client_signal )
The daily_salt is a random value rotated every 24 hours and never persisted after rotation. Because the salt changes daily, the same person on day two hashes to a completely different element — the sketch cannot be used to track someone across days, and the identifiers are unrecoverable once the salt is gone. Within a single day it's stable enough to deduplicate, which is all a daily heatmap needs. This is pseudonymisation done so that even we can't reverse it, which is a much stronger position than "we promise not to look."
We run that derivation in a Cloudflare Worker sitting in front of the origin, so the raw client signals never hit our own logs:
// Cloudflare Worker: derive the daily pseudonym at the edge.
// The raw IP / UA never leaves the edge; only the hash is forwarded.
export default {
async fetch(request, env) {
const url = new URL(request.url);
if (url.pathname !== '/beat') return new Response('not found', { status: 404 });
const videoId = url.searchParams.get('v');
const positionS = url.searchParams.get('p');
if (!videoId || !positionS) return new Response('bad request', { status: 400 });
// Coarse signal: country + a truncated UA class, never the full IP.
const country = request.cf?.country ?? 'ZZ';
const uaClass = (request.headers.get('user-agent') ?? '').slice(0, 24);
const dailySalt = await env.SALT_KV.get('current'); // rotated by a cron trigger
const material = `${dailySalt}|${videoId}|${country}|${uaClass}`;
const digest = await crypto.subtle.digest('SHA-256', new TextEncoder().encode(material));
const pseudonym = [...new Uint8Array(digest)].map(b => b.toString(16).padStart(2, '0')).join('');
// Forward only the non-reversible pseudonym to origin.
await fetch(`${env.ORIGIN}/ingest`, {
method: 'POST',
headers: { 'content-type': 'application/json', 'x-internal': env.INGEST_SECRET },
body: JSON.stringify({ video_id: +videoId, position_s: +positionS, pseudonym }),
});
// 1x1 transparent gif, cache-busted client-side.
return new Response(GIF_BYTES, { headers: { 'content-type': 'image/gif' } });
},
};
The origin receives { video_id, position_s, pseudonym } and nothing else. There is no row anywhere that says "this IP watched this video" — the edge saw the IP but didn't store it, and the origin stores a hash it can never invert. That is the difference between "analytics" and "surveillance," and it's also the answer I give when a legal review asks where the personal data is: after the salt rotates, there isn't any.
Ingestion in PHP 8.4
Our origin is PHP on LiteSpeed. The ingest endpoint takes the pseudonym, turns it into an HLL element, and upserts it into the right sketch. The key trick is hll_hash_bytea to build a single-element sketch, then hll_union in an ON CONFLICT clause so the whole thing is one atomic statement.
<?php
declare(strict_types=1);
// PHP 8.4 ingest handler. Assumes a PDO connection to Postgres with the hll extension.
final class HeatmapIngestor
{
public function __construct(private readonly \PDO $pg) {}
public function record(int $videoId, int $positionS, string $pseudonymHex): void
{
// Clamp position so a malformed beacon can't create absurd rows.
if ($positionS < 0 || $positionS > 86_400) {
return;
}
// Build a one-element sketch from the pseudonym and union it into the
// hourly bucket. date_trunc keeps the aggregation grain at one hour.
$sql = <<<'SQL'
INSERT INTO heatmap_sketch (video_id, position_s, hour_bucket, sketch)
VALUES (
:vid,
:pos,
date_trunc('hour', now()),
hll_add(hll_empty(14, 5), hll_hash_bytea(decode(:pseudo, 'hex')))
)
ON CONFLICT (video_id, position_s, hour_bucket)
DO UPDATE SET sketch = hll_union(
heatmap_sketch.sketch,
EXCLUDED.sketch
)
SQL;
$stmt = $this->pg->prepare($sql);
$stmt->execute([
':vid' => $videoId,
':pos' => $positionS,
':pseudo' => $pseudonymHex,
]);
}
}
One subtlety that bit us early: doing a write per beacon at viral-video volume hammers the same hot rows (the popular seconds of a popular video), and every upsert on the same (video_id, position_s, hour_bucket) serialises. The fix is to buffer in the application and merge in batches. We keep a short-lived in-process buffer keyed by (video_id, position_s), fold beacons into a local sketch, and flush it every few hundred milliseconds:
<?php
declare(strict_types=1);
final class BufferedHeatmapIngestor
{
/** @var array<string, string[]> map of "vid:pos" => list of pseudonym hex */
private array $buffer = [];
private int $pending = 0;
public function __construct(
private readonly \PDO $pg,
private readonly int $flushThreshold = 500,
) {}
public function offer(int $videoId, int $positionS, string $pseudonymHex): void
{
$this->buffer["{$videoId}:{$positionS}"][] = $pseudonymHex;
if (++$this->pending >= $this->flushThreshold) {
$this->flush();
}
}
public function flush(): void
{
if ($this->pending === 0) {
return;
}
$this->pg->beginTransaction();
$sql = <<<'SQL'
INSERT INTO heatmap_sketch (video_id, position_s, hour_bucket, sketch)
SELECT :vid, :pos, date_trunc('hour', now()),
hll_add_agg(hll_hash_bytea(decode(h, 'hex')), 14, 5)
FROM unnest(:hashes::text[]) AS t(h)
ON CONFLICT (video_id, position_s, hour_bucket)
DO UPDATE SET sketch = hll_union(heatmap_sketch.sketch, EXCLUDED.sketch)
SQL;
$stmt = $this->pg->prepare($sql);
foreach ($this->buffer as $key => $hashes) {
[$vid, $pos] = array_map('intval', explode(':', $key));
$arrayLiteral = '{' . implode(',', $hashes) . '}';
$stmt->execute([':vid' => $vid, ':pos' => $pos, ':hashes' => $arrayLiteral]);
}
$this->pg->commit();
$this->buffer = [];
$this->pending = 0;
}
}
hll_add_agg folds a whole array of hashes into one sketch server-side, so a flush that covers 500 beacons across, say, 40 distinct (video, second) pairs becomes 40 upserts instead of 500. At our peaks that cut ingest write volume by well over 10x and, more importantly, collapsed the lock contention on hot rows.
Building the heatmap from sketches
Reading is where HLL earns its keep. A heatmap for a video over the last 24 hours is a merge of every hourly sketch per position, then a cardinality estimate:
-- Per-second unique-viewer estimate for one video over a time window.
SELECT
position_s,
hll_cardinality(hll_union_agg(sketch))::bigint AS unique_viewers
FROM heatmap_sketch
WHERE video_id = $1
AND hour_bucket >= now() - interval '24 hours'
GROUP BY position_s
ORDER BY position_s;
hll_union_agg is the aggregate merge — register-wise max across all the hourly sketches for that second — and hll_cardinality reads the estimate out. This query touches maybe 180 positions times 24 hourly buckets = ~4300 tiny rows for a 3-minute video, versus scanning hundreds of millions of raw event rows. On our hardware it returns in single-digit milliseconds, which means we can compute heatmaps on demand instead of precomputing them.
Because the sketches are mergeable, the same stored data answers "last hour," "last 24 hours," and "all week" just by changing the WHERE on hour_bucket. There's no separate rollup table per window. And a cross-video question — "which second across the whole trending list has the highest retention cliff?" — is the same shape, just grouped differently.
One honest caveat: HLL union is exact-ish, but HLL intersection (via inclusion-exclusion) compounds error badly. If you need "viewers who watched both second 10 and second 120," HLL is the wrong tool — that's a genuinely harder problem and the estimates get unusable fast. We only ever union, never intersect, and I'd advise you to hold that line too.
Rendering and retention curves in Python
The API hands the raw per-second estimates to whatever's drawing the chart. Our reporting jobs are Python, and the useful transform is turning absolute unique counts into a retention curve — each second as a fraction of the opening second — which is what actually tells you where people drop off or rewind.
import psycopg
from dataclasses import dataclass
@dataclass
class HeatmapPoint:
second: int
unique_viewers: int
retention: float # fraction of the peak opening seconds
def fetch_heatmap(conn: psycopg.Connection, video_id: int, hours: int = 24) -> list[HeatmapPoint]:
sql = """
SELECT position_s,
hll_cardinality(hll_union_agg(sketch))::bigint AS uniq
FROM heatmap_sketch
WHERE video_id = %s
AND hour_bucket >= now() - (%s || ' hours')::interval
GROUP BY position_s
ORDER BY position_s
"""
with conn.cursor() as cur:
cur.execute(sql, (video_id, hours))
rows = cur.fetchall()
if not rows:
return []
# Baseline = average of the first 3 seconds, which smooths the
# HLL noise on the denominator (a single-second baseline is jittery).
opening = [u for s, u in rows if s < 3] or [rows[0][1]]
baseline = max(sum(opening) / len(opening), 1.0)
return [
HeatmapPoint(second=s, unique_viewers=u, retention=round(u / baseline, 4))
for s, u in rows
]
def find_rewind_spikes(points: list[HeatmapPoint], threshold: float = 1.15) -> list[int]:
"""Seconds where uniques jump above the local trend usually mean people
scrubbed back to re-watch a moment -- the 'good part' of a viral clip."""
spikes = []
for i in range(1, len(points) - 1):
local = (points[i - 1].unique_viewers + points[i + 1].unique_viewers) / 2 or 1
if points[i].unique_viewers / local >= threshold:
spikes.append(points[i].second)
return spikes
That find_rewind_spikes heuristic is genuinely useful for a viral-video product: a spike where uniques exceed the surrounding trend means people scrubbed backwards to rewatch a moment. On our platform those spikes correlate strongly with the clips that keep spreading, so we surface them to editors as "the moment." Note the deliberate 3-second baseline smoothing — with ~0.8% error you don't want a single noisy register deciding your denominator.
Expiry, storage, and where SQLite fits
Retention policy is a one-line cron because the hour_bucket column makes old data trivially droppable:
DELETE FROM heatmap_sketch WHERE hour_bucket < now() - interval '90 days';
That 90-day window is also our documented retention period in the privacy policy — the storage design and the compliance commitment are the same mechanism, which is the tidiest way to keep them honest. No orphaned personal data survives it, because there was no personal data in the sketches to begin with.
One architectural note for the small-scale case: not everything needs Postgres. Our per-video aggregate counters (total plays, total uniques per day) live in SQLite in WAL mode on the edge nodes, because they're read-heavy, tiny, and don't need cross-node merges. The HLL heatmap specifically wants Postgres because the mergeable-sketch aggregate is a first-class server-side operation there; SQLite has no native HLL type, and reimplementing the merge in application code throws away the main advantage. Use the database whose primitives match the operation — SQLite for the flat counters, Postgres for the sketches.
What this bought us
The migration from exact COUNT(DISTINCT) to HLL sketches did three things at once, which is rare:
- Storage dropped from tens of GB of raw behavioural rows to a heatmap table measured in hundreds of MB, because sketch size is independent of viewer count.
- Query latency for a full heatmap went from a table scan we had to precompute to an on-demand query in single-digit milliseconds.
-
Compliance got easier, not harder: there is no reversible viewer identifier anywhere in the pipeline, the retention window is enforced by a single
DELETE, and "where is the personal data" has a real answer — it evaporated with the daily salt.
The trade you accept is roughly 0.8% error on every count and a hard ban on set intersection. For a viral-video engagement heatmap — where the shape of the retention curve matters far more than whether second 47 had 1,004,210 or 1,012,880 viewers — that's a trade I'd make every time. If your product genuinely needs exact per-user attribution you're in a different regime and HLL won't save you. But if you've been storing raw (user, event) rows purely to COUNT(DISTINCT) them later, you're paying for storage and compliance risk to buy precision nobody asked for. Sketch it instead.
Top comments (0)