PostgresでHyperLogLogを使用してビデオヒートマップを構築する
視聴者がビデオのどの時点で視聴を停止したのかを、エディターに正確に示したいと考えているはずです。
これを実現するには、ヒートマップが必要です。このヒートマップは、ビデオの1秒ごとのユニーク視聴者数を示します。
TrendVidStreamでは、毎日4,000万件のイベントを処理しています。使用しているスタックはシンプルで、PHP 8.4とPostgresです。
問題はカーディナリティ（集合の要素数）です。1秒ごとのユニーク視聴者数をカウントするのは困難です。
素朴な方法：
- 生のイベントテーブルを使用する。
COUNT(DISTINCT viewer_hash)を実行する。- ビデオとタイムバケットでグループ化する。
この方法はすぐに限界に達しました。90日分のデータには36億行が必要になります。クエリには9秒かかりました。また、ユニークカウントはロールアップ（集計）ができません。「1時間あたりのユニーク数」を合計して「1日あたりのユニーク数」を求めることはできません。なぜなら、同じ人が両方に現れる可能性があるからです。
解決策：HyperLogLog (HLL)。
HLLはデータセットの小さな「スケッチ（sketch）」を作成します。視聴者が何百万人いても、サイズはわずか10 KB程度と非常に軽量です。
なぜヒートマップにHLLが適しているのか：
- 固定サイズ：サイズが小さく保たれます。
- マージ可能：2つのスケッチを結合（union）して全体のカウントを取得できます。これにより、ロールアップの問題が解決されます。
実装方法：
postgresql-hll 拡張機能を使用しています。テーブルは2つ用意します。
- ステージングテーブル：PHPが生のハートビートを書き込む、追記専用（append-only）のテーブルです。書き込み速度を維持するため、ここではインデックスを使用しません。
- スケッチテーブル：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%です。ヒートマップとしては完璧な精度です。
学んだ教訓：
- ハッシュ化はデータベース内で行う。アプリケーション側でハッシュ化してはいけません。
- 更新はバッチ処理で行う。1行ずつスケッチを更新すると、書き込みホットスポットが発生します。
- ハッシュには日次ソルト（salt）を使用する。これによりプライバシーが保護され、日次のスケッチをクリーンに保てます。
COUNT(DISTINCT) のせいでダッシュボードが遅くなっているなら、巨大なデータウェアハウスは必要ありません。必要なのはHLLです。