Scaling Video Viewership Analytics With TimescaleDB Hypertables

When SQLite Stopped Being Enough for View Events

TopVideoHub aggregates trending videos across the Asia-Pacific region, and most of the stack is deliberately boring: PHP 8.4, a SQLite catalog with an FTS5 index wired up to a CJK tokenizer for Japanese/Korean/Chinese search, LiteSpeed in front, and Cloudflare on the edge. That setup is wonderful for the catalog. It is a disaster for viewership analytics.

The problem showed up the week we crossed roughly 12 million view events per day. Every play, pause, seek, completion, and region/language tag was landing in one SQLite view_events table. SQLite serializes writers, so the ingest worker held the database long enough that the read replicas serving the trending dashboards started timing out. A simple "plays per region, bucketed by hour, last 7 days" query went from snappy to 40+ seconds, and the database file pushed past 60 GB. We tried partial indexes, WAL mode, and manual monthly tables. It bought us a month.

The honest conclusion: append-heavy time-series data is a different workload than a catalog, and it deserves a different engine. We moved the analytics half of TopVideoHub onto PostgreSQL with the TimescaleDB extension and its hypertables. The catalog stayed on SQLite. This article is the concrete migration — schema, ingest from PHP, continuous aggregates, compression, and the Go and Python jobs that read it.

Why Time-Series Is a Different Beast

Before reaching for any tool it helps to name why view events break a normal table:

• Append-only, time-ordered. New rows are almost always now. You rarely update a row from last Tuesday.
• Queries are range scans over time. "Last 24h", "this week per region" — never "give me random rows."
• The hot data is tiny, the cold data is enormous. Yesterday matters a lot; the same day last year matters for one rollup query.
• High write fan-out. Our Asia-Pacific regions (JP, KR, TW, SG, VN, TH, HK, plus US/GB baselines) each emit events in bursts tied to local prime time.

A single B-tree index across 4 billion rows tries to keep all of that in one structure. TimescaleDB's answer is the hypertable: one logical table that is transparently sharded under the hood into many physical "chunks," each covering a time range (and optionally a partition key). Queries and inserts hit only the relevant chunks, so the working index is the size of one chunk, not the universe.

Hypertables in One Command

A hypertable looks like a normal table to your application. You create an ordinary table, then promote it:

CREATE TABLE view_events (
    ts          TIMESTAMPTZ      NOT NULL,
    video_id    BIGINT           NOT NULL,
    region      TEXT             NOT NULL,
    language    TEXT             NOT NULL,  -- ja, ko, zh-Hant, etc.
    event_type  TEXT             NOT NULL,  -- play | pause | complete
    watch_ms    INTEGER          NOT NULL DEFAULT 0,
    device      TEXT
);

-- Promote to a hypertable: chunk by day, sub-partition by region.
SELECT create_hypertable(
    'view_events',
    by_range('ts', INTERVAL '1 day')
);

SELECT add_dimension(
    'view_events',
    by_hash('region', 4)
);

-- Indexes are per-chunk, so this stays small even at billions of rows.
CREATE INDEX ON view_events (video_id, ts DESC);
CREATE INDEX ON view_events (region, ts DESC);

The by_range('ts', INTERVAL '1 day') line is the whole trick: each day of data becomes its own chunk with its own indexes. At 12M events/day a chunk holds ~12M rows — comfortably indexable and cacheable. The by_hash('region', 4) adds space partitioning so that a query filtered to one region touches roughly a quarter of each day's data.

Pick the chunk interval so that the most recent chunk (and its indexes) fits in about 25% of shared_buffers. Too-small chunks create planning overhead from thousands of partitions; too-large chunks lose the locality benefit. One day was right for us; a low-traffic side project might use one week.

Ingesting View Events From PHP 8.4

Our edge collectors push events to a queue, and a PHP worker drains them in batches. The single biggest ingest win is to never insert one row at a time. PostgreSQL's COPY protocol is the fastest path, and PDO can drive a multi-row prepared statement that gets close.

Here is the batched writer we run. It uses PHP 8.4, native PDO with the pgsql driver, and groups events into chunks of 1,000 so each round trip carries real weight:

<?php
declare(strict_types=1);

final class ViewEventWriter
{
    private const BATCH = 1000;

    public function __construct(private readonly PDO $pdo)
    {
        $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    }

    /** @param list<array{ts:string,video_id:int,region:string,language:string,event_type:string,watch_ms:int,device:?string}> $events */
    public function flush(array $events): int
    {
        $written = 0;
        foreach (array_chunk($events, self::BATCH) as $chunk) {
            $placeholders = implode(',', array_fill(
                0,
                count($chunk),
                '(?,?,?,?,?,?,?)'
            ));

            $sql = "INSERT INTO view_events
                    (ts, video_id, region, language, event_type, watch_ms, device)
                    VALUES {$placeholders}";

            $stmt = $this->pdo->prepare($sql);
            $params = [];
            foreach ($chunk as $e) {
                array_push(
                    $params,
                    $e['ts'], $e['video_id'], $e['region'],
                    $e['language'], $e['event_type'], $e['watch_ms'],
                    $e['device'] ?? null
                );
            }

            $this->pdo->beginTransaction();
            $stmt->execute($params);
            $this->pdo->commit();
            $written += count($chunk);
        }
        return $written;
    }
}

$pdo = new PDO(
    'pgsql:host=analytics-db;port=5432;dbname=tvh_analytics',
    'tvh_ingest',
    getenv('TVH_PG_PASSWORD'),
    [PDO::ATTR_PERSISTENT => true]
);

$writer = new ViewEventWriter($pdo);
$writer->flush($eventsFromQueue);

A few things that mattered in production:

• Persistent connections (ATTR_PERSISTENT) avoid the TCP + auth handshake on every worker tick. LiteSpeed keeps the PHP process warm, so this pays off.
• One transaction per batch, not per row. Autocommit-per-row was our original sin in SQLite and it carried over until we profiled it.
• Insert in roughly time-ascending order when you can. Hypertables tolerate out-of-order inserts, but mostly-ordered writes keep recent chunks hot and avoid touching old, compressed chunks.

Continuous Aggregates Precompute the Dashboards

The trending dashboards never need raw events — they need "plays per video per hour" or "watch minutes per region per day." Recomputing those from raw rows on every page load is exactly what was killing us. TimescaleDB's continuous aggregate is a materialized view that refreshes incrementally as new data lands, and it is itself a hypertable.

CREATE MATERIALIZED VIEW plays_hourly
WITH (timescaledb.continuous) AS
SELECT
    time_bucket(INTERVAL '1 hour', ts) AS bucket,
    video_id,
    region,
    count(*) FILTER (WHERE event_type = 'play')     AS plays,
    count(*) FILTER (WHERE event_type = 'complete') AS completes,
    sum(watch_ms)                                   AS watch_ms
FROM view_events
GROUP BY bucket, video_id, region
WITH NO DATA;

-- Refresh policy: keep the last 3 days fresh, recompute every 30 min,
-- but never touch the most recent hour (still being written).
SELECT add_continuous_aggregate_policy('plays_hourly',
    start_offset      => INTERVAL '3 days',
    end_offset        => INTERVAL '1 hour',
    schedule_interval => INTERVAL '30 minutes'
);

Now the dashboard query reads from plays_hourly — a table that is orders of magnitude smaller than the raw events — and the engine real-time aggregates the last uncovered hour on the fly so the numbers are never stale. The 40-second query became a 50-millisecond one.

The end_offset => INTERVAL '1 hour' detail is easy to miss and important: it tells the policy not to materialize the in-progress bucket, which would otherwise be rewritten constantly. Real-time aggregation fills that gap at query time.

Querying Rollups From Go for the Live Dashboard

The live "what's trending right now" panel is a small Go service behind Cloudflare. It hits the continuous aggregate with pgx, which is faster than database/sql for this kind of read path because it speaks the binary protocol natively:

package main

import (
    "context"
    "fmt"
    "os"
    "time"

    "github.com/jackc/pgx/v5/pgxpool"
)

type TrendRow struct {
    VideoID int64
    Region  string
    Plays   int64
    WatchMS int64
}

func topTrending(ctx context.Context, pool *pgxpool.Pool, region string) ([]TrendRow, error) {
    const q = `
        SELECT video_id, region, sum(plays) AS plays, sum(watch_ms) AS watch_ms
        FROM plays_hourly
        WHERE bucket >= now() - INTERVAL '6 hours'
          AND region = $1
        GROUP BY video_id, region
        ORDER BY plays DESC
        LIMIT 20`

    rows, err := pool.Query(ctx, q, region)
    if err != nil {
        return nil, fmt.Errorf("query trending: %w", err)
    }
    defer rows.Close()

    var out []TrendRow
    for rows.Next() {
        var r TrendRow
        if err := rows.Scan(&r.VideoID, &r.Region, &r.Plays, &r.WatchMS); err != nil {
            return nil, err
        }
        out = append(out, r)
    }
    return out, rows.Err()
}

func main() {
    ctx, cancel := context.WithTimeout(context.Background(), 3*time.Second)
    defer cancel()

    pool, err := pgxpool.New(ctx, os.Getenv("TVH_PG_DSN"))
    if err != nil {
        panic(err)
    }
    defer pool.Close()

    rows, err := topTrending(ctx, pool, "JP")
    if err != nil {
        panic(err)
    }
    for _, r := range rows {
        fmt.Printf("video=%d plays=%d watch_ms=%d\n", r.VideoID, r.Plays, r.WatchMS)
    }
}

Because plays_hourly is partitioned the same way as the raw table, the region = $1 filter prunes chunks aggressively. We cache the JSON result of this at the Cloudflare edge for 60 seconds, so the database barely sees the dashboard traffic at all — it mostly serves cache misses and the 30-minute refresh.

Compression and Retention Pay the Storage Bill

This is where hypertables stopped a second crisis — disk. Raw view events are highly repetitive: the same region, language, and event_type repeat millions of times. TimescaleDB's columnar compression on older chunks gave us roughly a 12x reduction.

ALTER TABLE view_events SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'region, video_id',
    timescaledb.compress_orderby   = 'ts DESC'
);

-- Compress anything older than 7 days.
SELECT add_compression_policy('view_events', INTERVAL '7 days');

-- Drop raw events after 180 days; the continuous aggregates keep the history.
SELECT add_retention_policy('view_events', INTERVAL '180 days');

The mental model is clean:

• Hot (0-7 days): uncompressed, fast to write and update.
• Warm (7-180 days): compressed columnar, still queryable, ~12x smaller.
• Cold (180+ days): raw rows dropped; the hourly and daily continuous aggregates retain the shape of history forever at a fraction of the size.

compress_segmentby should be the columns you filter on (so compressed chunks can be skipped per segment), and compress_orderby should match how you scan — newest first, in our case.

A Python Job for CJK Language Trend Anomalies

The payoff of clean rollups is that analytics jobs become short. We run a nightly Python job that flags videos whose plays in a given language spiked abnormally — useful for catching a video going viral in, say, Korean before it shows up in our global trending. It reads straight from the continuous aggregate with psycopg:

import os
import statistics
import psycopg

SQL = """
SELECT video_id, language,
       time_bucket('1 day', bucket) AS day,
       sum(plays) AS plays
FROM plays_hourly ph
JOIN view_events ve USING (video_id)         -- language lives on raw rows
WHERE bucket >= now() - INTERVAL '14 days'
GROUP BY video_id, language, day
ORDER BY video_id, language, day
"""

def detect_spikes(min_z: float = 3.0):
    dsn = os.environ["TVH_PG_DSN"]
    series: dict[tuple[int, str], list[int]] = {}

    with psycopg.connect(dsn) as conn, conn.cursor() as cur:
        cur.execute(SQL)
        for video_id, language, _day, plays in cur:
            series.setdefault((video_id, language), []).append(int(plays))

    alerts = []
    for (video_id, language), points in series.items():
        if len(points) < 8:
            continue
        history, today = points[:-1], points[-1]
        mean = statistics.fmean(history)
        stdev = statistics.pstdev(history) or 1.0
        z = (today - mean) / stdev
        if z >= min_z:
            alerts.append((video_id, language, today, round(z, 2)))

    alerts.sort(key=lambda a: a[3], reverse=True)
    return alerts


if __name__ == "__main__":
    for video_id, language, plays, z in detect_spikes():
        print(f"SPIKE video={video_id} lang={language} plays={plays} z={z}")

In practice we precompute language onto a dedicated plays_by_language_daily aggregate rather than joining back to raw rows, but the join version above is the honest first draft and it runs in seconds because the heavy lifting already happened in the continuous aggregate.

Numbers After the Migration

A few weeks in, with the same hardware budget:

• Dashboard "plays per region, 7 days" query: ~40 s → ~50 ms.
• Ingest throughput: comfortably past 30k events/sec in batched mode, with headroom.
• On-disk size for 180 days of events: ~60 GB SQLite file → ~9 GB compressed hypertable.
• Write contention against reads: gone — Postgres MVCC means the ingest worker never blocks the dashboards.

None of this required rewriting the product. The PHP catalog still runs on SQLite with the FTS5 CJK tokenizer; we simply stopped asking a document store to behave like a time-series engine.

Lessons Worth Keeping

If you are staring at a swelling events table, the order of operations that worked for us was: promote to a hypertable with a sane chunk interval, batch your inserts, build continuous aggregates for every dashboard, then turn on compression and retention. The hypertable solves write contention and query locality; the continuous aggregates solve dashboard latency; compression solves the disk bill. Skip any one of them and the wins are partial.

The broader lesson is about picking the right store for the shape of the data, not the convenience of one engine. SQLite is still perfect for our catalog and search. View events are append-only, time-ordered, and queried by range — that is the exact shape TimescaleDB hypertables are built for, and treating them that way turned our worst-performing surface into one of the quietest parts of the stack.