TimescaleDB Hypertables for Multi-Region Video Viewership Analytics

The 9-Second Query That Killed the Trending Page

Our view-event table crossed 40 million rows on a Tuesday. The query that builds the regional trending shelf — videos watched in JP in the last 6 hours, ranked by acceleration — went from 40ms to just over 9 seconds. LiteSpeed workers piled up behind it, Cloudflare started serving stale HTML on a 5-minute page cache, and the mobile watch page felt like 2011.

The stack behind TopVideoHub is deliberately boring: PHP 8.4, SQLite with an FTS5 index and a custom CJK-aware tokenizer for search, LiteSpeed in front, Cloudflare on the edge. That combination is excellent for read-mostly catalog data. It is genuinely bad at one thing: append-heavy time-series with high-cardinality group-bys. A single writer lock, no partitioning, no columnar storage, and every GROUP BY video_id over a 90-day window is a full scan.

So we split the workload. Catalog, search, and page rendering stayed on SQLite. Viewership analytics moved to PostgreSQL with TimescaleDB hypertables. This is what that migration actually looked like — schema, ingest path, continuous aggregates, compression numbers, and the parts that bit us.

Why Hypertables Instead of Native Partitioning

Plain Postgres declarative partitioning works. We prototyped it. The reason we did not ship it:

• Partition management is manual. Someone has to create next month's partition. That someone eventually forgets, and inserts start failing at 00:00 UTC.
• No native columnar compression. Our events are extremely repetitive — the same video_id and region repeat thousands of times per hour. Row storage wastes an enormous amount of space on that.
• No incremental materialized views. Refreshing a normal materialized view over 40M rows every 15 minutes is the same full scan we were trying to escape.

A hypertable is a regular Postgres table with automatic chunking, plus three things layered on top: chunk exclusion during planning, native compression that converts old chunks to a columnar layout, and continuous aggregates that refresh only the buckets whose source data changed. You still write plain SQL. INSERT, SELECT, JOIN against SQLite-exported dimension tables — all unchanged.

Schema Design for Multi-Region Viewership Events

The event is intentionally narrow. Anything derivable at query time is not stored.

CREATE TABLE view_event (
  ts         timestamptz NOT NULL,
  video_id   text        NOT NULL,
  region     char(2)     NOT NULL,
  lang       text        NOT NULL,
  surface    smallint    NOT NULL,   -- 1=home 2=category 3=search 4=related
  dwell_ms   integer     NOT NULL DEFAULT 0,
  completed  boolean     NOT NULL DEFAULT false
);

-- 1-day chunks: ~1.6M rows/chunk at our volume, comfortably
-- inside shared_buffers for the recent working set.
SELECT create_hypertable(
  'view_event',
  by_range('ts', INTERVAL '1 day')
);

-- Secondary hash dimension. Nine regions, four hash partitions:
-- parallel workers stop fighting over the same chunk on writes.
SELECT add_dimension('view_event', by_hash('region', 4));

CREATE INDEX ON view_event (video_id, ts DESC);
CREATE INDEX ON view_event (region, ts DESC);
CREATE INDEX ON view_event (lang, ts DESC) WHERE lang IN ('ja','ko','zh-Hant','zh-Hans');

A few decisions worth explaining:

• Chunk interval. The rule of thumb is that one chunk plus its indexes should fit in about 25% of RAM. We started at 7 days, which produced 11M-row chunks and made compression jobs run for minutes. One day is the sweet spot for us. You can change it with set_chunk_time_interval() and it applies to future chunks only.
• char(2) for region. ISO country codes, fixed width, and it segments beautifully under compression.
• surface as smallint. We slice trending by entry point constantly — a video that trends only from search behaves very differently from one that trends from the home shelf.
• The partial index on CJK languages. Roughly 60% of our traffic is Japanese, Korean, or Chinese, and nearly every language-specific dashboard query filters to that set. A partial index keeps it small.

No user ID. We do not need one for trending, and not collecting it removes an entire class of privacy and retention questions.

Batching Writes From PHP Without Adding Request Latency

The watch page cannot afford a synchronous round trip to Postgres on every hit. The pattern that worked: buffer in the request, flush once on shutdown, and never let a failed flush surface to the user.

<?php
declare(strict_types=1);

final class ViewEventBuffer
{
    private const int FLUSH_ROWS = 200;
    private const int COLS = 7;

    /** @var list<list<string|int|bool>> */
    private array $rows = [];

    public function __construct(private readonly PDO $pdo) {}

    public function record(
        string $videoId,
        string $region,
        string $lang,
        int $surface,
        int $dwellMs = 0,
        bool $completed = false,
    ): void {
        $this->rows[] = [
            (new DateTimeImmutable('now', new DateTimeZone('UTC')))
                ->format('Y-m-d H:i:s.uP'),
            $videoId,
            strtoupper(substr($region, 0, 2)),
            $lang,
            $surface,
            max(0, $dwellMs),
            $completed,
        ];

        if (count($this->rows) >= self::FLUSH_ROWS) {
            $this->flush();
        }
    }

    public function flush(): int
    {
        if ($this->rows === []) {
            return 0;
        }

        $tuple  = '(' . implode(',', array_fill(0, self::COLS, '?')) . ')';
        $values = implode(',', array_fill(0, count($this->rows), $tuple));
        $params = array_merge(...$this->rows);

        $sql = 'INSERT INTO view_event
                (ts, video_id, region, lang, surface, dwell_ms, completed)
                VALUES ' . $values;

        try {
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute($params);
            $written = $stmt->rowCount();
        } catch (PDOException $e) {
            // Analytics must never break a page render.
            error_log('view_event flush failed: ' . $e->getMessage());
            $written = 0;
        }

        $this->rows = [];
        return $written;
    }
}

// Wiring, in the front controller:
$pdo = new PDO(
    'pgsql:host=127.0.0.1;dbname=tvh_analytics',
    getenv('PG_USER'),
    getenv('PG_PASS'),
    [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_PERSISTENT => true],
);
$buffer = new ViewEventBuffer($pdo);
register_shutdown_function(static fn() => $buffer->flush());

Three details that matter more than the code itself:

• ATTR_PERSISTENT => true. Under LiteSpeed's PHP SAPI, workers are long-lived. Without persistent connections you pay TCP plus Postgres backend startup on every request, and that startup cost dwarfs the insert.
• register_shutdown_function. The flush happens after the response body has been sent, so the user never waits on it.
• Swallowing the exception. Losing a view event is annoying. Serving a 500 because the analytics box is rebooting is unacceptable. Log it, drop it, move on.

At 200 rows per multi-row INSERT, a flush takes about 3ms on a modest 4-vCPU box. That is well inside the noise of a page render.

Continuous Aggregates Are the Actual Point

Hypertables alone would not have fixed the 9-second query. Continuous aggregates did. A continuous aggregate is a materialized view over a hypertable that Timescale refreshes incrementally — it recomputes only the time buckets whose underlying chunks were modified since the last run.

CREATE MATERIALIZED VIEW video_views_hourly
WITH (timescaledb.continuous) AS
SELECT
    time_bucket(INTERVAL '1 hour', ts) AS bucket,
    video_id,
    region,
    count(*)                                  AS views,
    count(*) FILTER (WHERE completed)         AS completions,
    sum(dwell_ms)::bigint                     AS dwell_ms_total,
    percentile_agg(dwell_ms)                  AS dwell_sketch
FROM view_event
GROUP BY bucket, video_id, region
WITH NO DATA;

SELECT add_continuous_aggregate_policy('video_views_hourly',
    start_offset      => INTERVAL '3 days',
    end_offset        => INTERVAL '1 hour',
    schedule_interval => INTERVAL '15 minutes');

-- Columnar compression on the raw hypertable.
ALTER TABLE view_event SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'video_id, region',
    timescaledb.compress_orderby   = 'ts DESC'
);
SELECT add_compression_policy('view_event', INTERVAL '7 days');
SELECT add_retention_policy('view_event',  INTERVAL '90 days');

The dwell_sketch column is the subtle bit. You cannot store a percentile in a continuous aggregate and then average it later — percentiles do not compose. percentile_agg from the timescaledb-toolkit extension stores a UDDSketch, a mergeable approximate-quantile structure. At query time you rollup() the sketches across buckets and then extract the percentile:

SELECT video_id,
       sum(views) AS views_24h,
       approx_percentile(0.5,  rollup(dwell_sketch)) AS dwell_p50,
       approx_percentile(0.95, rollup(dwell_sketch)) AS dwell_p95
FROM video_views_hourly
WHERE region = 'JP' AND bucket >= now() - INTERVAL '24 hours'
GROUP BY video_id
ORDER BY views_24h DESC
LIMIT 50;

That is mathematically sound and reads 24 pre-aggregated rows per video instead of scanning millions of events.

On start_offset => INTERVAL '3 days': the refresh policy only re-examines the last three days of buckets. Events arriving later than that — say a delayed batch from a mobile client — will land in the raw hypertable but never make it into the aggregate. If you need those, either widen the offset or turn on real-time aggregation, which unions the materialized buckets with a live scan of the newest chunk. We turned real-time aggregation off (timescaledb.materialized_only = true) because the live-scan tail made query latency unpredictable, and a trending shelf that lags by up to an hour is fine when Cloudflare is caching the page for five minutes anyway.

Compression Numbers, Honestly Reported

After 30 days of production traffic:

• Raw uncompressed: 41.2M rows, 4.7 GB including indexes.
• After compression on chunks older than 7 days: 612 MB. Roughly 7.6x.
• Hourly continuous aggregate: 38 MB. It is a rounding error next to the raw data, which is why we can afford a daily and a weekly rollup on top of it.

The segmentby = 'video_id, region' choice drove most of that. Compression groups rows by the segmentby columns and stores each remaining column as an array; when a single video accumulates thousands of events per region per chunk, the repeated identifiers collapse to one value. Picking a high-cardinality segmentby key — a session ID, say — would have produced tiny segments and a compression ratio near 1.

The cost: compressed chunks are effectively append-only for older data. Recent TimescaleDB versions do permit UPDATE and DELETE on compressed chunks, but they decompress the affected segments to do it, which is slow and refragments storage. Plan your schema as if compressed data is immutable, because operationally it is.

A Go Sidecar When PHP Buffering Is Not Enough

During a regional trending spike, per-request flushing generates a lot of small transactions. We put a small Go service in front of Postgres that accepts events over a Unix socket and writes them with COPY, which is several times faster than multi-row INSERT at batch sizes above a thousand.

package main

import (
    "context"
    "log"
    "time"

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

type Event struct {
    TS        time.Time
    VideoID   string
    Region    string
    Lang      string
    Surface   int16
    DwellMS   int32
    Completed bool
}

var cols = []string{
    "ts", "video_id", "region", "lang", "surface", "dwell_ms", "completed",
}

func runWriter(ctx context.Context, pool *pgxpool.Pool, in <-chan Event) {
    const maxBatch = 5000

    ticker := time.NewTicker(2 * time.Second)
    defer ticker.Stop()

    batch := make([]Event, 0, maxBatch)

    flush := func() {
        if len(batch) == 0 {
            return
        }
        rows := make([][]any, len(batch))
        for i, e := range batch {
            rows[i] = []any{
                e.TS, e.VideoID, e.Region, e.Lang,
                e.Surface, e.DwellMS, e.Completed,
            }
        }

        cctx, cancel := context.WithTimeout(ctx, 10*time.Second)
        n, err := pool.CopyFrom(
            cctx,
            pgx.Identifier{"view_event"},
            cols,
            pgx.CopyFromRows(rows),
        )
        cancel()

        if err != nil {
            // Drop the batch rather than grow the buffer without bound.
            // Analytics loss is acceptable; an OOM kill is not.
            log.Printf("copy failed, dropping %d events: %v", len(batch), err)
        } else {
            log.Printf("wrote %d events", n)
        }
        batch = batch[:0]
    }

    for {
        select {
        case e := <-in:
            batch = append(batch, e)
            if len(batch) >= maxBatch {
                flush()
            }
        case <-ticker.C:
            flush()
        case <-ctx.Done():
            flush()
            return
        }
    }
}

Two seconds or five thousand events, whichever comes first. The explicit drop-on-error is deliberate: an unbounded retry buffer in a sidecar is how you turn a database hiccup into a memory-exhaustion incident.

Feeding the Result Back Into SQLite

The front end never queries Postgres. A cron job computes the trending sets and writes them into the SQLite file that LiteSpeed serves from, so a page render stays a local file read.

#!/usr/bin/env python3
import os
import sqlite3
import psycopg

REGIONS = ("JP", "KR", "TW", "SG", "VN", "TH", "HK", "US", "GB")
MIN_VIEWS = 25
TOP_N = 60

TRENDING_SQL = """
SELECT video_id,
       sum(views)                                          AS views_24h,
       sum(views) FILTER (
           WHERE bucket >= now() - INTERVAL '6 hours')     AS views_6h,
       approx_percentile(0.5, rollup(dwell_sketch))::int   AS dwell_p50,
       sum(completions)::float / NULLIF(sum(views), 0)     AS completion_rate
FROM video_views_hourly
WHERE region = %s
  AND bucket >= now() - INTERVAL '24 hours'
GROUP BY video_id
HAVING sum(views) >= %s
ORDER BY (sum(views) FILTER (WHERE bucket >= now() - INTERVAL '6 hours'))::numeric
         / NULLIF(sum(views), 0) DESC,
         views_24h DESC
LIMIT %s
"""


def main() -> None:
    pg = psycopg.connect(os.environ["PG_DSN"])
    lite = sqlite3.connect(os.environ["SQLITE_PATH"])
    lite.execute("PRAGMA journal_mode=WAL")

    lite.execute("""
        CREATE TABLE IF NOT EXISTS trending_region (
            region          TEXT    NOT NULL,
            rank            INTEGER NOT NULL,
            video_id        TEXT    NOT NULL,
            views_24h       INTEGER NOT NULL,
            views_6h        INTEGER NOT NULL,
            dwell_p50       INTEGER NOT NULL,
            completion_rate REAL    NOT NULL,
            PRIMARY KEY (region, rank)
        )
    """)

    with pg, pg.cursor() as cur:
        for region in REGIONS:
            cur.execute(TRENDING_SQL, (region, MIN_VIEWS, TOP_N))
            rows = [
                (region, i, *r) for i, r in enumerate(cur.fetchall(), start=1)
            ]
            if not rows:
                print(f"{region}: no rows above threshold, keeping previous set")
                continue

            lite.execute("DELETE FROM trending_region WHERE region = ?", (region,))
            lite.executemany(
                "INSERT INTO trending_region VALUES (?,?,?,?,?,?,?)", rows
            )
            print(f"{region}: {len(rows)} videos")

    lite.commit()
    lite.close()
    pg.close()


if __name__ == "__main__":
    main()

The ordering expression is the acceleration ratio — share of the day's views that landed in the last six hours — with absolute 24-hour volume as the tiebreak. That surfaces genuinely rising videos instead of the same evergreen uploads every day. The if not rows: continue guard matters more than it looks: without it, a single failed cron run during a quiet overnight window would blank an entire region's shelf.

Things That Bit Us

• ORDER BY without a time predicate. Chunk exclusion only works when the planner can prove a time range. A query with no ts or bucket filter touches every chunk. Every analytics query in our codebase now carries an explicit time bound, enforced by a query-builder assertion.
• timestamptz, never timestamp. With nine regions across seven UTC offsets, naive timestamps produce bucket boundaries that silently disagree. Store UTC, convert at the presentation layer.
• Continuous aggregates on top of continuous aggregates need matching bucket alignment. Our daily rollup reads from the hourly aggregate; the bucket interval must be an exact multiple or the refresh policy quietly does nothing useful.
• max_locks_per_transaction. A query spanning many chunks acquires a lock per chunk. The Postgres default of 64 is far too low for a hypertable with hundreds of chunks. We run 512.
• Compression policies and manual backfill conflict. Backfilling into a compressed chunk works but is slow enough to look like a hang. Decompress the target range explicitly, backfill, recompress.

Conclusion

The trending query that took 9.2 seconds now runs in 34ms against the hourly continuous aggregate, and the raw event table takes 87% less disk than it did before compression. None of that required rewriting the application — the front end still renders from SQLite, search still runs through the FTS5 CJK index, and the PHP change amounted to one buffer class plus a shutdown hook.

The general lesson is smaller than the tooling suggests: stop asking one storage engine to be good at both point lookups over a catalog and aggregations over an append-only event stream. Those workloads want opposite layouts. Split them, keep the boring read path boring, and let the time-series database do the part it was built for.