Xây dựng Heatmap Video với HyperLogLog trong Postgres
Bạn muốn cho các biên tập viên biết chính xác thời điểm người xem ngừng xem một video.
Để làm được điều này, bạn cần một heatmap (bản đồ nhiệt). Heatmap này hiển thị số lượng người xem duy nhất cho mỗi giây của video.
Tại TrendVidStream, chúng tôi xử lý 40 triệu sự kiện mỗi ngày. Chúng tôi sử dụng một stack đơn giản: PHP 8.4 và Postgres.
Vấn đề nằm ở tính cardinality. Việc đếm số lượng người xem duy nhất cho mỗi giây là rất khó.
Cách làm thông thường:
- Sử dụng một bảng sự kiện thô (raw events table).
- Chạy lệnh
COUNT(DISTINCT viewer_hash). - Nhóm theo video và bucket thời gian.
Cách này nhanh chóng thất bại. Với 90 ngày dữ liệu, bạn cần tới 3,6 tỷ dòng. Các truy vấn mất tới 9 giây. Ngoài ra, các số lượng duy nhất không thể cộng dồn (roll up). Bạn không thể cộng "số lượng duy nhất mỗi giờ" để ra "số lượng duy nhất mỗi ngày" vì cùng một người có thể xuất hiện ở cả hai khoảng thời gian.
Giải pháp: HyperLogLog (HLL).
HLL tạo ra một "sketch" nhỏ của một tập dữ liệu. Nó cực kỳ nhỏ, chỉ khoảng 10 KB, bất kể có hàng triệu người xem đi chăng nữa.
Tại sao HLL hiệu quả cho heatmap:
- Kích thước cố định: Nó luôn giữ được sự nhỏ gọn.
- Có thể hợp nhất: Bạn có thể union hai sketch để có được tổng số lượng toàn cục. Điều này giải quyết được vấn đề cộng dồn (rollup).
Cách triển khai của chúng tôi:
Chúng tôi sử dụng extension postgresql-hll. Chúng tôi sử dụng hai bảng.
- Một bảng staging: Một bảng chỉ cho phép ghi thêm (append-only), nơi PHP ghi các heartbeat thô. Chúng tôi không sử dụng index ở đây để giữ tốc độ ghi nhanh.
- Một bảng sketch: Bảng này lưu trữ dữ liệu HLL.
Chúng tôi sử dụng một cron job để di chuyển dữ liệu từ bảng staging sang bảng sketch. Chúng tôi sử dụng lệnh sau:
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;
Toán tử "||" sẽ hợp nhất dữ liệu mới vào sketch hiện có.
Kết quả:
- Tốc độ truy vấn: Các truy vấn heatmap của chúng tôi chỉ mất từ 15 đến 40 ms. Cách làm cũ mất tới 9 giây.
- Lưu trữ: Tập dữ liệu 90 ngày của chúng tôi chỉ chiếm 40 GB. Cách làm cũ sẽ tốn tới 1,2 TB.
- Độ chính xác: Chúng tôi thấy sai số khoảng 0,6% đến 1,4%. Đối với một heatmap, mức này là hoàn hảo.
Bài học rút ra:
- Thực hiện hash bên trong cơ sở dữ liệu. Đừng hash trong ứng dụng của bạn.
- Thực hiện cập nhật theo lô (batch). Đừng cập nhật các sketch từng dòng một, nếu không bạn sẽ tạo ra các điểm nóng về ghi dữ liệu (write hotspots).
- Sử dụng một "salt" hàng ngày cho các mã hash. Điều này giúp bảo vệ quyền riêng tư và giữ cho các sketch hàng ngày của bạn luôn sạch sẽ.
Nếu dashboard của bạn bị chậm do COUNT(DISTINCT), bạn không cần một kho dữ liệu (data warehouse) khổng lồ. Bạn cần HLL.