DEV Community

ahmet gedik
ahmet gedik

Posted on

PostgreSQL JSONB for Tracking European Video Trends

#postgres #database #analytics #webdev

Why JSONB for Regional Analytics

At ViralVidVault, each video has different analytics depending on which European region it trends in. Polish engagement rates, Dutch device breakdowns, Scandinavian watch-time patterns — this data has variable shape and evolves as we add new metrics. PostgreSQL JSONB handles this perfectly.

The Schema 

CREATE TABLE video_trends (
    id SERIAL PRIMARY KEY,
    video_id TEXT NOT NULL REFERENCES videos(id),
    snapshot_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    total_views BIGINT NOT NULL,
    regional_data JSONB NOT NULL DEFAULT '{}',
    engagement JSONB NOT NULL DEFAULT '{}'
);

CREATE INDEX idx_trends_video ON video_trends(video_id, snapshot_at DESC);
CREATE INDEX idx_trends_regional ON video_trends USING GIN(regional_data);
CREATE INDEX idx_trends_engagement ON video_trends USING GIN(engagement);
Enter fullscreen mode Exit fullscreen mode

A typical regional_data value:

{
  "PL": {"views": 45000, "trending_rank": 3, "category": "Music"},
  "NL": {"views": 12000, "trending_rank": 15, "category": "Entertainment"},
  "SE": {"views": 8200, "trending_rank": 22, "category": "Music"},
  "GB": {"views": 95000, "trending_rank": 8, "category": "Music"}
}
Enter fullscreen mode Exit fullscreen mode

And engagement:

{
  "like_rate": 0.052,
  "comment_rate": 0.011,
  "avg_watch_seconds": 156,
  "devices": {"mobile": 0.58, "desktop": 0.35, "tablet": 0.07},
  "peak_hour_utc": 19
}
Enter fullscreen mode Exit fullscreen mode

Querying Regional Data

Find videos trending in Poland with high engagement:

SELECT v.title, t.regional_data -> 'PL' ->> 'views' AS pl_views,
       t.engagement ->> 'like_rate' AS like_rate
FROM video_trends t
JOIN videos v ON v.id = t.video_id
WHERE t.regional_data ? 'PL'
  AND (t.engagement ->> 'like_rate')::float > 0.05
ORDER BY (t.regional_data -> 'PL' ->> 'views')::int DESC
LIMIT 20;
Enter fullscreen mode Exit fullscreen mode

The ? operator checks if the key 'PL' exists. The ->> operator extracts a text value. Cast to float or int for numeric comparisons.

Aggregate views by region across all videos:

SELECT region.key AS region,
       COUNT(*) AS video_count,
       SUM((region.value ->> 'views')::bigint) AS total_views,
       AVG((region.value ->> 'trending_rank')::int) AS avg_rank
FROM video_trends t,
     jsonb_each(t.regional_data) AS region
WHERE t.snapshot_at > NOW() - INTERVAL '24 hours'
GROUP BY region.key
ORDER BY total_views DESC;
Enter fullscreen mode Exit fullscreen mode

jsonb_each unpacks the JSONB object into key-value rows. This lets you aggregate across all regions in a single query — something that would require separate columns (views_pl, views_nl, views_se...) without JSONB.

Updating JSONB Incrementally

When new trending data arrives, update specific regions without overwriting the entire object:

-- Add or update a single region
UPDATE video_trends
SET regional_data = jsonb_set(
    regional_data,
    '{NO}',
    '{"views": 5400, "trending_rank": 18, "category": "Nature"}'::jsonb
)
WHERE video_id = 'abc123'
  AND snapshot_at = (SELECT MAX(snapshot_at) FROM video_trends WHERE video_id = 'abc123');

-- Merge new engagement data
UPDATE video_trends
SET engagement = engagement || '{"share_rate": 0.023}'::jsonb
WHERE video_id = 'abc123';
Enter fullscreen mode Exit fullscreen mode

The || operator merges objects. Existing keys are updated, new keys are added. No need to read-modify-write in application code.

Time-Series Analytics for the Dashboard

ViralVidVault admin dashboard shows trends over time:

SELECT
    date_trunc('day', snapshot_at) AS day,
    jsonb_object_agg(
        region.key,
        region.value ->> 'views'
    ) AS views_by_region
FROM video_trends t,
     jsonb_each(t.regional_data) AS region
WHERE video_id = 'abc123'
  AND snapshot_at > NOW() - INTERVAL '30 days'
GROUP BY day
ORDER BY day;
Enter fullscreen mode Exit fullscreen mode

This returns one row per day with a JSONB object showing views per region — exactly what a chart library needs.

Performance Observations

On ~50,000 trend records with GIN indexes:

  • Key existence check (?): 1-2ms
  • Path extraction with filter: 3-5ms
  • jsonb_each aggregation (24h window): 10-20ms
  • Full cross-region aggregation: 30-60ms

JSONB is not as fast as dedicated columns for simple lookups, but the flexibility to add regions, metrics, and breakdowns without schema changes is invaluable for a platform tracking trends across 7 European markets.

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

Top comments (0)