JSONB Video Analytics Dashboards in PostgreSQL

One of the most appealing PostgreSQL features for a multi-region platform like TopVideoHub is JSONB: binary JSON storage with indexing and querying capabilities. Here's how to build a flexible analytics system that doesn't require schema migrations every time you add a new metric.

The Problem with Rigid Schema

Traditional analytics tables look like this:

-- Too rigid — adding new metrics requires ALTER TABLE
CREATE TABLE video_analytics (
    video_id TEXT,
    date DATE,
    region TEXT,
    views INTEGER,
    avg_watch_time REAL,
    mobile_views INTEGER,
    desktop_views INTEGER
);

Every new metric requires a migration and deployment. JSONB solves this with a flexible payload column that you can extend at any time.

JSONB-Based Analytics Schema

CREATE TABLE video_analytics (
    id          BIGSERIAL PRIMARY KEY,
    video_id    TEXT NOT NULL,
    date        DATE NOT NULL,
    region      TEXT NOT NULL,
    -- Core counters for fast WHERE filtering
    total_views INTEGER DEFAULT 0,
    -- Flexible payload for all other metrics
    metrics     JSONB DEFAULT '{}'::jsonb,
    updated_at  TIMESTAMPTZ DEFAULT now(),
    UNIQUE(video_id, date, region)
);

-- Index for region+date queries (most common access pattern)
CREATE INDEX idx_analytics_region_date ON video_analytics(region, date);

-- GIN index enables @> containment queries on metrics
CREATE INDEX idx_analytics_metrics ON video_analytics USING GIN(metrics);

-- Expression index for frequently-queried numeric field
CREATE INDEX idx_analytics_completion ON video_analytics
    USING BTREE (((metrics->>'completion_rate')::real));

Inserting and Merging JSONB Data

-- Upsert analytics row with JSONB merge using || operator
INSERT INTO video_analytics (video_id, date, region, total_views, metrics)
VALUES (
    'dQw4w9WgXcQ',
    CURRENT_DATE,
    'JP',
    15000,
    '{
        "avg_watch_time_seconds": 187,
        "completion_rate": 0.62,
        "device_breakdown": {
            "mobile": 0.78,
            "desktop": 0.18,
            "tablet": 0.04
        },
        "engagement": {
            "likes": 1240,
            "comments": 89,
            "shares": 340
        },
        "hourly_peak": 14
    }'::jsonb
)
ON CONFLICT (video_id, date, region) DO UPDATE SET
    total_views = video_analytics.total_views + EXCLUDED.total_views,
    metrics = video_analytics.metrics || EXCLUDED.metrics,
    updated_at = now();

The || operator merges JSONB objects, updating individual fields without rewriting the entire payload. This is key for incrementally updating analytics.

Querying Analytics for Dashboards

For TopVideoHub, understanding mobile-vs-desktop split matters across Asian regions. Japan and Korea skew heavily mobile; Taiwan and Singapore have more desktop engagement:

-- Top videos by mobile engagement in Japan last 7 days
SELECT
    va.video_id,
    v.title,
    va.total_views,
    (va.metrics->'device_breakdown'->>'mobile')::real AS mobile_ratio,
    (va.metrics->>'completion_rate')::real AS completion_rate
FROM video_analytics va
JOIN videos v ON v.video_id = va.video_id
WHERE va.region = 'JP'
  AND va.date >= CURRENT_DATE - interval '7 days'
  AND (va.metrics->'device_breakdown'->>'mobile')::real > 0.7
ORDER BY va.total_views DESC
LIMIT 20;

Aggregating Across Regions

-- Device breakdown comparison across all Asia-Pacific regions
SELECT
    region,
    SUM(total_views) AS total_views,
    ROUND(AVG((metrics->'device_breakdown'->>'mobile')::real)::numeric, 3) AS avg_mobile_ratio,
    ROUND(AVG((metrics->>'completion_rate')::real)::numeric, 3) AS avg_completion
FROM video_analytics
WHERE date BETWEEN '2026-02-01' AND '2026-02-28'
  AND region IN ('JP', 'KR', 'TW', 'SG', 'VN', 'TH', 'HK')
GROUP BY region
ORDER BY total_views DESC;

-- Cross-region engagement ratio: which videos are more popular in Korea than Japan?
SELECT
    video_id,
    MAX(CASE WHEN region = 'KR' THEN total_views END) AS kr_views,
    MAX(CASE WHEN region = 'JP' THEN total_views END) AS jp_views,
    ROUND(
        MAX(CASE WHEN region = 'KR' THEN total_views END)::numeric /
        NULLIF(MAX(CASE WHEN region = 'JP' THEN total_views END), 0),
        2
    ) AS kr_jp_ratio
FROM video_analytics
WHERE date = CURRENT_DATE
  AND region IN ('KR', 'JP')
GROUP BY video_id
HAVING MAX(CASE WHEN region = 'KR' THEN total_views END) > 10000
ORDER BY kr_jp_ratio DESC
LIMIT 10;

Python Integration for Batch Analytics

import psycopg2
import json
from datetime import date
from typing import Any

def record_analytics(
    conn,
    video_id: str,
    region: str,
    total_views: int,
    metrics: dict[str, Any]
) -> None:
    with conn.cursor() as cur:
        cur.execute("""
            INSERT INTO video_analytics (video_id, date, region, total_views, metrics)
            VALUES (%s, %s, %s, %s, %s::jsonb)
            ON CONFLICT (video_id, date, region) DO UPDATE SET
                total_views = video_analytics.total_views + EXCLUDED.total_views,
                metrics = video_analytics.metrics || EXCLUDED.metrics,
                updated_at = now()
        """, (video_id, date.today(), region, total_views, json.dumps(metrics)))
    conn.commit()

# Usage
record_analytics(
    conn,
    video_id="dQw4w9WgXcQ",
    region="JP",
    total_views=15000,
    metrics={
        "avg_watch_time_seconds": 187,
        "completion_rate": 0.62,
        "device_breakdown": {"mobile": 0.78, "desktop": 0.18, "tablet": 0.04}
    }
)

When to Add a Real Column vs JSONB

The key question: is this field used in WHERE clauses or ORDER BY? If yes, promote it to a real column with an index. If it's just for display or occasional aggregation, keep it in JSONB.

For TopVideoHub, total_views and region are real columns because they're in every query's WHERE clause. device_breakdown, completion_rate, and hourly patterns stay in JSONB because they're only accessed in dashboard queries.

---

This article is part of the Building TopVideoHub series. Check out TopVideoHub to see these techniques in action.