การสร้าง Video Heatmaps ด้วย HyperLogLog ใน Postgres

คุณต้องการแสดงให้บรรณาธิการ (editors) เห็นอย่างชัดเจนว่าผู้ชมหยุดดูวิดีโอที่วินาทีไหน

ในการทำเช่นนี้ คุณต้องมี heatmap ซึ่ง heatmap นี้จะแสดงจำนวนผู้ชมที่ไม่ซ้ำกัน (unique viewers) ในทุกๆ วินาทีของวิดีโอ

ที่ TrendVidStream เราประมวลผลเหตุการณ์ (events) ถึง 40 ล้านรายการในทุกๆ วัน โดยเราใช้ stack ง่ายๆ คือ PHP 8.4 และ Postgres

ปัญหาคือเรื่อง cardinality การนับจำนวนผู้ชมที่ไม่ซ้ำกันในแต่ละวินาทีนั้นเป็นเรื่องยาก

วิธีแบบธรรมดา (Naive way):

  • ใช้ตาราง events แบบดิบ (raw events table)
  • รันคำสั่ง COUNT(DISTINCT viewer_hash)
  • Group by วิดีโอและช่วงเวลา (time bucket)

วิธีนี้ล้มเหลวอย่างรวดเร็ว สำหรับข้อมูล 90 วัน คุณต้องใช้ถึง 3.6 พันล้านแถว ซึ่งทำให้การ query ใช้เวลานานถึง 9 วินาที นอกจากนี้ จำนวน unique counts ยังไม่สามารถทำ rollup ได้ คุณไม่สามารถนำ "uniques per hour" มาบวกกันเพื่อให้ได้ "uniques per day" ได้ เพราะคนคนเดียวกันอาจปรากฏอยู่ในทั้งสองช่วงเวลา

ทางออกคือ: HyperLogLog (HLL)

HLL จะสร้าง "sketch" ขนาดเล็กของชุดข้อมูล ซึ่งมีขนาดจิ๋วเพียงประมาณ 10 KB ไม่ว่าจะมีคนดูเป็นล้านๆ คนก็ตาม

ทำไม HLL ถึงเหมาะสำหรับ heatmaps:

  • ขนาดคงที่: ขนาดจะยังคงเล็กอยู่เสมอ
  • รวมกันได้ (Mergeable): คุณสามารถนำ sketch สองอันมา union กันเพื่อหาจำนวนรวมทั้งหมดได้ ซึ่งช่วยแก้ปัญหาเรื่องการ rollup

การนำไปใช้งานของเรา:

เราใช้ extension postgresql-hll และใช้ตารางสองตาราง

  1. ตาราง staging: เป็นตารางแบบ append-only ที่ PHP จะเขียนข้อมูล heartbeat แบบดิบลงไป เราไม่ใช้ index ในตารางนี้เพื่อให้การเขียนข้อมูลทำได้อย่างรวดเร็ว
  2. ตาราง sketch: ใช้สำหรับเก็บข้อมูล HLL

เราใช้ cron job ในการย้ายข้อมูลจากตาราง staging ไปยังตาราง sketch โดยใช้คำสั่งนี้:

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;

ตัวดำเนินการ "||" จะทำการรวม (merge) ข้อมูลใหม่เข้ากับ sketch ที่มีอยู่เดิม

ผลลัพธ์ที่ได้:

  • ความเร็วในการ query: การ query heatmap ของเราใช้เวลาเพียง 15 ถึง 40 ms ในขณะที่วิธีเดิมใช้เวลาถึง 9 วินาที
  • พื้นที่จัดเก็บ: ชุดข้อมูล 90 วันของเราใช้พื้นที่เพียง 40 GB ในขณะที่วิธีเดิมต้องใช้ถึง 1.2 TB
  • ความแม่นยำ: เราพบความคลาดเคลื่อนประมาณ 0.6% ถึง 1.4% ซึ่งถือว่าสมบูรณ์แบบแล้วสำหรับ heatmap

บทเรียนที่ได้รับ:

  • ทำการ Hash ภายในฐานข้อมูล อย่าทำในแอปพลิเคชันของคุณ
  • ทำการอัปเดตแบบเป็นชุด (Batch) อย่าอัปเดต sketch ทีละแถว เพราะจะทำให้เกิด write hotspots
  • ใช้ daily salt สำหรับการ hash เพื่อปกป้องความเป็นส่วนตัวและช่วยให้ sketch รายวันของคุณสะอาด

หาก dashboard ของคุณทำงานช้าเพราะ COUNT(DISTINCT) คุณไม่จำเป็นต้องใช้ data warehouse ขนาดมหึมา แต่คุณต้องการ HLL

Source: https://dev.to/ahmet_gedik778845/building-per-second-video-heatmap-analytics-with-hyperloglog-in-postgres-2p22