PostgreSQL JSONB for Multi-Region Video Analytics

TrendVidStream serves video content from 8 regions across vastly different markets — the UAE's multilingual melting pot, Finland's education-heavy viewership, and Czech Republic's thriving local creator scene. Tracking analytics for these diverse markets requires a schema that adapts to new metrics without constant migrations.

PostgreSQL's JSONB is the answer.

Schema Design

CREATE TABLE video_analytics (
    id          BIGSERIAL PRIMARY KEY,
    video_id    TEXT NOT NULL,
    date        DATE NOT NULL,
    region      TEXT NOT NULL,  -- AE, FI, DK, CZ, BE, CH, US, GB
    total_views INTEGER DEFAULT 0,
    -- Flexible analytics payload
    metrics     JSONB DEFAULT '{}'::jsonb,
    updated_at  TIMESTAMPTZ DEFAULT now(),
    UNIQUE(video_id, date, region)
);

-- Region+date is the most common access pattern
CREATE INDEX idx_analytics_region_date
    ON video_analytics(region, date DESC);

-- GIN index enables efficient @> containment and jsonb_path queries
CREATE INDEX idx_analytics_metrics_gin
    ON video_analytics USING GIN(metrics jsonb_path_ops);

-- Expression indexes for frequently-queried numeric fields
CREATE INDEX idx_analytics_completion
    ON video_analytics (((metrics->>'completion_rate')::real))
    WHERE metrics ? 'completion_rate';

CREATE INDEX idx_analytics_mobile
    ON video_analytics (((metrics->'device'->>'mobile')::real))
    WHERE metrics->'device' IS NOT NULL;

Inserting Rich Analytics

Each region has different analytics characteristics. UAE has high mobile usage and multilingual engagement, Nordic regions show higher completion rates, Czech content stays mostly local:

INSERT INTO video_analytics (video_id, date, region, total_views, metrics)
VALUES (
    'abc123',
    CURRENT_DATE,
    'AE',
    42000,
    '{
        "completion_rate": 0.54,
        "avg_watch_seconds": 143,
        "device": {"mobile": 0.83, "desktop": 0.14, "tv": 0.03},
        "language_mix": {"ar": 0.48, "en": 0.28, "hi": 0.15, "ur": 0.09},
        "cross_region_trending": ["AE", "SA", "EG"],
        "peak_hour_utc": 18
    }'::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();

Analytics Queries for Platform Insights

-- Compare mobile usage across TrendVidStream regions
SELECT
    region,
    SUM(total_views) AS total_views,
    ROUND(AVG((metrics->'device'->>'mobile')::real)::numeric, 3) AS avg_mobile,
    ROUND(AVG((metrics->>'completion_rate')::real)::numeric, 3) AS avg_completion
FROM video_analytics
WHERE date >= CURRENT_DATE - interval '30 days'
GROUP BY region
ORDER BY avg_mobile DESC;

This query reveals interesting patterns: UAE's 83% mobile viewership vs Finland's ~62% reflects infrastructure and device preferences across markets.

-- Find content that crosses borders in the Middle East/Nordic markets
SELECT
    video_id,
    array_agg(region ORDER BY total_views DESC) AS regions,
    sum(total_views) AS global_views
FROM video_analytics
WHERE date = CURRENT_DATE
  AND metrics->'cross_region_trending' IS NOT NULL
GROUP BY video_id
HAVING count(*) > 2
ORDER BY global_views DESC
LIMIT 20;

Querying JSONB Arrays

For language mix analysis specific to the UAE feed on TrendVidStream:

-- Videos with significant Arabic-language engagement in UAE
SELECT
    va.video_id,
    v.title,
    va.total_views,
    (va.metrics->'language_mix'->>'ar')::real AS arabic_ratio
FROM video_analytics va
JOIN videos v ON v.video_id = va.video_id
WHERE va.region = 'AE'
  AND va.date >= CURRENT_DATE - interval '7 days'
  AND (va.metrics->'language_mix'->>'ar')::real > 0.4
ORDER BY va.total_views DESC
LIMIT 10;

Python Integration

import asyncpg
import json
from datetime import date
from typing import Any

async def record_regional_analytics(
    pool: asyncpg.Pool,
    video_id: str,
    region: str,
    total_views: int,
    metrics: dict[str, Any]
) -> None:
    async with pool.acquire() as conn:
        await conn.execute("""
            INSERT INTO video_analytics
                (video_id, date, region, total_views, metrics)
            VALUES ($1, $2, $3, $4, $5::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))


# Record UAE analytics with language mix data
await record_regional_analytics(
    pool,
    video_id="abc123",
    region="AE",
    total_views=42000,
    metrics={
        "completion_rate": 0.54,
        "device": {"mobile": 0.83, "desktop": 0.14},
        "language_mix": {"ar": 0.48, "en": 0.28, "hi": 0.15}
    }
)

The JSONB approach means that when we want to track a new metric — like subtitle language preference for Czech viewers or Ramadan-period viewing patterns for UAE — we simply start including it in the payload. No ALTER TABLE, no migration, no deployment coordination.

For a platform like TrendVidStream serving 8 culturally distinct regions, that flexibility is essential.

---

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