使用 HyperLogLog 在 Postgres 中构建视频热力图
你想向编辑准确展示观众在视频的哪个位置停止观看。
为此，你需要一个热力图。该热力图显示视频每一秒的独立观众人数。
在 TrendVidStream，我们每天处理 4000 万个事件。我们使用一个简单的技术栈：PHP 8.4 和 Postgres。
问题在于基数（cardinality）。统计每秒的独立观众人数非常困难。
朴素的方法：
- 使用原始事件表。
- 执行
COUNT(DISTINCT viewer_hash)。 - 按视频和时间桶进行分组。
这种方法很快就失效了。对于 90 天的数据，你需要 36 亿行。查询耗时 9 秒。此外，独立计数无法进行汇总（roll up）。你不能通过累加“每小时独立人数”来得到“每日独立人数”，因为同一个人可能出现在两个时间段内。
解决方案：HyperLogLog (HLL)。
HLL 为数据集创建一个小的“草图”（sketch）。无论有多少数百万人观看，它都非常小，大约只有 10 KB。
为什么 HLL 适用于热力图：
- 固定大小：它始终保持很小。
- 可合并：你可以合并两个草图以获得全局计数。这解决了汇总问题。
我们的实现方式：
我们使用 postgresql-hll 扩展。我们使用了两张表。
- 暂存表（staging table）：一个只增表，PHP 在其中写入原始心跳数据。我们在这里不使用索引，以保持写入速度。
- 草图表（sketch table）：用于存储 HLL 数据。
我们使用 cron 作业将数据从暂存表移动到草图表。我们使用以下命令：
INSERT INTO video_heatmap (...)
SELECT ..., hll_add_agg(hll_hash_text(viewer_hash), 14, 5)
FROM beacon_staging
GROUP BY ...
ON CONFLICT (...)
DO UPDATE SET viewers = video_heatmap.viewers || EXCLUDED.viewers;
“||”运算符将新数据合并到现有的草图中。
结果：
- 查询速度：我们的热力图查询耗时 15 到 40 毫秒。旧方法需要 9 秒。
- 存储空间：我们的 90 天数据集仅为 40 GB。旧方法则需要 1.2 TB。
- 准确度：误差大约在 0.6% 到 1.4% 之间。对于热力图来说，这已经非常完美了。
经验教训：
- 在数据库内部进行哈希。不要在应用程序中进行哈希。
- 批量更新。不要逐行更新草图，否则会产生写入热点。
- 为哈希使用每日盐值（salt）。这既能保护隐私，又能保持每日草图的整洁。
如果你的仪表板因为 COUNT(DISTINCT) 而变慢，你不需要庞大的数据仓库。你需要的是 HLL。